ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น

[March201711]

เลือก Item แล้วให้ข้อมูลวิ่งมาโดยดูจาก file data มาเข้าที่ type ค่ะ
ยกตัวอย่างเช่น ถ้าเราเลือก Item 6 Type เป็น vehicle Dept เป็น VGEN จะต้องดึงข้อมูลอย่างไร คือตอนนี้ต้องมาดูเองว่าอยู่ใน type ไหน แล้วมาใข้สูตรที่ type นั้นค่ะ

[koko_excel_tip]

ที่ P5=-INDEX(Data!$A$7:$AZ$32,MATCH($B$1,Data!$A$7:$A$32,0),MATCH("FY"&'6_No_staff'!P$4,Data!$A$6:$AZ$6,0))
กด [Enter] แล้วลากสูตรมาทางขวา ตามไฟล์แนบครับ

[March201711]

ค่ะ ถ้าเปลี่ยน Type ที่ sheet data เป็น Computer - Hardware (cell D12) แล้ว ทำไมที่ sheet 6_No_Staff ไม่วิ่งไปจับที่ Type Computer -Hardware ตามด้วยน่ะคะ

[koko_excel_tip]

ที่ C5=IFERROR(-INDEX(Data!N$7:N$32,SMALL(IF((Data!$A$7:$A$32='6_No_staff'!$B$1)*(Data!$D$7:$D$32='6_No_staff'!$B5),ROW(Data!$A$7:$A$32)-ROW(Data!$A$7)+1),1)),0) แล้วกด [Ctrl]+[Shift]+[Enter]
ลากสูตรลงมา และไปทางขวาถึงเดือน Dec

ที่ P5=IFERROR(-INDEX(Data!$AM$7:$AM$32,SMALL(IF((Data!$A$7:$A$32='6_No_staff'!$B$1)*(Data!$D$7:$D$32='6_No_staff'!$B5),ROW(Data!$A$7:$A$32)-ROW(Data!$A$7)+1),1)),0) แล้วกด [Ctrl]+[Shift]+[Enter]
ลากสูตรลงมา

ที่ Q5=IFERROR(-INDEX(Data!$AZ$7:$AZ$32,SMALL(IF((Data!$A$7:$A$32='6_No_staff'!$B$1)*(Data!$D$7:$D$32='6_No_staff'!$B5),ROW(Data!$A$7:$A$32)-ROW(Data!$A$7)+1),1)),0) แล้วกด [Ctrl]+[Shift]+[Enter]
ลากสูตรลงมา

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

[March201711]

ขอบคุณมากค่ะ แต่ใช้สูตร array แล้ว ทำให้ข้อมูลหน่วงๆข้ามากเลยค่ะ ถ้าใช้สูตรนี้กับข้อมูลที่มีเป็นหมื่นๆกว่ารายการ เครื่องจะแฮงค์ๆ คำนวณช้ามาก ถ้าจะไม่ใช้สูตร array มี สูตรอื่นที่ใช้ได้บ้างไหมคะ

[snasui]

ค่ะ ถ้าเปลี่ยน Type ที่ sheet data เป็น Computer - Hardware (cell D12) แล้ว ทำไมที่ sheet 6_No_Staff ไม่วิ่งไปจับที่ Type Computer -Hardware ตามด้วยน่ะคะ

ตัวอย่างสูตรที่ C5 ครับ

=-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5)

[March201711]

ทำตามสูตรที่อาจารย์ให้มา ได้ยอดเป็น 0 ทั้งเดือนเลยค่ะ

[snasui]

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

[March201711]

ค่ะ แล้วตรง P5 กับ Q5 ยอดดึงมาไม่ถูกต้องค่ะ

[snasui]

P5 กับ Q5 ค่าเป็นเท่าใด นำมาจากคอลัมน์ไหนครับ

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

[March201711]

ต้องได้ค่า -1,070,000 โดยดึงข้อมูลจาก sheet data column AM ของ ปี 2020 และ column AZ ของปี 2021

[snasui]

ตัวอย่างสูตรที่ P5 ครับ

=-SUMIFS(Data!AM$7:AM$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5)

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

ฟังก์ชั่นพวกนี้เป็นฟังก์ชั่นพื้นฐานใช้บ่อยมาก ควรทำความเข้าใจและใช้ให้เป็น

ฟังก์ชั่นในกลุ่มนี้ที่จำเป็นและศึกษาไปด้วยกันคือ Sumif, Countif, Countifs ครับ

[March201711]

ถ้าเราจะเปลี่ยนค่า Data!AM$7:AM$32 โดยจับข้อมูล ที่ sheet data column AM6 FY2020 4 ตัวสุดท้าย ซึ่งจะตรงกันกับ sheet 6_No_staff column P4 เป็น2020 ต้องปรับเปลี่ยนสูตรอย่างไรค่ะ เพราะ ว่ามีFYหลายๆปี ต่อๆกัน จึงต้องมานั่งปรับทุกปี ทุกคอลัมน์เลยค่ะ

[snasui]

ตัวอย่างการปรับสูตรที่ P5 ครับ

=-SUMIFS(INDEX(Data!$N$7:$AZ$32,0,MATCH("*"&RIGHT(P$4,4),Data!$N$6:$AZ$6,0)),Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5)

[March201711]

ได้แล้วค่ะอาจารย์
มีปัญหาต่อค่ะ ถ้าเราจะดึงข้อมูลตรง Sheet BaseAlloc column B ให้มาแสดงใน sheet นี้ ที่ column U โดยดูว่าเป็น Basis อะไร และดูว่าเป็น แผนกไหน ใช้สูตร index+match ดึงมาแล้ว ข้อมูลเป็น 0 ค่ะ
เพราะมีการเปลี่ยนแปลง allocation ทุกเดือน ทุกแผนก ต้องมาแก้มือที่ column U ทุกครั้งไปค่ะ

[snasui]

ตัวอย่างสูตรที่ U12 ครับ

=VLOOKUP(S12,OFFSET(BaseAlloc!$A$2,MATCH($B$2,BaseAlloc!$A$2:$A$1000,0),0,1000,2),2,0)

Enter

ลักษณะของชีต BasAlloc ควรจะเป็น Database อย่างน้อยจะต้องมี 3 คอลัมน์ คือ Allocate name, PC code in PSGL และ % of Allocate unit หากไม่เป็น Database จะใช้สูตรซับซ้อนโดยไม่จำเป็นครับ

[koko_excel_tip]

ที่ช่อง U12=IF(OFFSET(Data!$A$7,MATCH('6_No_staff'!$B$1,Data!$A$7:$A$32,0),3)=B12,INDEX(INDIRECT("BaseAlloc!$B"&MATCH($B$2,BaseAlloc!$A$1:$A$172,0)):BaseAlloc!$B$173,MATCH(S12,INDIRECT("BaseAlloc!$A"&MATCH($B$2,BaseAlloc!$A$1:$A$172,0)):BaseAlloc!$A$173,0)),0)
กด [Enter] แล้วลากสูตรลงมา จะได้ตรงกับ Type ด้วยครับ
ตามไฟล์แนบ

[March201711]

ค่ะ อาจารย์ file นี้ได้จากแผนกอื่นเป็น data ดิบค่ะ
ถ้าเปลี่ยนเป็นเพิ่ม 3 column อย่างที่อาจารย์แนะนำ ต้องแก้สูตรอย่างไรคะ

[March201711]

ขอบคุณ คุณ koko excel ที่แนะนำค่ะ แต่งงๆสูตร แล้วข้อมูลไม่มาแสดงที่ cell C12:Q19 ค่ะ ต้องมียอดมาแสดงค่ะ

[snasui]

เติมคอลัมน์ A ในชีต BaseAlloc ให้เต็ม เซลล์ว่างต้องเติมค่าให้เหมือนค่าด้านบน จากนั้นปรับสูตรที่ U12 เป็นด้านล่างครับ

=LOOKUP(2,1/((BaseAlloc!$A$2:$A$1000=$B$2)*(BaseAlloc!$B$2:$B$1000=$S12)),BaseAlloc!$C$2:$C$1000)