การค้นหาและคืนค่ามาหลายคอลัมภ์ใน Excel

 เราจะใช้คำสั่ง Vlookup ในการค้นหาข้อมูลใน Excel โดย Vlookup มีรูปแบบคำสั่งดังนี้

=VLOOKUP("lookup_value", lookup_range, {col1, col2, col3...}, [match])`¹ `

โดย   lookup_value` คือค่าที่คุณต้องการค้นหาในแนวตั้ง และ  `lookup_range` คือช่วงข้อมูลสำหรับค้นหา `lookup_value` และค่าที่ตรงกัน¹ 

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

Employee ID	Name	Department	Salary
1	John	Sales	5000
2	Jane	Marketing	6000
3	Bob	IT	7000
4	Lily	HR	8000

จากตาราง ในเซลล์แรกของตารางใหม่ที่คุณต้องการแสดงชื่อ ให้ป้อนสูตร =VLOOKUP(A2,$A$2:$D$5,{2,3,4},FALSE) โดย

ที่ A2 คือเซลล์ที่มี รหัสพนักงานที่คุณต้องการค้นหา 

$A$2:$D$5 คือช่วงของตารางข้อมูลพนักงาน 

{2,3,4} หมายความว่าคุณต้องการคืนค่าจากคอลัมน์ 2 (ชื่อ), 3 (แผนก), และ 4 (เงินเดือน)

 และ FALSE หมายความว่าคุณต้องการการจับคู่แบบตรงทั้งหมด

กด Enter เพื่อรับผลลัพธ์

ตัวอย่างเช่น ถ้าคุณป้อนรหัสพนักงาน 2 ในเซลล์ A2 สูตรจะส่งกลับ Jane, Marketing และ 6000 ในเซลล์ที่คุณป้อนสูตร

การจัดรูปแบบเซลล์ใน Custom ให้มีสีใน Excel

 

หากต้องการจัดรูปแบบตัวเลขให้มีสีหรือเงื่อนไข คุณสามารถใช้ custom format และใส่โค้ดสีหรือเงื่อนไขได้  วิธีการคือ

• เลือกเซลล์ที่ต้องการจัดรูปแบบ
• คลิกขวาแล้วเลือก Format Cells
• เลือกแท็บ Number แล้วเลือกประเภท Custom
• ในช่อง Type ให้พิมพ์โค้ดรูปแบบตัวเลขที่ต้องการ เช่น [Red]0.00;[Blue]-0.00;[Green]0
• คลิก OK

โค้ดสีที่ใช้ใน custom format มีดังนี้

• [BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW]
• [COLOR 1-56] เป็นสีที่กำหนดจาก Theme Color

โค้ดเงื่อนไขที่ใช้ใน custom format มีดังนี้

• ; (เครื่องหมายอัญประกาศ) ใช้แยกส่วนของ custom format เป็นสี่ส่วน คือ positive;negative;zero;text
• [] (วงเล็บเหลี่ยม) ใช้กำหนดเงื่อนไขที่ต้องการ เช่น [<100]0.00;[>=100]0.0
• ? (เครื่องหมายคำถาม) ใช้แทนตำแหน่งของตัวเลขที่ไม่จำเป็นต้องแสดง เช่น ??.??;???.??

ตัวอย่างการใช้ custom format เพื่อจัดรูปแบบตัวเลขตามสีหรือเงื่อนไข

ตัวเลข	Custom Format	Result
12.34	[Red]0.00;[Blue]-0.00;[Green]0	Red color with 12.34
-12.34	[Red]0.00;[Blue]-0.00;[Green]0	Blue color with -12.34
0	[Red]0.00;[Blue]-0.00;[Green]0	Green color with 0
ABC	[Red]0.00;[Blue]-0.00;[Green]0	No color with ABC
99	[<100]0.00;[>=100]0.0	Two decimal places with 99.00
100	[<100]0.00;[>=100]0.0	One decimal place with 100.0
-5	??.??;???.??	Two spaces before -5
-50	??.??;???.??	One space before -50

 

การใส่เลข 0 หน้าตัวเลขใน Excel

วิธีหนึ่งในการเติมเลข 0 หน้าตัวเลขใน excel คือการเปลี่ยนรูปแบบของตัวเลขให้เป็น custom ใน excel ซึ่งก็คือการกำหนดรูปแบบตัวเลขที่ไม่มีให้เลือกในรูปแบบมาตรฐาน โดยใช้โค้ดรูปแบบตัวเลขที่มีความหมายต่างกัน เช่น

• 0 แสดงตัวเลขออกมาเสมอ ถ้าไม่มีจะเติมศูนย์

• แสดงตัวเลขออกมาเฉพาะที่จำเป็น ถ้าไม่มีจะไม่แสดง

• . แสดงจุดทศนิยม
• , แสดงเครื่องหมายคอมม่า
• % แสดงเปอร์เซ็นต์
• E+ E- e+ e- แสดงในรูปแบบเลขยกกำลัง
• \ ใช้ escape character เพื่อแสดงตัวอักษรพิเศษ
• “text” ใช้เพื่อแสดงข้อความที่กำหนด
• [ ] ใช้เพื่อกำหนดสีหรือเงื่อนไข

ตัวอย่างการใช้โค้ดรูปแบบตัวเลข

โค้ด	ผลลัพธ์	คำอธิบาย
#,##0.00	1,234.57	เพิ่มจุดทศนิยมสองตำแหน่งและคอมม่า
#,##0.00;[Red]-#,##0.00	-1,234.57	เพิ่มสีแดงให้กับค่าลบ
#,##0.00_);(#,##0.00)	(1,234.57)	เพิ่มวงเล็บให้กับค่าลบ
0%	10%	เพิ่มเครื่องหมาย %
# ?/?	1 2/3	เพิ่มเศษส่วน
#.00E+00	1.23E+03	เพิ่มรูปแบบเลขยกกำลัง
* #,##0 *	* 1,234 *	เพิ่ม * ทั้งสองข้าง
"Total "0.00	Total 1234.57	เพิ่มข้อความ Total

 

หากต้องการเติมเลข 0 ลงข้างหน้าตัวเลขเบอร์โทรศัพท์ เราสามารถใช้ custom format ใน excel ได้ วิธีการคือ

• เลือกเซลล์ที่ต้องการจัดรูปแบบ
• คลิกขวาแล้วเลือก Format Cells
• เลือกแท็บ Number แล้วเลือกประเภท Custom
• ในช่อง Type ให้พิมพ์โค้ดรูปแบบตัวเลขที่ต้องการ เช่น 0000000000
• คลิก OK

โค้ดรูปแบบตัวเลขที่ใช้ใน custom format จะมีหลักการดังนี้

• 0 แสดงตัวเลขออกมาเสมอ ถ้าไม่มีจะเติมศูนย์

• แสดงตัวเลขออกมาเฉพาะที่จำเป็น ถ้าไม่มีจะไม่แสดง

• \ ใช้ escape character เพื่อแสดงตัวอักษรพิเศษ เช่น -
• “text” ใช้เพื่อแสดงข้อความที่กำหนด

ตัวอย่างการใช้ custom format เพื่อจัดรูปแบบเบอร์โทรศัพท์

เบอร์โทร	Custom Format	Result
1234567890	(000)000-0000	(123)456-7890
1234567890	"Tel."0000-0000	Tel.1234-5678
1234567890	“Call” # ### ###	Call 1 234 567

ฟังก์ชั่น Select ใน AppSheet

 

                                                     ฟังก์ชั่น SELECT

 

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

รูปแบบของฟังก์ชั่น SELECT จะเป็นดังนี้:

SELECT(Table[Column], Condition)

โดย Table คือชื่อตารางที่ต้องการกรองข้อมูล และ Column คือชื่อคอลัมน์ที่ต้องการกรองข้อมูล ส่วน Condition คือเงื่อนไขการกรองข้อมูล ซึ่งสามารถใช้ตัวดำเนินการต่างๆ เช่น =, <, >, <=, >=, <> และ AND, OR ได้

ตัวอย่างการใช้งาน SELECT ใน AppSheet เพื่อกรองข้อมูลในตาราง:

SELECT(Orders[Customer Name], [Order Date] > "2022-01-01")

ซึ่งจะกรองแสดงเฉพาะชื่อลูกค้าในตาราง Orders ที่มีวันที่สั่งซื้อหลังจาก 2022-01-01 เท่านั้น

 

 

 

 

 

 

การใช้ Flash Fill ใน Excel

 

                                                          การใช้ Flash Fill ใน Excel

 

 

Flash Fill ใน Excel เป็นเครื่องมือที่ช่วยให้คุณสามารถตัด ต่อ แปลง หรือจัดรูปแบบข้อมูลในเซลล์ได้อย่างอัตโนมัติ โดยไม่ต้องเขียนสูตร หลักการทำงานของ Flash Fill คือ Excel จะพยายามหารูปแบบ (Pattern) จากข้อมูลที่คุณใส่เป็นตัวอย่าง เพื่อให้ได้ผลลัพธ์ที่คุณต้องการ¹².

วิธีการใช้ Flash Fill ใน Excel มีดังนี้

1. เตรียมข้อมูลดิบและคอลัมน์ผลลัพธ์ที่คุณต้องการได้
2. ใส่ตัวอย่างผลลัพธ์ที่คุณต้องการไปซัก 1-2 บรรทัด เพื่อทำการสอน Excel
3. คลิกที่เซลล์ที่เราสอน (หรือจะคลิกเซลล์ข้างใต้ก็ได้) แล้วกด Ctrl+E ทีละช่อง (ทีละคอลัมน์) เพื่อใช้ Flash Fill
4. ถ้า Excel ให้ผลลัพธ์ผิด ก็ต้องสอนเพิ่มไปอีกซักบรรทัดนึง เพื่อให้มันฉลาดขึ้น

Flash Fill เป็นเครื่องมือที่มีประโยชน์และสะดวกมาก แต่มันก็มีข้อเสียบ้าง เช่น

• Flash Fill ใช้ได้แค่ Excel 2013 ขึ้นไปเท่านั้น
• Flash Fill เป็นการ Stamp ค่าคงที่ลงไปแล้ว ไม่ใช่สูตร ดังนั้นถ้าข้อมูลเปลี่ยนหรือมีข้อมูลใหม่มา ผลลัพธ์จะไม่เปลี่ยนตาม

 

ถ้าคุณต้องการสร้างอีเมล์จากชื่อและนามสกุล คุณสามารถใช้ Flash Fill ได้ดังนี้

1. ใส่ตัวอย่างผลลัพธ์ที่คุณต้องการไปซัก 1-2 บรรทัด เช่น john.smith@email.com
2. คลิกที่เซลล์ที่เราสอน (หรือจะคลิกเซลล์ข้างใต้ก็ได้) แล้วกด Ctrl+E ทีละช่อง (ทีละคอลัมน์) เพื่อใช้ Flash Fill
3. Excel จะพยายามหารูปแบบ (Pattern) จากข้อมูลที่คุณใส่เป็นตัวอย่าง เพื่อให้ได้ผลลัพธ์ที่คุณต้องการ

ตัวอย่าง 

 

ชื่อ	นามสกุล	อีเมล์
John	Smith	john.smith@email.com
Mary	Jones	mary.jones@email.com
Peter	Lee	peter.lee@email.com

Flash Fill จะเติมข้อมูลให้คุณโดยอัตโนมัติ โดยไม่ต้องเขียนสูตร

ดูตัวอย่างการใช้งาน ได้ที่   

 

 

 

 

การใช้คำสั่ง TextSplit ใน Excel

 

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

 

สูตรการใช้งาน TEXTSPLIT ใน Excel มีรูปแบบดังนี้:

 

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with])

โดยที่:

• text: เป็นข้อความที่ต้องการแยก
• col_delimiter: เป็นตัวคั่นที่ใช้แยกข้อความเป็นคอลัมน์
• row_delimiter: เป็นตัวคั่นที่ใช้แยกข้อความเป็นแถว
• ignore_empty: เป็นการเลือกว่าจะไม่แสดงผลลัพธ์ที่เป็นช่องว่างหรือไม่ (TRUE/FALSE)
• match_mode: เป็นการเลือกว่าจะให้การแยกข้อความเป็นตัวพิมพ์เล็กหรือตัวพิมพ์ใหญ่ (0/1)
• pad_with: เป็นการเลือกว่าจะเติมช่องว่างหรือไม่ (TRUE/FALSE)

 ตัวอย่างการใช้งาน Textsplitที่ 1

=TEXTSPLIT("apple,banana,orange",",")

ผลลัพธ์ที่ได้คือ

apple
banana
orange

ตัวอย่างการใช้งาน Textsplit ที่ 2

=TEXTSPLIT(A2:A4," ")

ชื่อ - นามสกุล	First Name	Last Name
John Doe	John	Doe
Jane Smith	Jane	Smith
Jim Brown	Jim	Brown

ตัวอย่างการใช้งานอื่น ๆ ดูได้ที่