Page 1 of 2
ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Fri Jul 20, 2018 6:08 pm
by March201711
เลือก Item แล้วให้ข้อมูลวิ่งมาโดยดูจาก file data มาเข้าที่ type ค่ะ
ยกตัวอย่างเช่น ถ้าเราเลือก Item 6 Type เป็น vehicle Dept เป็น VGEN จะต้องดึงข้อมูลอย่างไร คือตอนนี้ต้องมาดูเองว่าอยู่ใน type ไหน แล้วมาใข้สูตรที่ type นั้นค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Fri Jul 20, 2018 8:59 pm
by 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] แล้วลากสูตรมาทางขวา ตามไฟล์แนบครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Fri Jul 20, 2018 9:49 pm
by March201711
ค่ะ ถ้าเปลี่ยน Type ที่ sheet data เป็น Computer - Hardware (cell D12) แล้ว ทำไมที่ sheet 6_No_Staff ไม่วิ่งไปจับที่ Type Computer -Hardware ตามด้วยน่ะคะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Fri Jul 20, 2018 11:26 pm
by 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]
ลากสูตรลงมา
ตามไฟล์แนบครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Fri Jul 20, 2018 11:48 pm
by March201711
ขอบคุณมากค่ะ แต่ใช้สูตร array แล้ว ทำให้ข้อมูลหน่วงๆข้ามากเลยค่ะ ถ้าใช้สูตรนี้กับข้อมูลที่มีเป็นหมื่นๆกว่ารายการ เครื่องจะแฮงค์ๆ คำนวณช้ามาก ถ้าจะไม่ใช้สูตร array มี สูตรอื่นที่ใช้ได้บ้างไหมคะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 5:07 am
by snasui
March201711 wrote: Fri Jul 20, 2018 9:49 pm
ค่ะ ถ้าเปลี่ยน 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)
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 6:45 am
by March201711
ทำตามสูตรที่อาจารย์ให้มา ได้ยอดเป็น 0 ทั้งเดือนเลยค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 6:54 am
by snasui
ลองตรวจสอบ
ทุกอักขระว่าผมเขียนสูตรไว้ว่าอย่างไร ทางทีดีให้คัดลอกไปวาง ไม่เช่นนั้นจะเกิดโอกาสผิดพลาดได้ครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:01 am
by March201711
ค่ะ แล้วตรง P5 กับ Q5 ยอดดึงมาไม่ถูกต้องค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:06 am
by snasui
P5 กับ Q5 ค่าเป็นเท่าใด นำมาจากคอลัมน์ไหนครับ
ถ้านำมาจากคอลัมน์อื่นด้วยเงื่อนไขเดียวกันก็ต้องปรับสูตรให้ตรงกับคอลัมน์ที่จะนำข้อมูลมาแสดง ไม่ใช่คัดลอกสูตรไปวางโดยไม่ปรับครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:24 am
by March201711
ต้องได้ค่า -1,070,000 โดยดึงข้อมูลจาก sheet data column AM ของ ปี 2020 และ column AZ ของปี 2021
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:31 am
by snasui
ตัวอย่างสูตรที่ P5 ครับ
=-SUMIFS(Data!AM$7:AM$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5)
สังเกตตรงที่ผมระบายสีในสูตร หากต้องการนำคอลัมน์ไหนของข้อมูลต้นทางมาแสดงก็ให้เปลี่ยนเป็นคอลัมน์นั้น
ฟังก์ชั่นพวกนี้เป็นฟังก์ชั่นพื้นฐานใช้บ่อยมาก ควรทำความเข้าใจและใช้ให้เป็น
ฟังก์ชั่นในกลุ่มนี้ที่จำเป็นและศึกษาไปด้วยกันคือ Sumif, Countif, Countifs ครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:40 am
by March201711
ถ้าเราจะเปลี่ยนค่า Data!AM$7:AM$32 โดยจับข้อมูล ที่ sheet data column AM6 FY2020 4 ตัวสุดท้าย ซึ่งจะตรงกันกับ sheet 6_No_staff column P4 เป็น2020 ต้องปรับเปลี่ยนสูตรอย่างไรค่ะ เพราะ ว่ามีFYหลายๆปี ต่อๆกัน จึงต้องมานั่งปรับทุกปี ทุกคอลัมน์เลยค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 7:53 am
by 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)
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 8:51 am
by March201711
ได้แล้วค่ะอาจารย์
มีปัญหาต่อค่ะ ถ้าเราจะดึงข้อมูลตรง Sheet BaseAlloc column B ให้มาแสดงใน sheet นี้ ที่ column U โดยดูว่าเป็น Basis อะไร และดูว่าเป็น แผนกไหน ใช้สูตร index+match ดึงมาแล้ว ข้อมูลเป็น 0 ค่ะ
เพราะมีการเปลี่ยนแปลง allocation ทุกเดือน ทุกแผนก ต้องมาแก้มือที่ column U ทุกครั้งไปค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 9:24 am
by 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 จะใช้สูตรซับซ้อนโดยไม่จำเป็นครับ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 9:36 am
by 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 ด้วยครับ
ตามไฟล์แนบ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 10:07 am
by March201711
ค่ะ อาจารย์ file นี้ได้จากแผนกอื่นเป็น data ดิบค่ะ
ถ้าเปลี่ยนเป็นเพิ่ม 3 column อย่างที่อาจารย์แนะนำ ต้องแก้สูตรอย่างไรคะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 10:10 am
by March201711
ขอบคุณ คุณ koko excel ที่แนะนำค่ะ แต่งงๆสูตร แล้วข้อมูลไม่มาแสดงที่ cell C12:Q19 ค่ะ ต้องมียอดมาแสดงค่ะ
Re: ดึงข้อมูลมากกว่า 3 เงื่อนไขหรืแมากกว่านั้น
Posted: Sat Jul 21, 2018 10:12 am
by 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)