Excel Forum

สวัสดีครับ พอดีจำเป็นต้องทำ report เลยขอรบกวนช่วยทำสูตร อาจจะซับซ้อนหน่อยครับ

คือ ( สรุปเป็นเดือน + ว่ามี job ที่ focus + Fruit แยกตาม type ) เท่าไหร่

เบื้องต้นทำ Result ที่ L, M, N มาให้แล้ว แต่อยากได้สูตรที่ dynamic หาค่าให้ครับผม

https://i.postimg.cc/SNMc9gZh/2021-09-20-23-06-57.jpg

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

ที่ L2 คีย์

=SUMPRODUCT(--ISNUMBER(MATCH(LEFT($A$2:$A$31,2),$I$2:$I$5,0)),--ISNUMBER(MATCH($B$2:$B$31,INDEX($E$2:$G$30,0,RIGHT(L$1)),0)),--(TEXT($C$2:$C$31,"MMMM")=$K2))

Enter > Copy ไปทางขวาและลงด้านล่าง

ค่าเป็น 0 หมดเลยครับอาจารย์ แก้ตรงไหนเหรอครับ

CyTrix wrote: Sun Jul 28, 2024 9:44 am ค่าเป็น 0 หมดเลยครับอาจารย์ แก้ตรงไหนเหรอครับ

ขออนุญาตนะครับ ท่านเจ้าของคำถาม ช่วยแนบ ไฟล์ ที่แสดงผลลัพธ์ ไม่ถูกต้องนั้นมาด้วยครับ

a) ถ้าใช้ Excel 2007 ขึ้นไป สูตรที่ อาจารย์ คนควน แสดงไปควรถูกต้อง และแสดงผลลัพธ์ตามหลักการ


b) มีคำถาม -- จากภาพที่ท่านเจ้าของคำถามแนบมา ตรงเดือน May คอลัมน์ L ผลลัพธ์ได้ 1 เลข 1 นัน้ มาได้อย่างไรครับ



** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

สวัสดีครับผม ขออนุญาตอธิบายแบบนี้ครับ

ขออนุญาตนะครับ ท่านเจ้าของคำถาม ช่วยแนบ ไฟล์ ที่แสดงผลลัพธ์ ไม่ถูกต้องนั้นมาด้วยครับ
Ans: จริงก็คือไฟล์แนบที่ post แล้ว copy สูตรของอาจารย์เข้าไปที่ L2 ครับ

a) ถ้าใช้ Excel 2007 ขึ้นไป สูตรที่ อาจารย์ คนควน แสดงไปควรถูกต้อง และแสดงผลลัพธ์ตามหลักการ
ANS: ใช้ Excel version นี้ครับ Microsoft® Excel® for Microsoft 365 MSO (Version 2405 Build 16.0.17628.20006) 64-bit
ผลคือเป็น 0 ตามรูปด้านบนครับ

b) มีคำถาม -- จากภาพที่ท่านเจ้าของคำถามแนบมา ตรงเดือน May คอลัมน์ L ผลลัพธ์ได้ 1 เลข 1 นัน้ มาได้อย่างไรครับ
ANS: ผม count แบบ manual มา โดยตรรกะคือ
Column L = ให้นับ แต่ละเดือน-2024, มี job ที่ขึ้นต้นตาม column I, แล้วมี Fruit ตาม column E, เป็นจำนวนกี่ job
Column M = ให้นับ แต่ละเดือน-2024, มี job ที่ขึ้นต้นตาม column I, แล้วมี Fruit ตาม column F, เป็นจำนวนกี่ job
Column N = ให้นับ แต่ละเดือน-2024, มี job ที่ขึ้นต้นตาม column I, แล้วมี Fruit ตาม column G, เป็นจำนวนกี่ job

..

สูตรตามไฟล์ ที่ท่านเจ้าของ คำถามแนบมา ที่อาจารย์ คนควนเขียนไปนั้น ถูกต้องแล้วครับ

a) ให้ Copy สูตรที่ L2 ไปทางขวา และลงไปข้างล่าง ในเซลล์ที่เกี่ยวข้อง


ิb) และผลลัพธ์ของ May คอลัมน์ L (L7) ตามภาพแรกที่ท่านเจ้าของคำถามได้โพสต์มาสอบถาม

ต้องเป็น 0 ไม่ใช่ 1


** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

ขอบคุณท่าน norkaz และท่านคนควน มากๆ ครับ ได้ผลตามโจทย์เลยครับ ต้องขออภัยไฟล์ตั้งต้นผมนับ manual มาผิด (human error)

และเจออาการแปลกๆ คือที่ column A B และ C ถ้าแค่ไปดับเบิ้ลคลิ๊ก ให้คล้ายมีการแก้ cell, สูตรบน L M N จะแสดงผลเป็น 0 ทั้งหมด ต้องปิดเปิดไฟล์ใหม่ถึงจะกลับมามีค่า

ขอสอบถามเพิ่มเติม สามารถไม่ต้องระบุ row ได้ไหมครับ เช่น $A$2:$A$31 $C$2:$C$31 สามารถเขียนสูตรเป็น $A:$A $C:$C ได้ไหม เนื่องจากจะเอาไปประยุกต์ใช้ที่ไฟล์มี row เยอะหลักพัน-หมื่นครับ

...

การอ้างอิงแถวเยอะๆ ทั้งชุดแบบนั้น สามารถทำได้ แต่ไฟล์ อาจจะหน่วงมาก

ในการทำงานจริง ถ้ามีแถวเยอะๆ ควรแยกสูตรออกเป็นคอลัมน์ แล้วอ้างอิง ไปตามคอลัมน์นั้นๆ

จะช่วยให้ การคำนวณเร็วขึ้นและตรวจสอบง่ายกว่ามาก

ลองแนบไฟล์โพสต์มาสอบถาม ได้ครับ



** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

norkaz wrote: Sun Jul 28, 2024 10:58 am ...

การอ้างอิงแถวเยอะๆ ทั้งชุดแบบนั้น สามารถทำได้ แต่ไฟล์ อาจจะหน่วงมาก

ในการทำงานจริง ถ้ามีแถวเยอะๆ ควรแยกสูตรออกเป็นคอลัมน์ แล้วอ้างอิง ไปตามคอลัมน์นั้นๆ

จะช่วยให้ การคำนวณเร็วขึ้นและตรวจสอบง่ายกว่ามาก

ลองแนบไฟล์โพสต์มาสอบถาม ได้ครับ

ขอบคุณมากครับ เดี๋ยวขอใช้ไปสักพักดูก่อนครับ เรื่องไฟล์ก็ตัวอย่างเหมือนเดิม แต่ row เยอะเป็นพันเท่านั้นครับ

ส่วนสูตรแสดงผลเป็น 0 ทุกครั้งที่ edit cell A1 to C30 ยังแก้ไม่ได้เลยครับ แค่ดับเบิ้ลคลิกเล่นๆ ปุ๊บ, L to N กลายเป็น 0 ตลอด

...

กรณีมีข้อมูลมากๆ ใช้คอลัมน์ไปช่วย ลองปรับไปตามนี้



D2 Helper

=IF(C2="","",TEXT(C2,"mmmm"))

Copy ลงไป

=============


E2 Helper

=COUNT(MATCH(LEFT(A2,2),$K$2:$K$9999,0))

Copy ลงไป

=============


F2 Helper

=IF(B2="","",AGGREGATE(15,6,{1,2,3}/(B2=$G$2:$I$9999),1))

Copy ลงไป

=============

N2

=SUMIFS($E$2:$E$9999,$D$2:$D$9999,$M2,$F$2:$F$9999,COLUMNS($N2:N2))

Copy ไปขวา และลงล่าง



** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

norkaz wrote: Sun Jul 28, 2024 11:16 am ...

กรณีมีข้อมูลมากๆ ใช้คอลัมน์ไปช่วย ลองปรับไปตามนี้



D2 Helper

=IF(C2="","",TEXT(C2,"mmmm"))

Copy ลงไป

=============


E2 Helper

=COUNT(MATCH(LEFT(A2,2),$K$2:$K$9999,0))

Copy ลงไป

=============


F2 Helper

=IF(B2="","",AGGREGATE(15,6,{1,2,3}/(B2=$G$2:$I$9999),1))

Copy ลงไป

=============

N2

=SUMIFS($E$2:$E$9999,$D$2:$D$9999,$M2,$F$2:$F$9999,COLUMNS($N2:N2))

Copy ไปขวา และลงล่าง



** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

ขอบพระคุณมากๆ ครับ ขอลองเล่นดูก่อน หากติดขัดตรงไหนจะขอปรึกษาอีกครั้งครับ

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

...


L2

=SUMPRODUCT((ROWS(L$2:L2)=MONTH($C$2:$C$31)*ISNUMBER(MATCH(LEFT($A$2:$A$31,2),$I$2:$I$5,0)))*ISNUMBER(MATCH($B$2:$B$31,INDEX($E$2:$G$31,0,COLUMNS($L2:L2)),0)))


** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...