Page 1 of 1
Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Tue Feb 20, 2018 11:12 am
by phatchara
เรียนพี่ๆและสมาชิกทุกท่านครับ จากรูปและไฟล์แนบ ผมพยายามอยู่หลายวัน ใช้ Vlookup ก็ดี Index Match แบบไม่ค่อยชำนาญ ก็ลองแล้ว แต่ไม่ได้ผลครับ
จึงรบกวนช่วยออกแบบสูตรดังนี้โจทย์ด้านล่างด้วยครับ
1.เลือก สาขา...........
2.เลือก เดือน...........
3.ให้แสดงผล ชื่อสินค้า + ยอดขาย เรียงยอดขายจากมากไปหาน้อย
3.1 โดยกรอง สถานะสินค้า A ออกมา
3.2 โดยกรอง สถานะสินค้า A,B ออกมา
3.3 โดยกรอง ทุกสถานะสินค้า ออกมา
(3.1-3.3 คือแยกเป็น 3 สูตร 3 ตารางออกมา)
ขอบคุณล่วงหน้าครับผม
Re: Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Tue Feb 20, 2018 8:30 pm
by snasui
ดูตัวอย่างการทำ Database, PivotTable และ Slicer แทนการเขียนสูตรหรือวิธีอื่นได้ที่ Sheet2 ในไฟล์แนบ
การสร้าง Slicer ให้คลิกลงไปใน PivotTable ใด ๆ แล้วเข้าเมนู Options > Insert Slicer
การเชื่อม Slicer กับ PivotTable ใด ๆ ให้คลิกขวาที่ Slicer นั้น ๆ > PivotTable Connections > เลือก PivotTable ได้ตามต้องการ
Re: Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Wed Feb 21, 2018 9:44 am
by phatchara
snasui wrote: Tue Feb 20, 2018 8:30 pm
ดูตัวอย่างการทำ Database, PivotTable และ Slicer แทนการเขียนสูตรหรือวิธีอื่นได้ที่ Sheet2 ในไฟล์แนบ
การสร้าง Slicer ให้คลิกลงไปใน PivotTable ใด ๆ แล้วเข้าเมนู Options > Insert Slicer
การเชื่อม Slicer กับ PivotTable ใด ๆ ให้คลิกขวาที่ Slicer นั้น ๆ > PivotTable Connections > เลือก PivotTable ได้ตามต้องการ
ขอบคุณมากๆเลยครับ เป็นเครื่องมือที่ช่วยได้เยอะเลยครับ มัวแต่ไปมองหาสูตรอยู่นาน
ขอบคุณอีกครั้งครับผม ^0^
Re: Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Wed Feb 21, 2018 11:42 am
by phatchara
snasui wrote: Tue Feb 20, 2018 8:30 pm
ดูตัวอย่างการทำ Database, PivotTable และ Slicer แทนการเขียนสูตรหรือวิธีอื่นได้ที่ Sheet2 ในไฟล์แนบ
การสร้าง Slicer ให้คลิกลงไปใน PivotTable ใด ๆ แล้วเข้าเมนู Options > Insert Slicer
การเชื่อม Slicer กับ PivotTable ใด ๆ ให้คลิกขวาที่ Slicer นั้น ๆ > PivotTable Connections > เลือก PivotTable ได้ตามต้องการ
ต้องขอรบกวนอีกนิดนึงครับผม
จากที่ได้ลองมาปรับใช้ดูแล้วมันสามารถตอบโจทย์ได้ดีระดับนึง แต่ติดตรงที
1. ข้อมูลในส่วนของ ฟิลด์(สถานที่ หรือเรียกอีกอย่างคือ สาขา) จะมีค่อนข้างมาก ทำให้การจัดเรียงตารางใหม่เพื่อให้ได้รูปแบบตามที่ท่านแนะนำและเพื่อให้ตอบรับกับคำสั่ง PivotTable & Slicer ไม่ค่อยสะดวกเท่าที่ควร เพราะในการ Export ข้อมูลจากระบบจะได้ข้อมูลที่ใกล้เคียงกับตัวอย่างทีผมทำให้ดูครับ
2. และกรณีถ้าจะ Print ออกมาเป็น Report รายงาน (Paper) ในส่วนของรูปแบบหรือหน้าตาของ Slicer จะไม่สอดคล้องกับ report เท่าไหร่นัก
หมายเหตุ แต่ถ้าใช้ Slicer คู่กับ PivotTable เพื่อการ monitor ยอดขาย อันนี้มันค่อนข้างดีทีเดียวครับ
จากรูป
ผมจะเลือกสาขา และ เดือน เพื่อแสดงข้อมูลยอดขาย,ยอดค่าใช้จ่าย และ รายการสินค้าเรียงมากไปน้อย ซึ่งในรูป จะแยกเป็น 3 หัวข้อหลักๆคือ 1. ยอดขายสินค้าประจำเดือน / 2.ต้นทุน / แต่ในส่วนที่ 3 ยอดขายรายสินค้าไม่รู้จะเขียนสูตรให้แสดงพร้อมจัดเรียงมากไปน้อย ยังไง (ถ้าใช้ Slicer กับ PivotTable อาจจะเป็นการเลือกซ้ำซ้อนจาก memu ด้านบน)
ไม่ทราบว่าพอมีวิธีอื่นอีกหรือไม่ครับ T_T
Re: Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Wed Feb 21, 2018 2:11 pm
by puriwutpokin
ลองดูนะครับว่าได้ไหม
ที่ A10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX(($K$2:$K$12="A")*($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=B10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($B$10:$B10,B10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ B10=IFERROR(LARGE(IF($K$2:$K$12="A",IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(B$10:B10)),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ D10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX((($K$2:$K$12="A")+($K$2:$K$12="B"))*($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=E10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($E$10:$E10,E10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ E10=IFERROR(LARGE(IF($K$2:$K$12={"A","B"},IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(E$10:E10)),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ G10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX(($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=H10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($H$10:$H10,H10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ H10=IFERROR(LARGE(IF($K$2:$K$12<>"",IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(H$10:H10)),"") กด Ctrl+Shift+Enter คัดลอกลง
Re: Lookup หลายเงื่อนไข สร้างสูตรอย่างไรครับ
Posted: Wed Feb 21, 2018 8:24 pm
by phatchara
puriwutpokin wrote: Wed Feb 21, 2018 2:11 pm
ลองดูนะครับว่าได้ไหม
ที่ A10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX(($K$2:$K$12="A")*($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=B10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($B$10:$B10,B10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ B10=IFERROR(LARGE(IF($K$2:$K$12="A",IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(B$10:B10)),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ D10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX((($K$2:$K$12="A")+($K$2:$K$12="B"))*($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=E10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($E$10:$E10,E10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ E10=IFERROR(LARGE(IF($K$2:$K$12={"A","B"},IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(E$10:E10)),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ G10=IFERROR(INDEX($L$2:$L$12,LARGE(IF(INDEX(($J$2:$J$12=$B$2)*(INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0))=H10),0)=1,ROW($L$2:$L$12)-ROW($L$2)+1),COUNTIF($H$10:$H10,H10))),"") กด Ctrl+Shift+Enter คัดลอกลง
ที่ H10=IFERROR(LARGE(IF($K$2:$K$12<>"",IF($J$2:$J$12=$B$2,INDEX($M$2:$P$12,,MATCH($B$1,$M$1:$P$1,0)))),ROWS(H$10:H10)),"") กด Ctrl+Shift+Enter คัดลอกลง
ขอบคุณมากๆ เลยครับ
ลองทดลองแล้วตอบโจทย์ใช้ได้ครบถ้วนเลยครับ