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

ปกติแล้วการแยกค่าในเซลล์เดียวเป็นหลายเซลล์สามารถใช้ 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 การแยกข้อมูลเป็นรายชีตพร้อมกันในคราวเดียว

การค้นหาข้อมูลจากหลายไฟล์หลายชีตหลายคอลัมน์ด้วย Vlookup

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

ยกตัวอย่างข้อมูลของเราเป็นข้อมูล Product อยู่ใน 3 ไฟล์ แต่ละไฟล์จะมีข้อมูลอยู่ที่ Sheet2 - Sheet4 ประกอบด้วย Product Name, Quantity และ Amount โดยต้องการจะ Lookup ค่า Product ในเซลล์ A4 ของ Sheet1 ของไฟล์หลัก จาก Sheet2 - Sheet4 ของแต่ละไฟล์ โดยให้สามารถค้นหาได้ทุกคอลัมน์ หากค้นพบให้นำข้อมูล Quantity และ Amount มาวางที่ เซลล์ B4, C4 ของ Sheet1 ของไฟล์หลักตามลำดับ ซึ่งได้มีการกำหนดชื่อไฟล์และชื่อชีตที่จะดึงข้อมูลเอาไว้ล่วงหน้าที่เซลล์ H1:I9 ของ Sheet1 ของไฟล์หลัก

ภาพการใช้ Vlookup ดึงข้อมูลที่ไม่เป็นระเบียบจากหลายไฟล์ หลายชีต หลายคอลัมน์มาแสดง

ไฟล์หลัก

ไฟล์ที่ 1

ไฟล์ที่ 2

เราสามารถเขียนสูตรที่เซลล์ B4 ของ Sheet1 ของไฟล์หลัก เพื่อค้นหาค่า Quantity ได้ดังนี้

=VLOOKUP($A4,OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9&"!A2",
MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9&"!A2:A100"),
0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-ROW($I$1)+1)))),
0,MATCH(TRUE,COUNTIF(OFFSET(INDIRECT(INDEX($H$1:$H$9&$I$1:$I$9
&"!A2:A100",MIN(IF(COUNTIF(OFFSET(INDIRECT($H$1:$H$9&$I$1:$I$9
&"!A2:A100"),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4),ROW($I$1:$I$9)-
ROW($I$1)+1)))),0,COLUMN($A$1:$O$1)-COLUMN($A$1)),$A4)>0,0)-1,100,3),
COLUMNS($A3:B3),0)

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

VlookupMultipleFilesSheetsCols

การค้นหาข้อมูลจากหลายชีตหลายคอลัมน์ที่ไม่เป็นระเบียบด้วย Vlookup

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

ยกตัวอย่างข้อมูลของเราเป็นข้อมูล Product อยู่ใน Sheet2 - Sheet4 ประกอบด้วย Product Name, Quantity และ Amount โดยต้องการจะ Lookup ค่า Product ในเซลล์ A4 ของ Sheet1 จาก Sheet2 - Sheet4 โดยให้สามารถค้นหาได้ทุกคอลัมน์ หากค้นพบให้นำข้อมูล Quantity และ Amount มาวางที่ เซลล์ B4, C4 ของ Sheet1 ตามลำดับ ซึ่งได้มีการกำหนดชื่อชีตที่จะดึงข้อมูลเอาไว้ล่วงหน้าที่เซลล์ I1:I3 ของ Sheet1

ภาพการใช้ Vlookup ดึงข้อมูลที่ไม่เป็นระเบียบจากหลายชีต หลายคอลัมน์มาแสดง

ชีตที่ใช้ Vlookup ดึงข้อมูล

ข้อมูลใน Sheet2

ข้อมูลใน Sheet3

ข้อมูลใน Sheet4

เราสามารถเขียนสูตรที่เซลล์ B4 ของ Sheet1 เพื่อค้นหาค่า Quantity ได้ดังนี้

=VLOOKUP($A4,OFFSET(INDIRECT(INDEX($I$1:$I$3&"!A2",MIN(IF(
COUNTIF(OFFSET(INDIRECT($I$1:$I$3&"!A2:A100"),0,
COLUMN($A$1:$O$1)-COLUMN($A$3)),$A4),ROW($I$1:$I$3)-ROW($I$1)+1)))),
0,MATCH(TRUE,COUNTIF(OFFSET(INDIRECT(
INDEX($I$1:$I$3&"!A2:A100",MIN(IF(COUNTIF(OFFSET(INDIRECT(
$I$1:$I$3&"!A2:A100"),0,COLUMN($A$1:$O$1)-COLUMN($A$3)),$A4),
ROW($I$1:$I$3)-ROW($I$1)+1)))),0,COLUMN($A$1:$O$1)-COLUMN($A$3)),
$A4)>0,0)-1,100,3),COLUMNS($A3:B3),0)

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

VlookupMultiSheetsMultiColumns

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

การจัดเรียงบ้านเลขที่นั้นไม่สามารถทำได้อย่างง่าย ๆ เนื่องจากบ้านเลขที่มักจะมีเครื่องหมายทับ (/) ปนอยู่ด้วย ทำให้เครื่องมือของ 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 ผู้ที่จะศึกษาสูตรลักษณะนี้จะต้องเข้าใจแต่ละฟังก์ชั่นเป็นอย่างดีมาก่อน สามารถดาวน์โหลดไฟล์แนบด้านล่างไปศึกษาได้ตามสะดวกครับครับ

การแทรกบรรทัดว่างระหว่างข้อมูล

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

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

1. เพิ่มคอลัมน์ช่วยและให้เลขลำดับกับข้อมูลเดิมโดย
   1. ที่เซลล์ E1 คีย์คำว่า “No” หรือคำใด ๆ ตามต้องการเพื่อใช้สำหรับการจัดเรียง
   2. ที่เซลล์ E2:E5 คีย์เลข 1-4
   3. คัดลอก E2:E5 แล้ววางต่อเนื่องลงไปด้านล่างจำนวน 3 รอบ
      
2. ทำการจัดเรียงข้อมูลใหม่
   1. คลิกเซลล์ใด ๆ ที่มีข้อมูล
   2. เข้าเมนู Data
   3. เลือก Sort
   4. ตรง Sort by เลือก “No”
   5. คลิกปุ่ม OK
      

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

Revised: January 29, 2017 at 07:06

การตัดเอาตัวอักษรตัวแรกของข้อความมาแสดง

กรณีมีข้อความอยู่ในเซลล์ใด ๆ แล้วต้องการตัดตัวอักษรโดยมีเงื่อนไขตามด้านล่างคือ

1. ถ้าตัวอักษรแรกเป็น พยัญชนะ ให้ตัดตัวอักษรที่เป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา
2. ถ้าตัวอักษรแรกเป็น สระ ให้ตัดตัวอักษรที่ถัดจากสระซึ่งเป็นพยัญชนะตัวแรกนับจากซ้ายมือออกมา

ยกตัวอย่างเช่น

ถ้า A1 คือ กาญจนา ผลลัพท์ที่ต้องการ คือ ก
ถ้า A1 คือ เอกวัฒน์ ผลลัพท์ที่ต้องการ คือ อ
ถ้า A1 คือ รุ้งทอง ผลลัพท์ที่ต้องการ คือ ร

เราสามารถเขียนสูตรที่ B1 เพื่อหาอักขระตัวแรกของค่าใน A1 ได้ตามด้านล่างครับ

=MID(A1,MIN(SEARCH(CHAR(ROW($161:$206)),A1&CHAR(ROW($161:$206)))),1)

Ctrl+Shift+Enter

Excel Forum • แสดงกระทู้ - การตัดตัวอักษร แบบมีเงื่อนไข

การเปรียบเทียบข้อมูลจาก 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 ไปทางขวาและลงด้านล่าง

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

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

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

ยกตัวอย่างเช่น มีรายชื่อตัวยาอยู่ในช่วงเซลล์ 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 ลงด้านล่าง

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

การหา Unique Value จากค่าที่ได้จากการกรอง

สำหรับการหาค่า Unique Value เราสามารถหาได้จากหลาย ๆ วิธีตาม Link นี้ครับ การหารายการเฉพาะที่ไม่ซ้ำ แต่หากเป็นการหาค่าที่ไม่ซ้ำจากรายการที่ได้จากการกรองต้องพึ่งสูตรที่มีความซับซ้อนมากขึ้น

ยกตัวอย่างมีข้อมูลอยู่ที่ A2:B10 หลังจากกรองแล้วให้นับเฉพาะค่าที่ไม่ซ้ำในช่วง A2:A10

เราสามารถคีย์สูตรใน D1 ได้ตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),A2:A10),IF(SUBTOTAL(3,OFFSET( B2,ROW( B2:B10)-ROW(B2),0)),A2:A10),0)=ROW(A2:A10)-ROW(A2)+1,SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))))

Ctrl+Shift+Enter

ภาพตัวอย่างภาพการใช้สูตรหาค่า Unique จากค่าที่ได้จากการกรอง

การนำรายการแรกที่ได้จากการ Filter มาแสดง

การนำบรรทัดแรกของข้อมูลที่ได้จากการ Filter มาแสดงไม่สามารถใช้สูตร Link มาแบบธรรมดาได้ ทั้งนี้เนื่องจากบรรทัดของข้อมูลที่ได้จากการ Filter นั้นจะไม่คงที่ ขึ้นอยู่กับว่ารายการที่ได้จากการ Filter นั้นอยู่ในบรรทัดใดบ้าง การนำข้อมูลที่พบตัวแรกของรายการที่ได้จากการ Filter จึงต้องใช้สูตรที่ซับซ้อนกว่าปกติ

ยกตัวอย่างเช่นข้อมูล Product ตามภาพด้านล่าง ต้องการให้ Filter ราย Product แล้วนำ Prodcut บรรทัดแรกที่ได้จากการ Filter มาแสดง

ภาพข้อมูล Product

สามารถทำได้ดังนี้

ที่เซลล์ A2 คีย์สูตร

=INDEX(A$4:A$20,MATCH(1,SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)),0))

Ctrl+Shift+Enter

โดยสูตร SUBTOTAL(3,OFFSET($A$4,ROW($A$4:$A$20)-ROW($A$4),)) เป็นสูตร Array ซึ่งจะแสดงผลลัพธ์เป็นชุดข้อมูล โดยจะแสดงผลลัพธ์เป็น 0 หากบรรทัดนั้นถูกซ่อนจากการ Filter และจะแสดง 1 หากบรรทัดนั้นได้จากการ Filter ซึ่งตัวอย่างสูตรจะแสดงตามด้านล่าง

=INDEX(A$4:A$20,MATCH(1,{0,0,1,0,0,0,0,0,1,1,0,0,1,0,0,1,0},0))

จากตัวอย่างด้านบนจะนำ A6 มาแสดง เนื่องจากเลข 1 ตัวแรกตรงกับ A6 ( เลข 0 ตัวแรกคือ A4) ซึ่งการจะเข้าใจสูตรนี้ต้องเข้าใจสูตร Index และ Match ก่อนครับ

ตัวอย่างการแสดง Prouduct จากการ Filter

การแสดงรายการซ้ำตามจำนวนที่กำหนด

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

กรณีแบบยากเราจะใช้สูตร Array ในการแสดงรายการซ้ำ ๆ ตามจำนวนที่กำหนด ยกตัวอย่างเช่นตามภาพด้านล่าง ข้อมูลอยู่ที่ A2:A7 จำนวนที่ระบุว่าซ้ำกันกี่ครั้งอยู่ที่ B2:B7 โดยจะนำรายการมาแสดงตั้งแต่ F2 เป็นต้นไป

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบยาก

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ B9 หาค่าสูงสุดของช่วง B2:B7
   =MAX(B2:B7)
   Enter
3. ที่เซลล์ F2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($F$2:F2)>$B$8,"",INDEX($A$2:$A$7,SMALL(IF(TRANSPOSE(ROW(INDIRECT("1:"&$B$9)))<=$B$2:$B$7,ROW($B$2:$B$7)-ROW($B$2)+1),ROWS($F$2:F2))))
   Ctrl+Shift+Enter > Copy ลงด้านล่างตามต้องการ สังเกตการกดแป้นจะต้องกด Ctrl+Shift ค้างไว้ก่อนแล้วตามด้วย Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาคร่อมสูตร ปีกกานี้จะคีย์เข้าไปเองไม่ได้ครับ การแก้ไขปรับปรุงสูตรจะต้องกด Ctrl+Shift+Enter ทุกครั้ง

กรณีแบบง่ายจะใช้สูตร Lookup และเพิ่ม C2:C7 มาช่วย โดยจะนำข้อมูลมาแสดงตั้งแต่ G2 เป็นต้นไปตามภาพด้านล่างครับ

ภาพแสดงการแสดงรายการซ้ำตามจำนวนที่กำหนดแบบง่าย

วิธีการ

1. ที่เซลล์ B8 คีย์สูตรเพื่อหายอดรวมตัวเลขทั้งหมด เพื่อประโยชน์ในการกำหนดจำนวนบรรทัดสูงสุดที่จะแสดงรายการ
   =SUM(B2:B7)
   Enter
2. ที่เซลล์ C2 คีย์สูตรเพื่อประโยชน์ในการ Lookup ข้อมูล
   =SUM(B$2:B2)-B2+1
   Enter > Copy ลงด้านล่าง
3. ที่เซลล์ G2 คีย์สูตรเพื่อแสดงรายการ
   =IF(ROWS($G$2:G2)>$B$8,"",LOOKUP(ROWS($G$2:G2),$C$2:$C$7,$A$2:$A$7))
   Enter > Copy ลงด้านล่างตามต้องการ

การให้ลำดับข้อมูลคะแนนโดยค่าเดียวกันได้ลำดับเดียวกันและไม่ข้ามลำดับ

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

	A	B	C
1	คะแนน	Rank	ที่ต้องการ
2	300	1	1
3	200	2	2
4	200	2	2
5	100	4	3

จากตารางจะเห็นว่าด้วยฟังก์ชั่น Rank จะมีการให้ลำดับข้ามไป คือไม่มีลำดับที่ 3 หากต้องการผลลัพธ์ตามคอลัมน์ B เราสามารถคีย์สูตรที่ B2 เป็น

=Rank($A2,$A$2:$A$5)

Enter > Copy ลงด้านล่าง

แต่หากต้องการผลลัพธ์ที่มีการให้ลำดับต่อเนื่องกันไปเช่นเดียวกับผลลัพธ์ในคอลัมน์ C ที่ C2 คีย์

=MATCH($A2,LARGE(IF(FREQUENCY(MATCH($A$2:$A$5,$A$2:$A$5,0),ROW($A$2:$A$5)-ROW($A$2)+1),$A$2:$A$5),ROW(INDIRECT("1:"&SUM(1/COUNTIF($A$2:$A$5,$A$2:$A$5))))),0)

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

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

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

การคำนวณโดยระบุกลุ่มข้อมูลจากข้อมูลทั้งหมด

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

ซึ่งกรณีที่จะยกตัวอย่างต่อไปนี้จะเป็นการคำนวณหาค่าค่าเฉลี่ย ค่ามากที่สุด ค่าน้อยที่สุด เฉพาะ 5 ค่าสุดท้ายของช่วงข้อมูลซึ่งไม่ติดกัน ทั้งดึงข้อมูลที่เกี่ยวข้องมาแสดง โดยข้อมูลอยู่ที่คอลัมน์ A:F และจะนำผลลัพธ์ซึ่งเป็นค่า Average, Min, Max และวันที่ที่เกี่ยวข้องของค่าต่าง ๆ มาแสดงที่คอลัมน์ I:M

ภาพตัวอย่างการคำนวณโดยระบุกลุ่มข้อมูลทั้งหมด

โดยมีลำดับการคำนวณดังข้างล่างนี้

1. เซลล์ H1 คีย์ ตัวเลขซึ่งเป็น n ลำดับสุดท้าย เช่นตามโจทย์คือ 5 อาจจะคีย์เป็น 7, 10 อันนี้แล้วแต่ความต้องการ
2. เซลล์ I2 คีย์สูตร เพื่อหาค่า Average สำหรับ Type A
   =AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
3. เซลล์ I3 คีย์สูตร เพื่อหาค่า Min สำหรับ Type A
   =MIN(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
4. เซลล์ I4 คีย์สูตร เพื่อหาวันที่ที่แสดงค่า Min สำหรับ Type A
   =INDEX(INDEX($A2:$A21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX($A2:$A21,MATCH(9.99999999999999E+307,B2:B21)),MATCH(I3,INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)),0))
   Ctrl+Shift+Enter
5. เซลล์ I5 คีย์สูตร เพื่อหาค่า Max สำหรับ Type A
   =MAX(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))
   Ctrl+Shift+Enter
6. เซลล์ I6 คีย์สูตร เพื่อหาวันที่ที่แสดงค่า Max สำหรับ Type A
   =INDEX(INDEX($A2:$A21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX($A2:$A21,MATCH(9.99999999999999E+307,B2:B21)),MATCH(I5,INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)),0))
   Ctrl+Shift+Enter
7. คลุม I2:I6 แล้ว Copy ไปทางด้านหลัง

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

ตัวอย่างการแกะสูตร

จากสูตร

=AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))

จากช่วงของสูตร

INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1))

เป็นการหาค่าแรกของข้อมูลที่ต้องการ

INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21))

เป็นการหาเซลสุดท้ายของช่วงข้อมูล(ที่เป็นตัวเลข)

มาดูไส้ของสูตรแรกกันใน ช่วงที่เป็น

LARGE(IF(ISNUMBER(B2:B21),ROW(B2:B21)-ROW(B2)+1),$H$1)

ความหมายคือ ถ้า B2:B21 เป็นตัวเลข ให้คืนค่าแถวลำดับ และนำลำดับที่ H1 (ตามแต่กำหนด) มาแสดง

เมื่อคลุมช่วง ISNUMBER(B2:B21) แล้วกดแป้น F9 จะได้

LARGE(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},ROW(B2:B21)-ROW(B2)+1),$H$1)

เมื่อ ลากคลุมช่วง ROW(B2:B21)-ROW(B2)+1 กับลากคลุม H1 แล้วกด F9 จะได้

LARGE(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}),5)

ในขั้นตอนนี้จะได้สูตร IF ในมุมมองที่ชัดเจนขึ้น ตัวหน้าที่เป็น False จะไม่นำค่าตัวเลขในปีกกาด้านหลังมาแสดง ส่วนตัวที่เป็น True จะนำตัวเลขในปีกกาด้านหลังมาแสดง

เมื่อลากคลุม IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20})

แล้ว กดแป้น F9 จะได้

LARGE({FALSE;2;3;4;FALSE;6;7;FALSE;FALSE;FALSE;FALSE;FALSE;13;14;FALSE;FALSE;17;18;FALSE;20},5)

หากลากคลุมสูตรนี้ต่อก็จะได้ 13 (ซึ่งจะเห็นได้ว่าตัวที่ใหญ่ที่สุดเป็นอันดับ 5 ในสูตรนี้คือ 13)

เมื่อมองภาพรวมสูตรทั้งหมดอีกครั้งจะได้เป็น

=AVERAGE(INDEX(B2:B21,IF(COUNT(B2:B21)<$H$1,1,13)):INDEX(B2:B21,MATCH(9.99999999999999E+307,B2:B21)))

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

สำหรับ Excel 2003 จะไม่สามารถ ใช้คำสั่ง Evaluate Formula ตั้งแต่ต้นในการพิสูจน์สูตรที่ให้นี้ได้ (Excel จะ ล่ม จะเห็นได้จากภาพที่ Title bar มีคำว่า Recovered) ผมถือว่ามันเป็น Bug จึงจำเป็นต้องใช้ 2 วิธีในการแกะสูตรครับ

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

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

เป็นที่น่าดีใจว่า 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