แยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ Data > Text to columns เข้ามาช่วยได้ และถ้าหากในเซลล์นั้นคั่นแต่ละส่วนด้วยอักขระใด ๆ แล้วจะยิ่งแยกได้โดยง่าย แต่หากว่าเซลล์นั้นมีค่าซ้ำกันหลายค่าแต่เราต้องการนำค่าที่ซ้ำกันนั้นมาแสดงเพียงค่าเดียวย่อมจะมีความยุ่งยากลำบากกว่าปกติ

ยกตัวอย่างตามภาพด้านล่างจะมีอักขระในเซลล์ A1 คั่นแต่ละส่วนด้วยเครื่องหมายคอมม่า ซึ่งจะพบว่ามีหลายส่วนเป็นค่าที่ซ้ำกัน หากเราต้องการแยกค่าเหล่านั้นออกมาแสดงในแต่ละเซลล์โดยผลลัพธ์เริ่มตั้งแต่ B2 เรียงลงไปด้านล่างโดยต้องการให้ค่าที่ซ้ำกันแสดงเพียงค่าเดียว เราจำเป็นจะต้องใช้สูตร Array เข้ามาช่วย

ภาพแสดงแยกข้อมูลในเซลล์โดยให้ค่าที่ซ้ำแสดงค่าเดียว

ที่เซลล์ B2 เราสามารถเขียนสูตรได้ดังนี้

=IFERROR(INDEX(TRIM(MID(SUBSTITUTE( $A$2,",",REPT(" ",50)),50(ROW(INDIRECT("1:50"))-1)+1,50)),SMALL( IF(FREQUENCY(MATCH(TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)),50(ROW( INDIRECT("1:50"))-1)+1,50)),TRIM(MID(SUBSTITUTE($A$2,",",REPT(" ",50)), 50*(ROW(INDIRECT("1:50"))-)+1,50)),0),ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),ROWS(B$2:B2))),"")

Ctrl+Shift+Enter > คัดลอกสูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์ตัวอย่างไปศึกษาได้ตามสะดวก

กรณีมีปัญหาเกี่ยวกับการใช้งาน Excel และ VBA สามารถสอบถามได้ที่ Excel Forum

การแยกข้อมูลเป็นรายชีตพร้อมกันในคราวเดียว

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

VDO การแยกข้อมูลเป็นรายชีตพร้อมกันในคราวเดียว

การจัดเรียงบ้านเลขที่

การจัดเรียงบ้านเลขที่นั้นไม่สามารถทำได้อย่างง่าย ๆ เนื่องจากบ้านเลขที่มักจะมีเครื่องหมายทับ (/) ปนอยู่ด้วย ทำให้เครื่องมือของ Excel ที่ใช้ในการจัดเรียงไม่สามารถให้ผลลัพธ์ที่ต้องการได้

การจัดเรียงบ้านเลขที่จึงต้องอาศัยวิธีการอื่นเข้ามาช่วย เช่นการแบ่งบ้านเลขที่ออกเป็น 2 คอลัมน์แล้วค่อยจัดเรียง หรือสามารถใช้สูตร Array เข้ามาช่วยได้

ภาพการเรียงบ้านเลขที่จากน้อยไปหามากด้วยสูตร

 

จากภาพด้านบนจะเป็นการนำข้อมูลบ้านเลขที่ในคอลัมน์ A มาเรียงใหม่ โดยให้เรียงจากน้อยไปหามาก

สูตรในเซลล์ C2 คือ

=INDEX($A$2:$A$14,MATCH(SMALL(--SUBSTITUTE(IF(ISNUMBER(FIND("/",$A$2:$A$14)),
$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT("0",10-LEN(MID($A$2:$A$14,
FIND("/",$A$2:$A$14&"/")+1,255)))),ROWS(C$2:C2)),--SUBSTITUTE(IF(
ISNUMBER(FIND("/",$A$2:$A$14)),$A$2:$A$14,$A$2:$A$14&"/"),"/",REPT(
"0",10-LEN(MID($A$2:$A$14,FIND("/",$A$2:$A$14&"/")+1,255)))),0))

ซึ่งจะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter เนื่องจากเป็นสูตร Array จากนั้น Copy สูตรลงด้านล่าง สามารถดาวน์โหลดไฟล์แนบตามด้านล่างไปศึกษาได้ตามสะดวกครับ

สูตรหาลำดับข้อความภายในอีกข้อความ

การหาลำดับข้อความใด ๆ จากสายอักขระข้อความ (ข้อความที่ติดกันในเซลล์เดียว) เป็นเรื่องไม่ยากนักหากว่าข้อความที่จะนำไปค้นหานั้นเป็นแค่ข้อความเดี่ยว แต่หากนำสายอักขระข้อความไปหาจากอีกสายอักขระข้อความย่อมไม่ง่ายนัก หากว่าไม่แบ่งข้อความนั้นออกมาเป็นข้อความละเซลล์เสียก่อน

สำหรับการแยกข้อความออกมาเป็นข้อความละเซลล์เพื่อง่ายต่อการจัดการหากว่าเราทราบแน่ชัดว่าข้อความนั้น ๆ แบ่งด้วยอักขระใด เราสามารถแบ่งด้วย Text to columns ได้อย่างง่าย ๆ และใช้ฟังก์ชั่นที่ไม่ซับซ้อนนักในการหาลำดับที่ของแต่ละข้อความออกมาได้

จากภาพด้านล่าง เซลล์ A2 เป็นตัวอย่างข้อความต้นทางหลายข้อความคั่นแต่ละข้อความด้วยเครื่องหมายคอมม่า ส่วนเซลล์ C2 เป็นข้อความเพียงบางส่วนที่เราจะนำไปหาจากข้อความใน A2 ว่าแต่ละข้อความใน C2 อยู่ในลำดับที่เท่าไรของ A2 โดยให้แสดงลำดับที่พบเรียงลงไปด้านล่าง เริ่มลำดับแรกที่เซลล์ D2

เมื่อนำแต่ละข้อความในเซลล์ C2 ไปหาจากข้อความในเซลล์ A2 จะพบว่า

1. A อยู่ในลำดับที่ 1
2. B อยู่ในลำดับที่ 2
3. AB อยู่ในลำดับที่ 6
4. K อยู่่ในลำดับที่ 9
5. CD อยู่ในลำดับที่ 3

ภาพแสดงการนำแต่ละข้อความในเซลล์ C2 ไปหาว่าอยู่ลำดับที่เท่าไรจากข้อความใน A2

[caption id="attachment_2767" align="alignnone" width="600"] String order in other string[/caption]

เราสามารถเขียนสูตรที่ D2 เพื่อหาคำตอบดังนี้

=IFERROR(INDEX(MATCH(MID($C$2,SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2)))),SMALL(IF(MID($C$2&",",ROW(
INDIRECT("1:"&LEN($C$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($C$2)+1))),
ROW(INDIRECT("1:"&LEN($C$2))))-SMALL(IF(MID(","&$C$2&",",ROW(
INDIRECT("1:"&LEN($C$2))),1)=",",ROW(INDIRECT("1:"&LEN($C$2)))),
ROW(INDIRECT("1:"&LEN($C$2))))),MID($A$2,SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2)))),SMALL(IF(MID($A$2&",",ROW(
INDIRECT("1:"&LEN($A$2)+1)),1)=",",ROW(INDIRECT("1:"&LEN($A$2)+1))),
ROW(INDIRECT("1:"&LEN($A$2))))-SMALL(IF(MID(","&$A$2&",",
ROW(INDIRECT("1:"&LEN($A$2))),1)=",",ROW(INDIRECT("1:"&LEN($A$2)))),
ROW(INDIRECT("1:"&LEN($A$2))))),0),ROWS(D$2:D2)),"")

Ctrl+Shift+Enter > Copy ลงด้านล่าง

สูตรนี้เป็นสูตร Array ที่มีความซับซ้อนสูง ซ้อนกันหลายชั้นเพื่อที่จะตัดแต่ละข้อความออกมาเสียก่อน แล้วค่อยหาลำดับที่พบข้อความใน C2 จากข้อความใน A2 ผู้ที่จะศึกษาสูตรลักษณะนี้จะต้องเข้าใจแต่ละฟังก์ชั่นเป็นอย่างดีมาก่อน สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับครับ

การเปรียบเทียบข้อมูลจาก 2 ชีทแล้วนำผลต่างไปแสดงในชีทที่ 3

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

สำหรับการสรุปผลต่างของข้อมูลแล้วแสดงรายการข้อมูลที่ต่างกันด้วยสูตรดูเหมือนจะเป็นเรื่องยากลำบากที่จะทำเช่นนี้ แต่ก็ไม่เกินความสามารถของ Excel โดยไม่ต้องพึ่งพา VBA แต่อย่างใด แต่จะมีหลายลำดับขั้นตอน ดังตัวอย่างตามภาพด้านล่างซึ่งเป็นข้อมูลของ Sheet1, Sheet2 และ Sheet3 ซึ่งเป็นชีทสรุปข้อมูล

ภาพข้อมูลใน Sheet1

ภาพข้อมูลใน Sheet2

ภาพการสรุปผลต่างของข้อมูลระหว่าง Sheet1 กับ Sheet2

จากภาพด้านบนจะเป็นการหาผลต่างระหว่าง 2 ชีทโดยยึดค่าในคอลัมน์ A เป็นหลัก เราสามารถเขียนสูตรที่ Sheet3 ได้ดังนี้ครับ

1. ที่เซลล์ G1:G2 คีย์ ไม่มีในชีท1, ไม่มีในชีท2 ตามลำดับ
2. ที่เซลล์ H1 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet2!A2:A117,sheet1!A2:A119,0)),1))
   Ctrl+Shift+Enter
3. ที่เซลล์ H2 คีย์สูตร
   =SUM(IF(ISNA(MATCH(sheet1!A2:A119,sheet2!A2:A117,0)),1))
   Ctrl+Shift+Enter
4. ที่เซลล์ H3 คีย์สูตร
   =SUM(H1:H2)
   Enter
5. ที่เซลล์ I1 คีย์สูตร
   =SUM(H$1:H1)-H1+1
   Enter > Copy ไปยัง I2
6. ที่เซลล์ E3 คีย์สูตร
   =IF(ROWS(E$3:E3)>$H$3,"",LOOKUP(ROWS(E$3:E3),$I$1:$I$2,$G$1:$G$2))
   Enter > Copy ลงด้านล่าง
7. ที่เซลล์ B3 คีย์สูตร
   =IF(ROWS(B$3:B3)>$H$3,"",CHOOSE(MATCH($E3,$G$1:$G$2,0),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet2!$A$2:$A$117,sheet1!$A$2:$A$119,0)),ROW(sheet2!$A$2:$A$117)-ROW(sheet2!$A$2)+1),COUNTIF($E$3:$E3,$E3))),INDEX(sheet1!B$2:B$119,SMALL(IF(ISNA(MATCH(sheet1!$A$2:$A$119,sheet2!$A$2:$A$117,0)),ROW(sheet1!$A$2:$A$119)-ROW(sheet1!$A$2)+1),COUNTIF($E$3:$E3,$E3)))))
   Ctrl+Shift+Enter > Copy ไปทางขวาและลงด้านล่าง

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

การสุ่มแบบแบ่งเป็นกลุ่มและแต่ละกลุ่มสุ่มมาไม่เท่ากัน

กรณีที่ต้องการสุ่มกลุ่มข้อมูล โดยแต่ละกลุ่มต้องการให้สุ่มมาไม่เท่ากันนั้น สามารถใช้สูตรในการจัดการได้เช่นกัน

ยกตัวอย่างเช่น ข้อมูลที่ A1:B19 แบ่งเป็น 3 กลุ่ม คือ A, B, C แต่ละสมาชิกกลุ่มมีมูลค่ากำกับตามภาพด้านล่าง ต้องการสุ่มโดย

1. สุ่ม A มา 1 ค่า
2. สุ่ม B มา 2 ค่า
3. สุ่ม C มา 3 ค่า

แล้วนำค่าที่ได้จากการสุ่มมารวมกัน

ภาพตัวอย่างข้อมูลที่ต้องการสุ่ม

เราสามารถใช้สูตรในการสุ่มได้ดังนี้ครับ

1. ที่ C2 คีย์สูตร
   =RAND()
   Enter > Copy ลงด้านล่าง
2. ที่ E2:E4 คีย์ A, B และ C ตามลำดับ
3. ที่ F2:F4 กรอกจำนวนที่ต้องการสุ่มแต่ละค่า
4. ที่ F5 คีย์สูตรเพื่อรวมจำนวนรายการที่ต้องสุ่ม
   =SUM(F2:F4)
   Enter
5. ที่ G2 คีย์สูตรเพื่อใช้หาบรรทัดที่เริ่มของแต่ละ Group
   =SUM(F$2:F2)-F2+1
   Enter > Copy ไปถึง G4
6. ที่ I2 คีย์สูตรเพื่อ List รายชื่อ Group
   =IF(ROWS(I$2:I2)>$F$5,"",LOOKUP(ROWS(I$2:I2),$G$2:$G$4,$E$2:$E$4))
   Enter > Copy ลงด้านล่าง
7. ที่ J2 คีย์สูตรเพื่อหา Value ที่ได้จากการสุ่ม
   =IF(I2="","",INDEX($B$2:$B$16,MATCH(SMALL(IF($A$2:$A$16=I2,$C$2:$C$16),COUNTIF(I$2:I2,I2)),IF($A$2:$A$16=I2,$C$2:$C$16),0)))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
8. กดแป้น F9 เพื่อสุ่มค่าตามต้องการ

 Note: Ctrl+Shift+Enter หมายถึงเมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl+Shift ค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้คีย์เข้าไปเองไม่ได้ ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง

ภาพแสดงผลลัพธ์ที่ต้องการในช่อง I:J

การค้นหาบางอักขระในข้อความ

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

ยกตัวอย่างเช่น มีรายชื่อตัวยาอยู่ในช่วงเซลล์ A2:A10 ต้องการคีย์ค้นหาในเซลล์ C2 แค่บางอักขระแล้วให้แสดงรายชื่อตัวยาออกมาในเซลล์ D2 เป็นต้นไป

เราสามารถเขียนสุตรได้ตามด้านล่างครับ

1. ที่ C2 คีย์คำค้นตามต้องการ
2. คีย์สูตรที่ D2 ตามด้านล่าง
   =IFERROR(INDEX($A$2:$A$10,SMALL(IF(MMULT(ISNUMBER(SEARCH(MID($C$2,TRANSPOSE(ROW(INDIRECT("1:"&LEN($C$2)))),1),$A$2:$A$10))+0,LEN(MID($C$2,ROW(INDIRECT("1:"&LEN($C$2))),1)))=LEN($C$2),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(D$2:D2))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

ภาพตัวอย่างการค้นหาบางอักขระในข้อความ

การจัดเรียงข้อมูลจากน้อยไปหามากหรือตรงกันข้ามด้วยสูตร

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

ตามด้านล่างจะเป็นภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตรเปรียบเทียบกับการเรียงข้อมูลด้วยเมนู Data > Sort

ภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตร

โดยมีวิธีการดังนี้

1. ข้อมูลอยู่ที่เซลล์ A1:A12
   
2. ที่ B1 คีย์สูตรเพื่อนับว่ามีเซลล์ที่มีข้อมูลจำนวนเท่าไร
   
   =Counta(A:A)
   
   Enter
   
3. ที่เซลล์ C1 คีย์สูตรเพื่อเรียงข้อมูลจากน้อยไปหามาก
   
   =IF(ROWS(C$1:C1)>$B$1,"",INDEX($A$1:$A$12,MATCH(SMALL(IF(ISERR(CODE($A$1:$A$12)),"",CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12)),ROWS(C$2:C2)),CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12),0)))
   
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

Note:

กรณีต้องการเรียงข้อมูลจากมากไปหาน้อยให้เปลี่ยน Small เป็น Large

การใช้สูตรสุ่มข้อมูลเป็นชุด ๆ โดยกำหนดได้ว่าให้ไม่ซ้ำกันภายในชุด

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

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

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

 

จากภาพด้านบนเราสมมุติว่าข้อมูลมีชุดละ 2 บรรทัด และโดยชุดแรกคือ C2:C3, ชุดที่สองคือ C4:C5 เป็นต้น และจะทำการสุ่มโดยนำข้อมูลสีที่ E3:E4 เข้าแสดงแต่ต้องไม่ซ้ำสีกันภายในชุดเดียวกัน

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

สำหรับเลข 2 ด้านบน สามารถใช้ฟังก์ชั่นนับค่าที่ต้องการสุ่มไว้ในเซลล์ใด ๆ แล้วค่อยอ้างอิงไปใช้ในสูตร จะสะดวกกว่ามากหากมีการแก้ไขในภายหลัง

ช่วงเซลล์ที่ต้องการสุ่มในฟังก์ชั่น Index ก็เช่นกัน สามารถทำให้ Dynamic โดยการให้ชื่อกับช่วงเซลล์แบบ Dynamic ได้

ตามรูป ที่ C2 คีย์

=Index($E$3:$E$4,Small(If(Countif(If(Mod(Rows($C$2:C2)-1,2)=0,$C$1:$C$1,Offset(C1,0,0,-Mod(Rows($C$2:C2)-1,2),1):C1),$E$3:$E$4)=0,Row($E$3:$E$4)-Row($E$3)+1),Int((2-Mod(Rows($C$2:C2)-1,2))*Rand())+1))

การกดแป้นให้รับสูตรต้องกด 3 แป้นคือ Ctrl+Shift+Enter ถ้ากดแป้นถูกต้องจะเห็นปีกกาคร่อมสูตร จากนั้น Copy ลงด้านล่าง

การสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ

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

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

จากภาพด้านล่างจะเป็นการสรุปข้อมูลตามที่ต้องการโดยการเลือก Region และ Product โปรแกรมจะแสดงยอดรวม ทั้งแจกแจงรายการทั้งหมดที่ตรงตาม Region และ Product

ภาพตัวอย่างการสรุปข้อมูลจากฐานข้อมูลที่แบ่งเป็นช่วง ๆ



โดยมีวิธีการและขั้นตอนดังนี้

A. List รายการของ Product ทั้งหมด

1. นับ Product ที่มีทั้งหมดโดยไม่นับค่าที่ซ้ำ H1 คีย์
   =SUM(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($B$1:$B$39)-ROW($B$1)),1))
   Ctrl+Shift+Enter
2. List รายการ Product ทั้งหมดโดยไม่เอาค่าซ้ำที่ H2 คีย์
   =IF(ROWS($H$2:H2)<=$H$1,INDEX($A$1:$A$39,SMALL(IF(FREQUENCY(IF(ISNUMBER($B$1:$B$39),MATCH("~"&$A$1:$A$39,$A$1:$A$39&"",0)),ROW($A$1:$A$39)-ROW($A$1)+1),ROW($A$1:$A$39)-ROW($A$1)+1),ROWS($H$2:H2))),"")
   Ctrl+Shift+Ener > Copy ลงด้านล่าง

B. List รายการ Region ทั้งหมด

1. นับจำนวน Region โดยไม่เอาค่าซ้ำที่ G1 คีย์
   =SUMPRODUCT(--(B1:B39=""),--(A1:A39<>""))
   Enter
2. List Region ทั้งหมด G2 คีย์
   =IF(ROWS($G$2:G2)<=$G$1,INDEX($A$1:$A$39,SMALL(IF($B$1:$B$39="",IF($A$1:$A$39<>"",ROW($B$1:$B$39)-ROW($B$1)+1)),ROWS($G$2:G2))),"")
   Ctrl+Shift+Ener > Copy ลงด้านล่าง

C. หาตำแหน่งเซลล์ว่างสุดท้ายในบรรทัดด้านล่างถัดจาก Region ที่เลือกใน E2 ที่ E1 คีย์

=SMALL(IF(A1:A39="",ROW(A1:A39)-ROW(A1)+1),MATCH(E2,G2:G7,0))
Ctrl+Shift+Ener

D. List รายการที่ตรงตามเงื่อนไขทั้ง Region และ Prod ที่เลือกใน E2 และ E3 ตามลำดับ

1. นับว่าตรงตามเงื่อนไขทั้งหมดมีจำนวนเท่าไรที่ D6 คีย์
   =COUNTIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3)
   Enter
2. List Product ทั้งหมดที่ตรงตามเงื่อนไข ที่ D8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
3. List Value ทั้งหมดที่ตรงตามเงื่อนไข ที่ E8 คีย์
   =IF(ROWS($D$8:D8)<=$D$6,INDEX(OFFSET($B$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)),SMALL(IF(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0))=$E$3,ROW(OFFSET($A$2,MATCH($E$2,$A$1:$A$39,0),0,$E$1-MATCH($E$2,$A$1:$A$39,0)))-MATCH($E$2,$A$1:$A$39,0)-1),ROWS($D$8:D8))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

E. สูตรหายอดรวมรายการที่ตรงตามเงื่อนไขโดยไม่ต้องแจกแจงรายการออกมาก่อน ที่ E4 คีย์

=SUMIF(OFFSET(A2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)),E3,OFFSET(B2,MATCH(E2,$A$1:$A$39,0),0,E1-MATCH(E2,$A$1:$A$39,0)))
Enter