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

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

Indirect

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

VDO อธิบายฟังก์ชั่น Indirect

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

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

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

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว (2)

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

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

แม้จะเป็นระดับการประยุกต์การใช้งานที่ซับซ้อนก็ตาม เราก็ต้องอาศัยฟังก์ชั่นเหล่านี้เป็นหลัก ซึ่งได้แก่
✏ 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

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

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

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

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

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

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

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

การค้นหาตัวเลขทะเบียนรถที่ถูกโฉลก

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

1. ให้รวมตัวเลขทุกตัวแล้วได้ค่าเป็น 9, 18, 27 หรือ 36
2. สามารถคัดชุดที่มีตัวเลขที่ไม่ต้องการออกไปได้ด้วย ยกตัวอย่างเช่นไม่รวมตัวเลขที่มีค่าต่อไปนี้ 0, 3, 7, 8

การหาค่าดังกล่าวสามารถใช้สูตรหรือ VBA มาคำนวณได้ครับ

กรณีใช้สูตรในการคำนวณมีวิธีการดังนี้

1. เปิดสมุดงานใหม่แล้วเลือกชีทว่าง
2. ที่ A1 คีย์ No. และ ที่ B1 คีย์ Select เพื่อเป็นชื่อ Field
3. ที่ A2 คีย์ Row()-1 > Copy ลงด้านล่างตามต้องการ
4. ที่ B2 คีย์สูตร
   =IF(MIN(LEN(SUBSTITUTE(A2,{0,3,7,8},"")))<LEN(A2),"",IF(ISNUMBER(MATCH(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),{9,18,27,36},0)),SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0),""))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

กรณีใช้ VBA ในการคำนวณมีวิธีการดังนี้

1. กดแป้น Alt+11 เพื่อเปิดหน้าต่าง VBE
2. คลิกเมนู Insert > Module
3. Copy Code ด้านล่างไปวาง
   
   

   Sub ListNum()
       Dim i As Integer, bln As Boolean
       Dim c As Integer, j As Integer
       Dim a As String, s As Integer
       For i = 1 To 9999
           bln = False
           s = 0
           For j = 1 To Len(CStr(i))
               a = Mid(i, j, 1)
               Select Case a
                   Case 0, 3, 7, 8
                       bln = True
                       Exit For
                   Case Else
                       s = s + a
               End Select
           Next j
           If bln = False Then
               Select Case s
                   Case 9, 18, 27, 36
                       c = c + 1
                       Cells(c + 1, 1) = i
               End Select
           End If
       Next i
   End Sub
   

   
   
   
   
4. กดแป้น Alt+Q เพื่อกลับมายังโปรแกรม Excel
   
5. กดแป้น Alt+F8 > เลือก ListNum > Run

โปรแกรมจะทำการ Run ตัวเลขมาให้ตามเงื่อนไขที่ต้องการ

ภาพตัวอย่างการใช้งาน

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

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

การนำข้อมูลจากหลาย Sheet มาต่อกันใน Sheet เดียว

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

ภาพ Sheet ที่ใช้รวมข้อมูล 



ภาพ Sheet ที่ 1 



ภาพ Sheet ที่ 2 



ภาพ Sheet ที่ 3 



จากภาพด้านบนเป็นการนำข้อมูล Sheet1, 2, 3 มาต่อกัน ให้ทำตามลำดับดังนี้ครับ

1. เขียนชื่อชีทเรียงกันไว้ตามต้องการที่ G1:G3
2. เซลล์ H1 คีย์สูตรเพื่อนับว่ามีข้อมูลใน Sheet1 เท่าไร
   =COUNTA(INDIRECT("'"&G1&"'!"&"A2:A65536"))
   Enter แล้ว Copy ลงด้านล่าง
3. เซลล์ H4 รวมจำนวนจากด้านบน คีย์
   =SUM(H1:H3)
   Enter
4. เซลล์ I1 ใช้สูตรในการ Run ข้อมูลใหม่เพื่อใช้ในสูตร Lookup
   =SUM($H$1:H1)-H1+1
   Enter แล้ว Copy ลงด้านล่าง
5. เซลล์ E2 คีย์สูตรเพื่อ List รายชื่อ Sheet
   =IF(ROWS($E$2:E2)>$H$4,"",LOOKUP(ROWS($E$2:E2),$I$1:$I$3,$G$1:$G$3))
   Enter แล้ว Copy ไปด้านล่าง
6. เซลล์ A2 คีย์สูตรเพื่อนำข้อมูลใน Sheet ต่าง ๆ มาแสดงต่อกัน
   =IF($E2<>"",INDIRECT("'"&$E2&"'!"&SUBSTITUTE(ADDRESS(1,COLUMN()),1,"")&COUNTIF($E$2:$E2,$E2)+1),"")
   Enter แล้ว Copy ไปทางขวาและลงด้านล่าง

จะเห็นว่าการนำข้อมูลจากหลาย ๆ Sheet มาต่อกันนั้น ไม่ใช่เรื่องยากอีกต่อไปสำหรับพวกเราครับ

การกำหนดให้อ้างอิงตำแหน่งเดิมแม้มีการแทรกคอลัมน์หรือแทรกบรรทัด

ปกติหากมีการย้ายเซลล์ สูตรของเราจะทำการเปลี่ยนแปลงให้อัตโนมัติ ยกตัวอย่างเช่นที่ B1 คีย์สูตร

=A1

หากเราย้าย A1 ไปที่ E1 สูตรที่ B1 จะกลายเป็น

=E1

หากเรายังต้องการให้ B1 อ้างถึง A1 เช่นเดิมไม่ว่าจะย้าย A1 ไปไหน ต้องใช้ฟังก์ชั่น Indirect เข้ามาช่วย ซึ่งจะต้องเขียนสูตรที่ B1ใหม่เป็น

=Indirect("A1")

คราวนี้ไม่ว่าเราจะย้าย A1 ไปไหน ค่าในเซลล์ B1 จะยังคงอ้างอิงค่าในเซลล์ A1 เสมอไป เช่น

A1 มีค่าเท่ากับ 5, B1 คีย์สูตร =Indirect("A1") เมื่อทำการย้าย A1 ไปที่ E1 จะทำให้ E1 มีค่าเท่ากับ 5 และ A1 มีค่าเป็นค่าว่าง

เซลล์ B1 จะมีค่าเป็น 0 ซึ่งเป็นค่า A1 (ค่าว่างเมื่อเชื่อมโยงไปเซลล์อื่นจะแสดงค่า 0) ไม่ใช่ 5 ซึ่งแสดงใน E1

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

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

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

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

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

แยกภาษาไทยออกจากอังกฤษหรืออังกฤษออกจากไทย

กรณีที่มีการเขียนภาษาอังกฤษต่อด้วยภาษาไทยหรือตรงกันข้ามกัน และต้องการแยกคำออกมา สามารถใช้สูตรในการแยกภาษาออกมาได้ครับ สมมุติ

1. เซลล์ A1 มีคำว่า Bangkokกรุงเทพมหานคร ต้องการแยก Bangkok และกรุงเทพมหานครให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =LEFT(A1,MATCH("zzz",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยกกรุงเทพมหานครออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter
2. เซลล์ A1 มีคำว่า กรุงเทพมหานครBangkok ต้องการแยก กรุงเทพมหานคร และ Bangkok ให้อยู่คนละเซลล์สามารถทำได้ดังนี้
1. เซลล์ B1 แยกกรุงเทพมหานครออกมาโดยคีย์สูตร
   =LEFT(A1,MIN(SEARCH(CHAR(ROW(INDIRECT("65:90"))),A1&"abcdefghijklmnopqrstuvwxyz")-1))
   Ctrl+Shift+Enter
2. เซลล์ C1 แยก Bangkok ออกมาแสดงโดยคีย์สูตร
   =Substitute(A1,B1,"")
   Enter

Note: สูตรที่ B1 ทั้งสองสูตรเป็นสูตรแบบอาร์เรย์ การคีย์ให้รับสูตรจะต้อง Ctrl+Shift+Enter ครับ