Page 1 of 1
Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่าง
Posted: Fri Jun 21, 2013 11:07 am
by khainui
- จากไฟล์ตัวอย่างที่แนบ ใน Sheet Report Cell E 2 ใช้ validation list มี 2 คำถามครับ
1. จะทำอย่างไรให้เรียงลำดับจากน้อยไปมาก หรือมากไปน้อยครับ
2. ใน List จะมีข้อมูลที่เป็นช่องว่างมาด้วยเนื่องจากเป็นข้อมูลของสูตร ทำอย่างไรให้ใน List เป็นข้อมูล Value อย่างเดียวโดยไม่มีช่องว่างครับ
- ใน Sheet Database Column K ผมใช้สูตรเพื่อเรียงลำดับข้อมูลใหม่แต่มันไม่สามารถเรียงลำดับได้ครับ หาสาเหตุไม่เจอ
ขอบคุณครับ
Re: Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่
Posted: Fri Jun 21, 2013 11:43 am
by snasui
ลองตามนี้ครับ
- H2 ปรับสูตรเป็นด้านล่างเพื่อนำ Space ในข้อความออกไป
=IF(ROWS(H$2:H2)>$I$2,"",INDEX(SUBSTITUTE(Dept," ",""),SMALL(IF(FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1),ROW(Dept)-MIN(ROW(Dept))+1),ROWS(H2:$H$2))))
Ctrl+Shift+Enter > Copy ลงด้านล่าง
- J2 คีย์สูตรเพื่อให้ลำดับ
=IF(H2="","",SUMPRODUCT(--(H2>$H$2:$H$15))+COUNTIF(H$2:H2,H2))
Enter > Copy ลงด้านล่าง
- K2 คีย์สูตรเพื่อเรียงใหม่
=IF(H2="","",INDEX($H$2:$H$15,MATCH(ROWS(K$2:K2),$J$2:$J$15,0)))
Enter > Copy ลงด้านล่าง
Note: เมื่อนำ Space ออกไปจากข้อความแล้วใน Validation จะนำค่านั้นมาค้นหาข้อมูล ตามตัวอย่างนี้จะค้นหาไม่เจอเพราะว่าไม่มีข้อมูลที่ตรงกัน สำหรับข้อมูลที่ตรงกันจะต้องมี Space หรือ วรรค อยู่ด้วย นอกจากจะเปลี่ยนจากต้นแหล่งแล้วสามาถที่จะเปลี่ยนโดยใช้เซลล์อื่นช่วย หรือเปลี่ยนโดย VBA หากต้องการจะทำเช่นนั้นให้ลองทำมาก่อน ติดตรงไหนค่อยถามกันครับ
Re: Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่
Posted: Fri Jun 21, 2013 12:49 pm
by nattasiray
สำหรับผมแล้ว คงหันมาใช้ Data Remove Duplicate List ครับ โดยใช้ VBA ควบคุมขั้นตอนการกระทำ ลองค้นหาใน Youtube ด้วยคำว่า date remove duplicates
Re: Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่
Posted: Fri Jun 21, 2013 2:01 pm
by khainui
ขอบคุณครับ เดี๋ยวขอลองไล่สูตรดูครับ
Re: Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่
Posted: Fri Jun 21, 2013 4:51 pm
by khainui
snasui wrote:
[*]J2 คีย์สูตรเพื่อให้ลำดับ
=IF(H2="","",SUMPRODUCT(--(H2>$H$2:$H$15))+COUNTIF(H$2:H2,H2))
Enter > Copy ลงด้านล่าง
Note: เมื่อนำ Space ออกไปจากข้อความแล้วใน Validation จะนำค่านั้นมาค้นหาข้อมูล ตามตัวอย่างนี้จะค้นหาไม่เจอเพราะว่าไม่มีข้อมูลที่ตรงกัน สำหรับข้อมูลที่ตรงกันจะต้องมี Space หรือ วรรค อยู่ด้วย นอกจากจะเปลี่ยนจากต้นแหล่งแล้วสามาถที่จะเปลี่ยนโดยใช้เซลล์อื่นช่วย หรือเปลี่ยนโดย VBA หากต้องการจะทำเช่นนั้นให้ลองทำมาก่อน ติดตรงไหนค่อยถามกันครับ
กรณีสูตร sumproduct หมายถึงนับจำนวนของข้อมูลในช่วง ของ h2 ถึง h15 ว่ามีข้อมูลกี่รายการที่น้อยกว่า h2 ใช่ไม๊ครับ แล้วจึงนำมาบวกกับสูตร countif ที่มีค่าเท่ากับ 1
กรณีลบ Space ออกไป มันไม่ได้ช่วยให้ใน validation list ไม่ดึงข้อมูลที่เป็นสูตรมาด้วยใช่ไม๊ครับ ควรใช้สูตรตัวไหนเพื่อให้สามารถนับจำนวนที่เป็นข้อมูลแต่ไม่นับเซลที่เป็นสูตรครับ
Re: Validation List ทำอย่างไรให้เรียงลำดับ และไม่ให้มีช่องว่
Posted: Fri Jun 21, 2013 7:37 pm
by snasui
khainui wrote:กรณีสูตร sumproduct หมายถึงนับจำนวนของข้อมูลในช่วง ของ h2 ถึง h15 ว่ามีข้อมูลกี่รายการที่น้อยกว่า h2 ใช่ไม๊ครับ แล้วจึงนำมาบวกกับสูตร countif ที่มีค่าเท่ากับ 1
ถูกต้องแล้วครับ
เมื่อนับไว้แล้วก็ไม่ต้องนับอีกครับ สามารถใช้สูตรที่ Dept_name เป็นด้านล่างครับ
=OFFSET(database!$H$1,1,0,
database!$I$2,1)