ต้องการสรุปข้อมูลยอดรวมค่าเที่ยวของคนแต่ละคนพร้อมเรียงมากไปหาน้อย

[hanuaggie61]

ผมมีข้อมูลเป็นตารางการวิ่งงานของคนขับรถในแต่ละวัน โดยทำเป็น Sheet ละเดือน

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

เบื้องต้นผมทำเป็น Pivot Table ตามคอลัมภ์ J-L
แต่พบปัญหาเวลา Copy Sheet ไปเป็นเดือนใหม่ Pivot Table มันยังอ้างอิงไปยังตารางข้อมูลของเก่าอยู่
ทำให้ต้องมาเปลี่ยนแหล่งที่มาของตารางใหม่ทุกครั้ง (ซึ่ง User ปกติทำไม่เป็น)

ผมเลยรบกวนขอแบบที่เป็นสูตรหน่อยได้ไหมครับ
ผมพยายามยำ Uniqe กับ Sort แล้ว แต่ทำแบบโชว์หลายคอลัมภ์ไม่ได้

ผลลัพธ์ที่ต้องการตาม Pivot คอลัมภ์ J-L เลยครับ

รายงานสรุปการวิ่งงาน - Test.xlsx

Screenshot Pivot.png

[snasui]

กรุณา Update Version ของ Excel ที่ใช้ตาม Link นี้ด้วย https://snasui.com/viewtopic.php?p=103177#p103177 เพื่อจะได้ตอบให้ตรงกับ Version ที่ใช้งานอยู่จริงครับ

[norkaz]

...

รบกวนท่านเจ้าของคำถาม ช่วยระบุเวอร์ชั่นตามที่ อาจารย์ คนควน ได้แจ้งไปครับ

หากใช้ Excel 365 ลองปรับไปแบบนี้

J3
=LET(d,D3:D999,h,H3:H999,u,UNIQUE(FILTER(d,h<>"")),VSTACK(SORT(HSTACK(u,COUNTIFS(d,u),SUMIFS(h,d,u)),{3},-1),HSTACK("Grand Total",SUM(COUNTIFS(d,u)),SUM(h))))


ถ้าใช้ เวอร์ชั่น ต่ำกว่า Excel 365

N3 (Helper)
=IFERROR(INDEX(D:D,AGGREGATE(15,6,ROW($D$3:$D$999)/(MATCH($D$3:$D$999,$D$3:$D$999,0)=ROW($D$3:$D$999)-ROW($D$3)+1),ROWS($N$3:N3))),"")
Copy ลงไป

J3
=INDEX(N:N,MOD(AGGREGATE(14,6,SUMIFS($H$3:$H$999,$D$3:$D$999,$N$3:$N$999)+ROW($H$3:$H$999)/10^6,ROWS($J$3:J3)),1)*10^6)
Copy ลงไป

K3
=COUNTIFS($D$3:$D$999,"<>""",$D$3:$D$999,J3)
Copy ลงไป

L3
=SUMIFS($H$3:$H$999,$D$3:$D$999,J3)
Copy ลงไป

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

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

...

[norkaz]

ผมมีข้อมูลเป็นตารางการวิ่งงานของคนขับรถในแต่ละวัน โดยทำเป็น Sheet ละเดือน

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

เบื้องต้นผมทำเป็น Pivot Table ตามคอลัมภ์ J-L
แต่พบปัญหาเวลา Copy Sheet ไปเป็นเดือนใหม่ Pivot Table มันยังอ้างอิงไปยังตารางข้อมูลของเก่าอยู่
ทำให้ต้องมาเปลี่ยนแหล่งที่มาของตารางใหม่ทุกครั้ง (ซึ่ง User ปกติทำไม่เป็น)

ผมเลยรบกวนขอแบบที่เป็นสูตรหน่อยได้ไหมครับ
ผมพยายามยำ Uniqe กับ Sort แล้ว แต่ทำแบบโชว์หลายคอลัมภ์ไม่ได้

ผลลัพธ์ที่ต้องการตาม Pivot คอลัมภ์ J-L เลยครับ
รายงานสรุปการวิ่งงาน - Test.xlsx
Screenshot Pivot.png

ปัญหาของท่านเจ้าของคำถาม ประเด็นหลักๆ คือการต้องมาคอย เปลี่ยนแหล่งข้อมูลเดิม

หากไม่ต้องการสูตร แบบซับซ้อน ก็ใช้วิธีการ PivotTable แบบเดิม ซึ่งน่าจะสะดวกกว่า

โดยอาศัยชีทที่ทำปัจจุบันเป็น Template หลักเก็บไว้ เผื่อช่วงข้อมูลไปเยอะๆ

1. เมื่อทำเสร็จแล้ว แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day1

2. เมื่อมีข้อมูลใหม่ ก็นำมาแทนที่ข้อมูล ใน ชีท Temaplate หลัก แล้ว Refresh แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day2

3. เมื่อมีข้อมูลใหม่ ก็นำมาแทนที่ข้อมูล ใน ชีท Temaplate หลัก แล้ว Refresh แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day3

หากไม่ต้องการ Refresh บ่อยๆ สามารถตั้งค่า Refresh ทุกครั้งอัตโนมัติ เมื่อเปิดไฟล์

a) คลิกขวาที่ PivotTable

b) PivotTable Options

c) ที่แท็บ Data ติ๊กที่ Refresh data when opening the file -> OK

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

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

...

[hanuaggie61]

กรุณา Update Version ของ Excel ที่ใช้ตาม Link นี้ด้วย https://snasui.com/viewtopic.php?p=103177#p103177 เพื่อจะได้ตอบให้ตรงกับ Version ที่ใช้งานอยู่จริงครับ

Update ข้อมูล Profile เรียบร้อยแล้วครับ
ครั้งนี้ผมน่าจะพิมพ์ยาวจนลืมแจ้งเวอร์ชั่นครับ

[hanuaggie61]

...
รบกวนท่านเจ้าของคำถาม ช่วยระบุเวอร์ชั่นตามที่ อาจารย์ คนควน ได้แจ้งไปครับ
หากใช้ Excel 365 ลองปรับไปแบบนี้

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

J3
=LET(d,D3:D999,h,H3:H999,u,UNIQUE(FILTER(d,h<>"")),VSTACK(SORT(HSTACK(u,COUNTIFS(d,u),SUMIFS(h,d,u)),{3},-1),HSTACK("Grand Total",SUM(COUNTIFS(d,u)),SUM(h))))

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

ขอบคุณมากครับ สูตรใช้ได้ตรงตามที่ต้องการครับ (คำถามที่แล้ว ก็ได้ VStack กับ Hstack มาช่วยเหมือนกันได้แนวทางแล้วครับ)

* สอบถามสูตร Sort ครับ ตรงค่าที่ 2 (Sort_Index) ที่เลือกให้เรียงตามคอลัมน์ที่ 3 ทำไมถึงต้องใส่เครื่องหมายวงเล็บปีกกา ที่เหมือนเป็น Array เหรอครับ ?
ผมได้ทดลองเอาออกก็ยังแสดงผลปกติ (ลองเพิ่มข้อมูลพขร.อีก 1 คน ก็ยังใช้ได้ปกติ)

Sort_Ask.png

[hanuaggie61]

ปัญหาของท่านเจ้าของคำถาม ประเด็นหลักๆ คือการต้องมาคอย เปลี่ยนแหล่งข้อมูลเดิม
หากไม่ต้องการสูตร แบบซับซ้อน ก็ใช้วิธีการ PivotTable แบบเดิม ซึ่งน่าจะสะดวกกว่า
โดยอาศัยชีทที่ทำปัจจุบันเป็น Template หลักเก็บไว้ เผื่อช่วงข้อมูลไปเยอะๆ

1. เมื่อทำเสร็จแล้ว แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day1
2. เมื่อมีข้อมูลใหม่ ก็นำมาแทนที่ข้อมูล ใน ชีท Temaplate หลัก แล้ว Refresh แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day2
3. เมื่อมีข้อมูลใหม่ ก็นำมาแทนที่ข้อมูล ใน ชีท Temaplate หลัก แล้ว Refresh แล้ว Move or Copy ไปไว้อีกชีท ตั้งชื่อเช่น Day3

เป็นเทคนิคที่เป็นความรู้เลยครับอันนี้
ทำใน Master แล้ว Copy Sheet ไปเก็บเป็นข้อมูลแต่ละเดือนใน Sheet ใหม่ ซึ่งไม่มีการแก้ไขแล้ว
(หรือต่อให้มีก็ Copy ข้อมูลไปวางใน Master เพื่อดึงใหม่ก็ได้)
แต่ต้องอธิบาย User หรือ ทำ Note ไว้นิดนึง

[norkaz]

...


{3} หมายถึง คอลัมน์ ที่3 ถูกต้องตามที่สอบถามมาครับ หรือ คีย์ 3 ไปตรงๆ ก็ได้ครับ กรณีเลือก Sort เพียงคอลัมน์เดียว

ถ้า Sort แบบหลายคอลัมน์พร้อมกันในสูตรนั้นๆ เช่น ให้Sort คอลัมน์ที่3 ก่อน แล้วกำหนดให้ Sort คอลัมน์ที่ 2 ตรงนั้นจะคีย์ 3,2 ไม่ได้ จำเป็นต้อง คีย์ {3,2}

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

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

[hanuaggie61]

...

{3} หมายถึง คอลัมน์ ที่3 ถูกต้องตามที่สอบถามมาครับ หรือ คีย์ 3 ไปตรงๆ ก็ได้ครับ กรณีเลือก Sort เพียงคอลัมน์เดียว
ถ้า Sort แบบหลายคอลัมน์พร้อมกันในสูตรนั้นๆ เช่น ให้Sort คอลัมน์ที่3 ก่อน แล้วกำหนดให้ Sort คอลัมน์ที่ 2 ตรงนั้นจะคีย์ 3,2 ไม่ได้ จำเป็นต้อง คีย์ {3,2}
** สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***
แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

ขอบคุณครับ
- ถ้ากรองชั้นเดียว ใช้หรือไม่ใช้ก็ได้
- ถ้ากรองหลายชั้น ต้องใช้ {} เท่านั้น