Excel Forum

พอดีทำได้ลองเขียน ก๊อป Code VBA จาก กระทู้นี้มา

http://snasui.blogspot.com/2011/06/vba_26.html

ตามไฟล์แนบ

แต่พอลองเลือกตามตัวอย่างกลับไม่ขึ้นมาเหมือนตัวอย่าง ต้องไปกด run marcro คราวนี้ขึ้น แต่ขึ้นมาแถวเดียว มันผิดตรงจุดไหนครับ งง

ลองดูตามไฟล์แนบครับ
ส่วนข้อผิดพลาดประการใด ให้อาจารย์คนควน หรือท่านอื่นๆ มาอธิบายอีกครั้งครับ

khainui wrote:ตามไฟล์แนบ

แต่พอลองเลือกตามตัวอย่างกลับไม่ขึ้นมาเหมือนตัวอย่าง ต้องไปกด run marcro คราวนี้ขึ้น แต่ขึ้นมาแถวเดียว มันผิดตรงจุดไหนครับ งง

Code นั้นอ้างอิง A4 ในการ Clear ข้อมูล ดังนั้น A4:E4 ซึ่งเป็นหัวคอลัมน์ ต้องเติมข้อมูลให้ครบครับ

snasui wrote:

khainui wrote:ตามไฟล์แนบ

แต่พอลองเลือกตามตัวอย่างกลับไม่ขึ้นมาเหมือนตัวอย่าง ต้องไปกด run marcro คราวนี้ขึ้น แต่ขึ้นมาแถวเดียว มันผิดตรงจุดไหนครับ งง

Code นั้นอ้างอิง A4 ในการ Clear ข้อมูล ดังนั้น A4:E4 ซึ่งเป็นหัวคอลัมน์ ต้องเติมข้อมูลให้ครบครับ

เข้าใจแล้วครับ แต่ขอสอบถามเพิ่มเติม ตรงช่อง e2 ใน sheet report ทำอย่างไรให้ดึงข้อมูลมาเป็น drop down list ครับ

พอดีทำด้วย data validation เลือก list แล้วมันมาทุกรายการเลย ไม่ได้ filter ให้เหลือข้อมูลละ 1 รายการ

สามารถทำได้หลายวิธีครับ อย่างง่าย ๆ ก็สร้างด้วย Manual แล้วค่อยนำมาใช้ใน Validation อย่างที่ยากขึ้นไปก็ใช้ VBA เขียนครับ ลองทำมาดูก่อน ติดตรงไหนก็ถามกันต่อครับ

snasui wrote: สามารถทำได้หลายวิธีครับ อย่างง่าย ๆ ก็สร้างด้วย Manual แล้วค่อยนำมาใช้ใน Validation อย่างที่ยากขึ้นไปก็ใช้ VBA เขียนครับ ลองทำมาดูก่อน ติดตรงไหนก็ถามกันต่อครับ

ทำได้แล้วครับด้วยวิธี Unique list แต่ยังติดปัญหามันไม่ได้เรียงลำดับจากมากไปน้อยในช่อง e2 และอีกปัญหาคือ

ถ้ามี Record ใหม่เพิ่มเข้าไปในช่อง Database ไม่สามารถทำให้มันเพิ่มข้อมูล Auto ในคอลัมน์ h และ i ได้ครับ ต้องใช้สูตรอย่างไรครับ

ตามไฟล์แนบครับ

ที่คอลัมน์ H และ I ใช้สูตรเดิมครับ แต่ปรับช่วงข้อมูลในสูตรให้รองรับกับข้อมูลที่เพิ่มขึ้น หรือ กำหนดชื่อ (Range Name) ให้กับช่วงข้อมูลต้นแหล่งเพื่อให้ยืดหยุ่นตามปริมาณข้อมูล แล้วค่อยนำมาใช้ในสูตร

ส่วนการเรียงจากน้อยไปหามากหรือจากมากไปหาน้อย หากจะใช้สูตรสามารถดูเพิ่มเติมได้ที่นี่ครับ การเรียงข้อมูลด้วยสูตร ซึ่งเพิ่มคอลัมน์สำหรับเรียงข้อมูลออกมาต่างหากอีก 1 คอลัมน์ครับ

ได้ลองเพิ่มช่วงแล้วครับแต่เหมือนมันขึ้น error ครับ เพราะมันเป็นค่าว่างหรือเปล่าครับ

ลองตามนี้ครับ

1. ที่เซลล์ I เปลี่ยนสูตรเป็น
   =ROUND(SUMPRODUCT((Dept<>"")/COUNTIF(Dept,Dept&"")),0)
   Enter
2. ที่เซลล์ H เปลี่ยนสูตรเป็น
   =IF(ROWS(H$2:H2)>$I$2,"",INDEX(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 ลงด้านล่าง

สูตรใช้ได้แล้วครับ เดี๋ยวขอไปแกะสูตรแต่ละชั้นก่อนว่ามีลำดับเป็นมาอย่างไร

ตอนนี้กำลังใช้สูตร sort ข้อมูลเพื่อไปออกในช่อง e2 อยู่ครับ

ขอบคุณครับ

snasui wrote: ลองตามนี้ครับ

1. ที่เซลล์ I เปลี่ยนสูตรเป็น
   =ROUND(SUMPRODUCT((Dept<>"")/COUNTIF(Dept,Dept&"")),0)
   Enter
2. ที่เซลล์ H เปลี่ยนสูตรเป็น
   =IF(ROWS(H$2:H2)>$I$2,"",INDEX(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 ลงด้านล่าง

อาจารย์ครับขอความหมายของสูตร 2 สูตรนี้หน่อยครับ (กำลังแกะสูตรอยู่ครับ)

สูตรแรกคือ =match(Dept,Dept,0) ทำไมผลของสูตรถึงออกมาเป็น 1 พอไปแถวที่ 2 จึงกลายเป็น 2 ครับ
สูตรสองคือ เครื่องหมาย if(Dept<>"" หมายถึง ช่วงเซลล์นั้นต้องไม่ใช่ค่าว่างใช่ไม๊ครับ

khainui wrote:สูตรแรกคือ =match(Dept,Dept,0) ทำไมผลของสูตรถึงออกมาเป็น 1 พอไปแถวที่ 2 จึงกลายเป็น 2 ครับ

เป็นการนำค่าใน Dept ตัวแรกไปตรวจสอบกับ Dept ทั้งชุดว่าอยู่ในลำดับที่เท่าไร เมื่อเป็นบรรทัดที่ 2 ก็จะเป็นการนำตัวที่ 2 ไปตรวจ เช่นนี้ไปเรื่อย ๆ ครับ

khainui wrote:สูตรสองคือ เครื่องหมาย if(Dept<>"" หมายถึง ช่วงเซลล์นั้นต้องไม่ใช่ค่าว่างใช่ไม๊ครับ

เข้าใจถูกแล้วครับ

=IF(ROWS(H$2:H2)>$I$2,"",INDEX(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))))

นั่งแกะตั้งนานงงครับว่า ตรง SMALL(IF(FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)) ส่งกลับเป็นตัวเลข row_number อย่างไรครับ มึน
รบกวนอธิบายหน่อยครับ ช่วงไหนเป็น row_number ของ index ช่วงไหนเป็น column_number ครับ

จากสูตร =IF(ROWS(H$2:H2)>$I$2,"",INDEX(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)))) หมายความว่า หาก ROWS(H$2:H2)>$I$2 มีค่าเป็นจริงแล้ว ให้แสดงค่าว่าง หากไม่เป็นจริงให้แสดงผลลัพธ์ของ INDEX(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)))

จากสูตร INDEX(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))) หมายความว่า จากช่วงเซลล์ 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))

จากสูตร 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)) หมายความว่า จากช่วงเซลล์ที่ได้จากผลลัพธ์ของสูตร IF(FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1),ROW(Dept)-MIN(ROW(Dept))+1) ให้หาค่าที่น้อยที่สุดในลำดับที่เป็นผลลัพธ์ของสูตร ROWS(H2:$H$2)

จากสูตร IF(FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1),ROW(Dept)-MIN(ROW(Dept))+1) หมายความว่า หาก FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1) เป็นจริง (ไม่เป็น 0) แล้วให้แสดงผลลัพธ์ของ ROW(Dept)-MIN(ROW(Dept))+1 หากไม่เป็นจริงให้แสดงค่า False

จากสูตร FREQUENCY(IF(Dept<>"",MATCH(Dept,Dept,0)),ROW(Dept)-MIN(ROW(Dept))+1) หมายความว่า ค่าที่ได้จากสูตร IF(Dept<>"",MATCH(Dept,Dept,0)) เกิดขึ้นกี่ครั้งจากค่าในผลลัพธ์จากสูตร ROW(Dept)-MIN(ROW(Dept))+1

ดูเพิ่มเติมเรื่อง Frequency ที่ http://www.snasui.com/viewtopic.php?f=3&t=2221

สูตร MATCH(Dept,Dept,0)) อธิบายแล้วตามความเห็นก่อนหน้านี้

สูตร ROW(Dept)-MIN(ROW(Dept))+1 หมายถึง จำนวนบรรทัดทั้งหมดจาก Dept หักด้วยบรรทัดเริ่มต้นของ Dept แล้วบวกด้วย 1 เพื่อให้แสดงค่าลำดับเริ่มที่ 1 เสมอ ดูเพิ่มเติมที่ http://www.snasui.com/viewtopic.php?p=17983#p17983