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

แม้จะเป็นระดับการประยุกต์การใช้งานที่ซับซ้อนก็ตาม เราก็ต้องอาศัยฟังก์ชั่นเหล่านี้เป็นหลัก ซึ่งได้แก่
✏ Min หาค่าที่น้อยที่สุด เช่น Min(A1:C10)
✏ Max หาค่าที่มากที่สุด เช่น Max(D8:E20)
✏ Average หาค่าเฉลี่ย เช่น Average(B5:F50)
✏ Count นับว่ามีตัวเลขกี่ตัว เช่น Count(I5:K8)
✏ Counta นับว่าไม่เป็นเซลล์ว่างกี่เซลล์ เช่น Counta(H8:V3)
✏ Countifs นับตามเงื่อนไข เช่น Countifs(A2:A5,"OK",B2:B5,1) เป็นการนับว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Sumifs รวมตามเงื่อนไข เช่น Sumifs(C2:C5,A2:A5,"OK",B2:B5,1) เป็นการรวมค่าใน C2:C5 โดยมีเงื่อนไขว่า A2:A5 เป็นคำว่า "OK" และ B2:B5 เป็นเลข 1 นั้น มีเท่าไร
✏ Left เป็นการตัดอักขระด้านซ้าย เช่น Left(A2,8) เป็นการตัดอักขระด้านซ้ายของเซลล์ A2 มา 8 อักขระ
✏ Right เป็นการตัดอักขระด้านขวา เช่น Right(B2,4) เป็นการตัดอักขระด้านขวาของเซลล์ B2 มา 4 อักขระ
✏ Mid เป็นการตัดอักขระโดยระบุตำแหน่งเริ่มและจำนวนอักขระที่ตัด เช่น Mid(D2,3,4) เป็นการตัดอักขระในเซลล์ D2 โดยเริ่มอักขระที่ 3 มาจำนวน 4 อักขระ
✏ Len เป็นการนับจำนวนอักขระ เช่น Len(C8) เป็นการนับว่าในเซลล์ C8 มีกี่อักขระ
✏ If เป็นการพิจารณาตามเงื่อนไขว่าจริงหรือเท็จ เช่น If(A2=5,"Yes","No") เป็นการพิจารณาว่า A2 เท่ากับ 5 จริงหรือไม่ หากเป็นจริงให้แสดงคำตอบเป็น Yes หากเป็นเท็จให้แสดงคำตอบเป็น No
✏ Vlookup เป็นการค้นหาค่าใด ๆ ในคอลัมน์แรกของตารางแล้วแสดงผลลัพธ์เป็นค่าในคอลัมน์ใด ๆ ของตารางตามที่กำหนด เช่น Vlookup("Finish",B3:F100,2,0) เป็นการค้นหาคำว่า Finish ใน B3:B100 (คอลัมน์แรกของตาราง) แล้วแสดงผลลัพธ์เป็นค่าใน C3:C100 (คอลัมน์ที่ 2 ของตาราง) หากไม่พบคำว่า Finish ใน B3:B100 จะให้ผลลัพธ์เป็น #N/A
✏ Sumproduct เป็นการหาผลรวมของผลคูณ เช่น Sumproduct(A2:A4,B2:B4) เป็นการหาผลรวมของผลคูณของ A2:A4 และ B2:B4 ลักษณะการทำงานคือ A2*B2+A3*B3+A4*B4
✏ Index เป็นการระบุพื้นที่แล้วแสดงผลลัพธ์ในตำแหน่งที่กำหนด เช่น Index(A3:C8,1,3) เป็นการนำบรรทัดที่ 1 คอลัมน์ที่ 3 ของพื้นที่ A3:C8 มาแสดง ค่าผลลัพธ์คือ C3
✏ Match เป็นการค้นหาค่าใด ๆ ในช่วงแล้วแสดงผลลัพธ์เป็นลำดับที่พบ เช่น Match(True,B5:B10,0) เป็นการหาว่าค่า True อยู่ในลำดับที่เท่าไรของช่วง B5:B10 หากไม่พบค่า True จะแสดงผลลัพธ์เป็น #N/A
✏ Indirect เป็นการอ้างอิงโดยอ้อม เช่น B2 มีค่าเท่ากับ 5, A10 มีค่าเท่ากับ B2 เซลล์ใด ๆ คีย์ Indirect("A10") คำตอบจะได้ 5 ลักษณะการทำงานของสูตรคือ หาก่อนว่าค่า A10 คืออะไร เมื่อพบว่า A10 คือ B2 ฟังก์ชั่นนี้จึงแสดงคำตอบที่เป็นค่าใน B2 สังเกตว่าค่าที่จะคีย์ลงไปใน Indirect ได้จะต้องเป็นการอ้างอิงไปยังปลายทางที่เป็นเซลล์หรือช่วงเซลล์เท่านั้น
✏ Offset เป็นการระบุตำแหน่งเซลล์โดยมีจุดเริ่ม ตำแหน่งบรรทัดที่ถัดจากจุดเริ่ม ตำแหน่งคอลัมน์ที่ถัดจากจุดเริ่ม ความสูงของข้อมูล และความกว้างของข้อมูล เช่น Offset(B4,0,0,1,5) เป็นการระบุช่วงข้อมูลโดยเริ่มที่ B4 ตำแหน่งบรรทัดที่ห่างจาก B4 เป็น 0 ตำแหน่งคอลัมน์ที่ห่างจาก B4 เป็น 0 แสดงว่ายังอยู่ที่จุดเริ่มเช่นเดิมคือ B4 ความสูงของข้อมูลเป็น 1 ความกว้างของข้อมูลเป็น 5 แสดงว่าจาก B4 ขยายไปทางขวา 5 คอลัมน์ ผลลัพธ์จะได้เป็นช่วงเซลล์ B4:F4

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

มีปัญหาการใช้งาน Microsoft Excel and VBA สอบถามได้ที่ snasui.com

Revised: December 23, 2017 at 12:04

การค้นหาข้อมูลที่สัมพันธ์กันแต่บรรทัดไม่ตรงกัน

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

ตัวอย่างตามด้านล่างจะเห็นว่า Line 3 ของแต่ละ Product ไม่ได้อยู่ในตำแหน่งเดียวกัน แต่เราสามารถที่จะ Lookup ค่ามาแสดงได้ตามตาราง E1:G5

ภาพตัวอย่างประกอบการหาข้อมูลที่สัมพันธ์กันแต่บรรทัดไม่ตรงกัน

การทำเช่นนี้ เราสามารถเขียนสูตรที่ G2 ตามด้านล่างครับ

=VLOOKUP(F2,OFFSET($B$2,MATCH($E2,$A$2:$A$13,0)-1,0,5,2),2,0)

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

เมื่อคีย์ข้อมูลแล้วให้ย้ายข้อมูลไปแสดงผลต่อจากข้อมูลด้านบน

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

ภาพประกอบการคีย์ข้อมูลแล้วให้ข้อมูลไปต่อจากข้อมูลด้านบน

การทำเช่นนี้สามารถใช้ Worksheet Change Envent โดยวาง Code VBA ด้านล่างที่ Sheet1 ในโปรแกรม VBE จากนั้นทดสอบคีย์ข้อมูลตามต้องการ

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    With Target
        If .Count > 1 Then
            Exit Sub
        ElseIf .Column = 2 And .Offset(1, 0) = "" _
            And .Offset(-1, 0) = "" Then
                .Offset(0, -1).Resize(, 2).Cut
                .End(xlUp).Offset(1, -1) _
                    .Resize(, 2).Insert shift:=xlDown
        End If
    End With
    Application.ScreenUpdating = True
End Sub

  

การรวมข้อมูลโดยอ้างอิงค่าที่เว้นเป็นช่วง ๆ

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

ภาพประกอบการรวมข้อมูลโดยอ้างอิงที่เว้นเป็นช่วง ๆ



การที่เราจะรวมข้อมูลในอลัมน์ D จึงต้องประยุกต์สูตรต่าง ๆ มาใช้ โดยคีย์สูตรที่ D5 ดังนี้

=IF(AND(LOOKUP(CHAR(255),$B$2:B5)=LOOKUP(CHAR(255),$B$2:B6),C6=""),SUM(OFFSET($C$1,MATCH(9.99999999999999E+307,C:C),0):OFFSET($C$1,MATCH(9.99999999999999E+307,$D$2:D4)+1,0)),IF(LOOKUP(CHAR(255),$B$2:B5)<>LOOKUP(CHAR(255),$B$2:B6),SUM(OFFSET($C$1,MATCH(CHAR(255),$B$2:B5),0):OFFSET($C$1,MATCH(CHAR(255),$B$2:B6)-1,0)),""))

โดยมีสูตรสำคัญ ๆ ดังนี้

1. LOOKUP(CHAR(255),$B$2:B5) เป็นการหาค่าอักขระสุดท้ายในช่วง B2:B5
2. OFFSET(…):OFFSET(…) เป็นการนำผลลัพธ์ของสูตร OFFSET มาเป็นช่วงข้อมูลใหม่
3. MATCH(CHAR(255),$B$2:B5),0) เป็นการหาตำแหน่งของอักขระสุดท้ายในช่วง B2:B5
4. MATCH(9.99999999999999E+307,C:C) เป็นการหาตำแหน่งสุดท้ายที่พบตัวเลขในช่วง C:C

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

โดยปกติแล้วฐานข้อมูลที่ควรจะเป็นนั้น ข้อมูลจะต้องเรียงติดกันจากบนลงล่าง ไม่มีบรรทัดว่าง ไม่มีค่าใด ๆ ที่ไม่เกี่ยวกับฐานข้อมูลอยู่ด้านข้างและด้านล่างของฐานข้อมูล เพื่อที่จะนำความสามารถที่มีเช่น 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

การคำนวณจากพื้นที่แบบสามเหลี่ยม

การคำนวณที่คิดว่ายาก ไม่น่าจะเป็นไปได้ อย่างเช่นกรณีที่คำนวณหาข้อมูลในพื้นที่สามเหลี่ยมนั้น ขอบอกว่า Excel สามารถทำได้สบายมากครับ แต่สูตรค่อนข้างจะซับซ้อน สูตรที่จะกล่าวถึงต่อไปนี้เป็นสูตรที่ผมใช้ตอบปัญหาเมื่อ เม.ย. 2550

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

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

ภาพประกอบ

 

A. สามเหลี่ยมบน

1. หาค่าผลรวม ที่ M2 คีย์
   =SUMPRODUCT(SUBTOTAL(9,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Enter
2. หาค่าเฉลี่ยที่ M3 คีย์
   =M2/SUMPRODUCT(SUBTOTAL(3,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Enter
3. หาค่า Max ที่ M4 คีย์
   =MAX(SUBTOTAL(4,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Ctrl+Shift+Enter
4. หาค่า Min ที่ M5 คีย์
   =MIN(SUBTOTAL(5,OFFSET(C2,,ROW(K10)-ROW(K2:K10),ROW(K10)-ROW(K2:K10)+1)))
   Ctrl+Shift+Enter

B. สามเหลี่ยมล่าง

1. หาค่าผลรวมที่ M11 คีย์
   =SUMPRODUCT(SUBTOTAL(9,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Enter
2. หาค่าเฉลี่ยที่ M12 คีย์
   =M11/SUMPRODUCT(SUBTOTAL(3,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Enter
3. หาค่า Max ที่ M13 คีย์
   =MAX(SUBTOTAL(4,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Ctrl+Shift+Enter
4. หาค่า Min ที่ M14 คีย์
   =MIN(SUBTOTAL(5,OFFSET(J12,,ROW(B4:B12)-ROW(B12),ROW(B4:B12)-ROW(B12)-1)))
   Crl+Shift+Enter

C. หาจุดตัดสามเหลี่ยมบนที่ M18 คีย์

1. =OFFSET(B2,MIN(MATCH(M17,C16:C25,0)-1,MATCH(N17,C16:C25,0)-1),MAX(MATCH(M17,C16:C25,0)-1,MATCH(N17,C16:C25,0)-1))
   Enter

D. หาจุดตัดสามเหลี่ยมล่างที่ M22 คีย์

1. =OFFSET(B3,MAX(MATCH(N21,B16:B25,0)-1,MATCH(M21,B16:B25,0)-1),MIN(MATCH(N21,B16:B25,0)-1,MATCH(M21,B16:B25,0)-1))
   Enter

ความเก่งกาจของฟังก์ชั่น Offset

ถ้าเราไปยืนที่ใดที่หนึ่งแล้วสามารถจะหันได้รอบทิศ ย่อมเป็นที่พอใจและน่ายินดีมากกว่าจะหันไปได้แค่ ซ้าย ขวาและมองไปข้างหน้าใช่ไหมครับ

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

ฟังก์ชั่น Offset เป็นฟังก์ชั่นสำหรับหาค่า เดี่ยว หรือ ชุดข้อมูล ได้ ขึ้นอยู่กับการกำหนดส่วนประกอบของฟังก์ชั่นครับ ซึ่งฟังก์ชั่น Offset มีส่วนประกอบ 5 ส่วนตามด้านล่าง

ไวยากรณ์

=Offset(Reference,Rows,Columns,Height,Width)

หรือ แปลตามแบบของผมเอง

=Offset(เซลล์อ้างอิง, จำนวนแถวที่ห่างจากเซลล์อ้างอิง ,จำนวนคอลัมน์ที่ห่างจากเซลล์อ้างอิง, ความสูงของข้อมูล, ความกว้างของข้อมูล)

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

=Offset(A1,0,0,1,1)

หมายความว่า

1. ให้หาค่าโดยดูจาก A1 เนื่องจากเซลล์อ้างอิง คือ A1
2. ห่างจาก A1 ไปด้านล่าง 0 แถว คือไม่ไปไหน ยังอยู่ที่ A1 เหมือนเดิ
3. ห่างจาก A1 ไปด้านขวา 0 คอลัมน์ คือไม่ไปไหน ยังอยู่ A1 เหมือนเดิม
4. ความสูงของข้อมูล 1 เซลล์ ก็คือความสูงของ A1
5. ความกว้างของข้อมูล 1 เซลล์ ก็คือความสูงของ A1 เช่นเดิม

สูตรด้านบน สามารถที่จะละส่วนประกอบ 2 ส่วนสุดท้ายไว้ก็ได้ เพราะมีความสูงและความกว้างของข้อมูลแค่ 1 บรรทัดก็จะได้เป็น

=Offset(A1,0,0)

หรือแบบของผู้ที่คิดว่าตัวเองเข้าใจดีแล้วก็จะเหลือ

=Offset(A1,,)

จะได้ค่าเดียวกัน คือ A1 นั่นเอง

ดูตัวอย่างตามภาพด้านล่าง 



ยกตัวอย่างใหม่ให้หลากหลายกว่าเดิม เพราะ Offset สามารถไปข้างหน้าและถอยหลังได้ การเริ่มที่เซลล์ A1 ไม่สามารถไปซ้ายและขึ้นบนได้ แต่มีประโยชน์แน่นอน จะกล่าวถึงในตอนท้ายถ้าไม่ลืม

ถ้าเช่นนั้น เริ่มที่ D5 ก็แล้วกัน จะได้เป็น

=Offset(D5,-1,-2,2,2)

โอ้โห...มีติดลบด้วย

ครับ ต้องไม่กระพริบตาเลยครับ

แปลสูตรได้ว่า

1. เริ่มจาก D5
2. ห่างจาก D5 จำนวน -1 แถว (อ้าว...แล้วไปไหน) ไป D4 ครับ ซึ่ง D4 จะกลายเป็นเซลล์อ้างอิงใหม่เพื่อใช้ในข้อ 3
3. ห่างจาก D4 จำนวน -2 คอลัมน์ (อ้าว...แล้วไปไหน) ไป B4 ครับ ซึ่ง B4 จะกลายเป็นเซลล์อ้างอิงใหมเพื่อใช้ในข้อ 4
4. จาก B4 ในข้อ 3 ความสูง 2 แถว ก็แสดงว่าสูงไปถึง B5 ก็จะกลายเป็น B4:B5
5. จากข้อ 4 (คือ B4:B5) กว้าง 2 คอลัมน์ ก็จะกลายเป็น B4:C5

สรุป Offset(D5,-1,-2,2,2) คือช่วงเซลล์ B4:C5
ในชีวิตจริงการใช้สูตรนี้อย่างเดียวจะเกิดค่าผิดพลาดเนื่องจากให้ผลลัพธ์เป็นช่วงข้อมูล ซึ่งเราไม่สามารถอ้างถึงช่วงข้อมูลขึ้นมาลอย ๆ ในเซลล์ใด ๆ ได้ เช่นถ้าคีย์ตรง ๆ ในเซลล์ใด ๆ เป็น =B4:C5 จะให้ผลลัพธ์เป็นค่าผิดพลาด ดังนั้น ปกติแล้วจะใช้สูตรอื่นมาครอบอีกทีครับ เช่น

=Sum(Offset(D5,-1,-2,2,2))

มายถึงการรวมยอดของช่วงเซลล์ B4:C5

ดูตัวอย่างตามภาพด้านล่าง 



ความสูงและความกว้างเป็นลบได้ไหม ได้แน่นอนครับ อย่างที่บอกว่าสามารถหันได้รอบทิศ

ถ้าเปลี่ยนสูตรด้านบนเป็น

=Sum(Offset(D5,-1,-2,-2,-2))

จะเป็นการ Sum ช่วงเซลล์ไหนครับ? (เฉลยอยู่ด้านล่าง) ให้ทดลองเล่นดูแล้วจะเข้าใจมากขึ้น อย่าเพิ่งดูเฉลยครับ

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

เช่น

=Offset(A1,-1,0)

ผลลัพธ์จะได้ #Ref!

เนื่องจาก A1 คือเซลล์แรกแล้ว ไม่มีเซลล์อื่นใดมาก่อน A1 อีก

คิดว่าสิ่งที่อธิบายมานี้ทำให้เข้าใจมากขึ้นสำหรับฟังก์ชั่น Offset และใช้งานกันอย่างเพลิดเพลินนะครับ

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

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

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

การที่เป็น Volatile นี้เองทำให้เราคำนึงให้มากสำหรับการใช้งาน เพราะถ้าใช้ Offset เป็นจำนวนมาก จะทำให้เครื่องคำนวณช้าลงอย่างมากครับ

---

เฉลย

=Sum(Offset(D5,-1,-2,-2,-2))

คือ Sum(A3:B4)

สูตรที่แสดงค่าผิดพลาดเมื่อปิดไฟล์

กรณีที่เราทำงานกับไฟล์ซึ่งมีการเชื่อมโยงกับไฟล์อื่น หากปิดไฟล์ต้นแหล่งไปแล้ว ทำไมสูตรที่เราเขียนไว้เกิดค่าผิดพลาดขึ้น เช่นติด #Ref! หรือ #Value!

ที่เป็นเช่นนี้เนื่องจากว่าเป็นข้อจำกัดของ Excel ในบางฟังก์ชั่นที่ไม่สามารถใช้กับไฟล์ที่ปิดไปแล้วได้ครับ ฟังก์ชั่นดังกล่าวได้แก่

1. Sumif
2. Countif
3. Offset
4. Indirect

หากปิดไฟล์ต้นแหล่ง 3 ตัวแรกจะเกิดค่าผิดพลาดเป็น #Value! และ Indirect จะเกิดค่าผิดพลาดเป็น #Ref! ดังนั้นในการใช้สูตรเพื่อหาข้อมูลจากไฟล์อื่นจำเป็นต้องระวังในการใช้สูตร 4 ตัวนี้ข้ามไฟล์ครับ

แทรกบรรทัดผลรวมสูตรไม่ขยายตามจะทำอย่างไร?

ปกติเมื่อเราแทรกบรรทัดที่เป็นผลรวมแล้วมีการกรอกตัวเลขเพิ่ม จะต้องมาแก้ไขสูตรผลรวมใหม่เพื่อให้รวมบรรทัดที่แทรกด้วยเสมอ ยกตัวอย่างเช่น A1:A5 มีข้่อมูลดังนี้

5
2
3
4
8

และที่ A6 เขียนสูตร

=Sum(A1:A5)

หากเราแทรกที่บรรทัด A6 แล้วคีย์ตัวเลขใด ๆ สูตรใน A7 จะไม่รวมตัวเลขที่เพิ่งคีย์ไปล่าสุด และยังคงแสดงเป็น

=Sum(A1:A5)

แต่ทั้งนี้เราต้องการให้แสดงเป็น

=Sum(A1:A6)

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

=Sum(Offset(A$1,0,0,Row()-Row(A$1),1))

จะทำให้สูตรขยายให้เสมอเมื่อมีการแทรกบรรทัดครับ