ขอฟังก์ชันแยกหมวดสินค้า

วังวู ช่ง · #1

เรียน ท่านอาจารย์ ที่เคาลบ และท่านสมาชิกที่รู้ทังหลายที่รักแพงครับ สิ่งที่ผมพะยายามทำแต่ทำไม่ได้คืว่า
Column A,B,C เป็นข้อมูนยาวไม่จำกัดครับ และมีหลายหมวดไม่จำกัดด้วยครับ ต้องฟังก์ชัน เพื่อแยกสินค้าออกเป็นหมวดตามตัวที่แนบมาครับ ช่วยบอกฟังก์ชันให้ด้วยครับ ขอบคุณมากครับ

bank9597 · #2

ลองดูครับ

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

ที่ F2 คีย์ =IF(ROWS(F$1:F1)>COUNTIF($B$2:$B$13,1000),"",INDEX(B$2:B$13,SMALL(IF($B$2:$B$13=1000,ROW($B$2:$B$13)-ROW($B$2)+1),ROWS(F$1:F1))))

กด Ctrl+Shuft+Enter คัดลอกไปทางขวา และซ้าย ส่วนตารางอื่นก็ปรับสูตรตามได้เลยครับ

วังวู ช่ง · #3

เรียน ท่านอาจารย์ ที่เคาลบ และท่านสมาชิกที่รู้ทังหลายที่รักแพงครับ ไม่ทราบว่า พอมีไครพอจะแนะนำได้ไหม หรือมีตัวอย่างไหนให้พอศึกษาได้บ้างครับ ตัวย่างเก่าที่ผมเคียทำประกดว่ามีความหยุ้งยากไม่น้อยสำลับผมครับ สิ่งที่ผมต้องกานทำใหม่คื ใน D1 เมื่อคีย์ 1000 ใน Column EFG ให้ออกผนหมวด 1000 เมื่อคีย์ 2000 ใน Column EFG ให้ออกผนหมวด 2000 เมื่อคีย์ 3000 ใน Column EFG ให้ออกผนหมวด 3000 ช่วยด้วยครับ ขอบคุณครับ

snasui · #4

เรื่องเดียวกัน ไม่ควรเปิดกระทู้ใหม่ครับ

สำหรับที่ถามมานั้น ลองตามไฟล์แนบครับ

ที่เซลล์ H1 คีย์สูตรเพื่อนับว่าพบกี่รายการ
=COUNTIF(B2:B13,D1)
Enter
ที่เซลล์ E2 คีย์สูตรเพื่อ List ชื่อสินค้า
=IF(ROWS(E$1:E1)>$H$1,"",INDEX(A$2:A$13,SMALL(IF($B$2:$B$13=$D$1,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(E$1:E1))))
Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

godman · #5

ผมสนใจสูตรนี้มากครับ เพราะว่าในคำถามลักษณะเดียวกัน ผมเคยใช้แต่ 6 ขั้นตอน แต่ของอาจารญ์ทำเพียง 2 ขั้นตอน แต่ก่อนผมจะเริ่มจากแทรกคอลัมพ์เพิ่มขวามือ ใส่ 0 นำแล้วเขียนสูตรบ่งชี้ว่าตัวเลขที่ตรงกันในลักษณะนับ 1 2 3....แล้วก็หาจำนวนค่าที่สูงสุด แล้วก็ใช้สูตรลิตต์รายการ ทีละฟิลด์ ซึ่งเสียเวลามาก พอมาเห็นสูตรของอาจารย์ทำให้ผมได้ทางที่ลัดขึ้น ขอรบกวนอาจารย์ช่วยอธิบายสูตรได้ไหมครับ และคำถามที่สอง คือ คำตอบลัษณะนี้ ใช้ pivot ทำได้ไหมครับ

godman · #6

ผมขออนุญาติเจ้าของกระทู้ ขอบคุณที่ได้ความรู้ ผมแนบไฟล์ที่ผมลองเล่นมา

snasui · #7

ผมได้ตอบคำถามลักษณะนี้ให้กับคุณ godman ไปบ้างแล้ว เห็นว่ามีความคุ้นเคยกับสูตรอยู่พอสมควร สำหรับที่ถามมานี้ อยากให้ถามมาเฉพาะที่แกะไม่ได้ครับ สูตรนี้ไม่ได้ซับซ้อนมากนัก มีแค่เงื่อนไขเดียวเท่านั้น อีกประการผมเห็นว่าตัวอย่างนี้ไม่ได้มีข้อมูลมากมาย เหมาะแก่การแกะสูตรอยู่พอสมควรครับ

ที่ผมตอบลักษณะนี้เพื่อสร้างความคุ้นเคยให้กับคุณ godman และท่านอื่น ๆ ที่กำลังศึกษาสูตรทำนองเดียวกันนี้ครับ ไม่ใช่เพราะว่าไม่ต้องการจะเปิดเผยแนวความคิดในการเขียนสูตร

bank9597 · #8

ครับผม หากมีข้อสงสัยในส่วนใดของสูตร สามารถถามมาได้ทันทีครับ ไม่จำกัด ผมจะช่วยตอบอีกแรงในกรณีที่อาจารย์ไม่ว่างครับ

godman · #9

ผมสงสัยสูตร small ครับ ผมเห็นอาจารย์ใช้บ่อย
INDEX(A$2:A$13,SMALL(IF($B$2:$B$13=$E$1,ROW($A$2:$A$13)-ROW($A$2)
ส่วน row ที่มี s ผมพอเข้าใจอยู่บ้าง
มันคือการไปดึงข้อมูลในฟิลด์ a2 ถึง a13 มาโดยมีเงื่อนไข ใช่ใหมครับ

snasui · #10

Small เป็นการหาค่าที่น้อยที่สุดเป็นลำดับที่กำหนดครับ

ยกตัวอย่างเช่น

=Small(A1:A10,4)

หมายถึงหาค่าที่น้อยที่สุดเป็นลำดับที่ 4 จากช่วงข้อมูล A1:A10

ขอยกตัวอย่างจากสูตรในเซลล์ E5 ตามไฟล์ที่ผมแนบกลับไป สูตรคือ

=SMALL(IF($B$2:$B$13=$D$1,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(E$1:E4))

จะเห็นว่าแทนที่จะเป็น A1:A10 แล้วนำค่าที่น้อยที่สุดในลำดับที่ 4 มาแสดงตามที่ผมยกมาด้านบน เรากำหนดเป็นเงื่อนไข นั่นคือ

ถ้า $B$2:$B$13=$D$1 แล้ว ให้แสดงผลลัพธ์ของ ROW($A$2:$A$13)-ROW($A$2)+1 แล้วนำลำดับที่น้อยที่สุดที่เป็นผลลัพธ์ของ ROWS(E$1:E4) มาแสดง

จากสูตร ROW($A$2:$A$13)-ROW($A$2)+1 จะให้คำตอบเป็นค่าลำดับ เริ่มจาก 1 สิ้นสุดที่ 12 โดยมีลำดับการคำนวณดังนี้

ROW($A$2:$A$13) ให้ผลลัพธ์เป็นค่าลำดับจาก 2-13 นั่นคือ {2;3;4;5;6;7;8;9;10;11;12;13}
ROW($A$2) ให้ผลลัพธ์เป็นเลข 2 จากสูตร ROW($A$2:$A$13)-ROW($A$2) ก็จะกลายเป็น {2;3;4;5;6;7;8;9;10;11;12;13}-2 และจะได้ผลลัพธ์เป็น {0;1;2;3;4;5;6;7;8;9;10;11}
เมื่อนำ 1 เข้าไปบวกจะกลายเป็น {0;1;2;3;4;5;6;7;8;9;10;11}+1 และจะได้ผลลัพธ์เป็น {1;2;3;4;5;6;7;8;9;10;11;12}

ภาพรวมสูตรจะได้เป็น

=SMALL(IF($B$2:$B$13=$D$1,{1;2;3;4;5;6;7;8;9;10;11;12},ROWS(E$1:E4))

หมายความว่า

ถ้า B2=D1 ให้แสดงผลลัพธ์เป็น 1 ถ้าไม่ใช่ให้แสดงผลลัพธ์เป็น False
ถ้า B3=D1 ให้แสดงผลลัพธ์เป็น 2 ถ้าไม่ใช่ให้แสดงผลลัพธ์เป็น False
ถ้า B4=D1 ให้แสดงผลลัพธ์เป็น 3 ถ้าไม่ใช่ให้แสดงผลลัพธ์เป็น False
...
ถ้า B13=D1 ให้แสดงผลลัพธ์เป็น 12 ถ้าไม่ใช่ให้แสดงผลลัพธ์เป็น False

ซึ่งในไฟล์ที่ผมแนบไปนั้นใน E5 สูตรจะได้เป็น =SMALL({FALSE;FALSE;FALSE;4;FALSE;6;FALSE;FALSE;FALSE;10;FALSE;12},ROWS(E$1:E4))

คิดว่าพอจะแปลเองได้แล้วครับว่าผลลัพธ์ที่ได้ควรจะเป็นค่าใด เนื่องจากพอทราบอยู่แล้วว่า ROWS(E$1:E4) ให้ผลลัพธ์เป็นค่าใด

godman · #11

ขอบคุณครับ ได้ความรู้อีกแล้ว

วังวู ช่ง · #12

เรียน ท่านอาจารย์ ที่เคาลบ และท่านสมาชิกที่รู้ทังหลายที่รักแพงครับ ยอมรับว่าพี่น้องคนไทเก่งมากครับเกี่ยวกรับเลื่อง Computer ครับ ในนี้ผมขอรบกวนครับ
วิทีของ ท่านอาจารย์
1. ที่ I1=COUNTIF(B2:B13,E1)
2. ที่ F2=IF(ROWS(F$1:F1)>$I$1,"",INDEX(A$2:A$13,SMALL(IF($B$2:$B$13=$E$1,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(F$1:F1))))
แต่ผมมาแกะ F2=IF(ROWS(F$1:F1)>COUNTIF($B$2:$B$13),"",INDEX(A$2:A$13,SMALL(IF($B$2:$B$13=COUNTIF($B$2:$B$13),ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(F$1:F1))))

เหันว่าใช้ได้ครับ แต่บันหาคือว่า เมื่อจำนวนข้อมูณเพี่มขื้น มีวิทีทำย่างไลเพื่อให้สูดที่ทำดั่งก่าวรับรู้โดยอัดตะโนมัด

จุดประสงของผมก่อคือ เขียน VBE ไม่เป็นครับ เพียงยากรู้วิทีทำ แล้ว Record Macro มาใช้งานครับ ช่วยด้วยครับ ขอบคุณมากครับ

snasui · #13

สามารถใช้ Table โดยคลิกที่ A1 > Insert > Table > ตรง Where is data for your table? ให้เลือกช่วงข้อมูล $A$1:$C$13 > ทำเครื่องหมายตรง My table has headers > OK สำหรับวิธีนี้ไม่ต้องปรับสูตรใด ๆ เนื่องจากสูตรจะปรับเปลี่ยนให้กับปริมาณข้อมูลใน Table

อีกวิธีใช้การตั้งชื่อ (Range Name) มาช่วยได้ครับ โดย

เข้าเมนู Formulas > Name manager > New
ช่อง Name คีย์คำว่า Product
ช่อง Refers to: คีย์สูตร =OFFSET(List!$A$2,0,0,COUNTA(List!$A:$A)-1,3)
คลิก OK > Close

จากนั้นเปลี่ยนสูตรที่คีย์ไว้เดิม โดย

ที่ H1 คีย์
=COUNTIF(B:B,D1)
Enter
ที่ E2 คีย์
=IF(ROWS(E$1:E1)>$H$1,"",INDEX(INDEX(Product,0,MATCH(E$1,$A$1:$C$1,0)),SMALL(IF(INDEX(Product,0,2)=$D$1,ROW(Product)-MIN(ROW(Product))+1),ROWS(E$1:E1))))
Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

ดูไฟล์แนบประกอบกรณีใช้ Range Name ครับ

วังวู ช่ง · #14

ได้แล้วครับ ท่าน อาจารย์ ครับ ขอบคุณมากครับ

ขอให้นอนลับฝัณดีน๋ะ

วังวู ช่ง · #15

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

บันหาในที่นี้คืว่า ยากให้ Column EFG รับรู้สูดไปเลื้อยๆ โดยไม่ต้อง Copy ลง, ใน Excel นี้สามาดทำได้ไม่ครับ ช่วยดูให้ค้วยครับ ขอบคุณมากครับ

snasui · #16

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

วังวู ช่ง · #17

เรียน ท่านอาจารย์ ที่เคาลบครับ ถ้าพูดเถิงในเลื่องของ VBA or VBE ผมต้องยอมก่อนครับ เพาะไม่เก่งจิงๆ ผมใช้ผ่านมาคืใช้วิที Record Macro แล้วใช้ไปเสียๆครับ ขอบคุณท่าน อาจารย์ มากที่ให้ความใก้สิด และดูแลช่วยเหลือมาโดยตะหลอดครับ
สิ่งที่ผมเรียนมาตะหลอดแม่นผมต้องกานทำดั่งข้อมูณมี่แนบมาครับ

ขอบคุนมากครับ

snasui · #18

ลองดูตัวอย่าง Code ตามด้านล่างครับ

Code: Select all

Sub SelectDataByCondition()
    Dim rSource As Range
    Dim rTarget As Range
    Dim r As Range, rCon As Range
    Dim i As Integer
    With Sheets("List")
        .Range("AH12", .Range("BN" & Rows.Count)).ClearContents
        Set rSource = .Range("G12", .Range("G" & Rows.Count).End(xlUp))
        Set rCon = .Range("AI7")
        For Each r In rSource
            If r = rCon Then
                i = i + 1
                If .Range("AI12") = "" Then
                    Set rTarget = .Range("AI12")
                Else
                    Set rTarget = .Range("AI" & Rows.Count).End(xlUp).Offset(1, 0)
                End If
                rTarget.Resize(1, 32) = r.Offset(0, -5).Resize(1, 32).Value
                rTarget.Offset(0, -1) = i
            End If
        Next r
    End With
End Sub

วังวู ช่ง · #19

เรียน ท่านอาจารย์ ที่เคาลบครับ ขอบคุณท่านมากครับ ช่วยให้ผมสะดวกขื้นมากในกานทำงานครับ

โชกดีครับ ท่าน อาจารย์ ครับ

khainui · #20

ติดตามมาดูข้อมูลเพิ่มเติม ในกระทู้นี้จากกระทู้ที่ผมตั้งคำถามขึ้น http://www.snasui.com/viewtopic.php?f=3&t=2875

เคลียร์มากเลยครับอาจารย์ แต่ขอบ่นเจ้าของกระทู้นี้นิดนึงเรื่องภาษาไทย ไหงพิมพ์ผิดเยอะมาก

Excel Forum

EXCEL TOOLS