สูตร Spreadsheet สำคัญที่มืออาชีพทุกคนควรรู้

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

โครงสร้างของสูตร

ทุกสูตรเริ่มต้นด้วย = และอ้างอิงได้จาก:

• การอ้างอิงเซลล์: A1, B2:D10 (แบบสัมพัทธ์) หรือ $A$1, $B$2:$D$10 (แบบสัมบูรณ์)
• ชื่อช่วงข้อมูล: =SUM(Revenue) แทนที่จะเป็น =SUM(B2:B100)
• ฟังก์ชัน: SUM, VLOOKUP, IF, TEXT ฯลฯ
• ค่าคงที่: ตัวเลข, ข้อความในเครื่องหมายคำพูด, TRUE/FALSE

การอ้างอิงแบบสัมพัทธ์และสัมบูรณ์: เมื่อคัดลอกสูตร การอ้างอิงแบบสัมพัทธ์จะเลื่อนตามตำแหน่ง ส่วนการอ้างอิงแบบสัมบูรณ์ (ที่มี $) จะคงที่เสมอ

=B2*C2       → เมื่อคัดลอกลง จะกลายเป็น =B3*C3, =B4*C4...
=B2*$C$1     → C1 คงที่ (เช่น อัตราภาษี) ส่วน B2 เลื่อนตาม

สูตรค้นหาข้อมูล

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

ค้นหาค่าในคอลัมน์แรกของตารางและส่งคืนค่าจากคอลัมน์อื่น:

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

• A2 — ค่าที่ต้องการค้นหา
• $E$2:$G$100 — ตารางข้อมูล (ล็อกด้วย $)
• 2 — ส่งคืนค่าจากคอลัมน์ที่ 2 ของตาราง
• FALSE — ค้นหาแบบตรงทั้งหมด (ควรใช้ FALSE เสมอ)

ข้อจำกัดของ VLOOKUP: ค้นหาได้เฉพาะจากซ้ายไปขวา และหากแทรกคอลัมน์เพิ่มจะทำให้หมายเลขดัชนีคลาดเคลื่อน

INDEX / MATCH (ทางเลือกที่ดีกว่า)

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

ยืดหยุ่นกว่า VLOOKUP — ค้นหาได้ทุกทิศทาง:

=INDEX($G$2:$G$100, MATCH(A2, $E$2:$E$100, 0))

ส่งคืนค่าจากคอลัมน์ G ตรงที่คอลัมน์ E ตรงกับ A2 และการแทรกคอลัมน์ไม่ทำให้สูตรพัง

XLOOKUP (Excel และ Google Sheets รุ่นใหม่)

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

ตัวแทนรุ่นใหม่สำหรับทั้งสองสูตรข้างต้น:

=XLOOKUP(A2, $E$2:$E$100, $G$2:$G$100, "Not found")

Syntax กระชับกว่า จัดการกรณีที่ไม่พบค่าได้อย่างสวยงาม และทำงานได้ทุกทิศทาง

สูตรเงื่อนไข

IF

=IF(condition, value_if_true, value_if_false)
=IF(B2>1000, "High", "Low")
=IF(C2="", "Missing", C2)

IF ซ้อนกัน (ควรใช้ IFS แทน)

=IF(B2>=90,"A", IF(B2>=80,"B", IF(B2>=70,"C","F")))

-- กระชับกว่าด้วย IFS:
=IFS(B2>=90,"A", B2>=80,"B", B2>=70,"C", TRUE,"F")

AND / OR ในเงื่อนไข

=IF(AND(B2>100, C2="Active"), "Priority", "Normal")
=IF(OR(D2="VIP", E2>10000), "Discount", "")

สูตรรวมข้อมูล

SUMIF / SUMIFS

รวมค่าที่ตรงตามเงื่อนไข:

=SUMIF(range, criteria, sum_range)
=SUMIF(B:B, "Electronics", C:C)   -- รวม C เมื่อ B = "Electronics"

-- หลายเงื่อนไข:
=SUMIFS(C:C, B:B, "Electronics", D:D, ">2025-01-01")

COUNTIF / COUNTIFS

=COUNTIF(A:A, "Active")           -- นับแถวที่ A = "Active"
=COUNTIF(B:B, ">1000")            -- นับแถวที่ B > 1000
=COUNTIFS(A:A, "Active", C:C, "US")  -- ทั้งสองเงื่อนไข

AVERAGEIF

=AVERAGEIF(B:B, "Electronics", C:C)  -- เฉลี่ย C เมื่อ B = "Electronics"

สูตรจัดการข้อความ

การรวมข้อความ

=CONCATENATE(A2, " ", B2)          -- syntax แบบเก่า
=A2 & " " & B2                     -- syntax แบบใหม่ (แนะนำ)
=TEXTJOIN(", ", TRUE, A2:A10)      -- รวมด้วยตัวคั่น ข้ามเซลล์ว่าง

การดึงข้อความ

=LEFT(A2, 3)                       -- 3 ตัวอักษรแรก
=RIGHT(A2, 4)                      -- 4 ตัวอักษรสุดท้าย
=MID(A2, 3, 5)                     -- 5 ตัวอักษรเริ่มจากตำแหน่งที่ 3
=LEN(A2)                           -- จำนวนตัวอักษรทั้งหมด
=FIND("@", A2)                     -- ตำแหน่งของ @ ในข้อความ

การทำความสะอาดข้อความ

=TRIM(A2)                          -- ลบช่องว่างหน้า-หลัง
=UPPER(A2) / =LOWER(A2)           -- เปลี่ยนตัวพิมพ์
=PROPER(A2)                        -- Title Case
=SUBSTITUTE(A2, " ", "_")         -- แทนที่ช่องว่างด้วยขีดล่าง
=CLEAN(A2)                         -- ลบอักขระที่พิมพ์ไม่ได้

สูตรวันที่และเวลา

=TODAY()                           -- วันที่ปัจจุบัน
=NOW()                             -- วันที่และเวลาปัจจุบัน
=YEAR(A2) / =MONTH(A2) / =DAY(A2) -- ดึงส่วนประกอบ
=DATEDIF(A2, B2, "D")             -- จำนวนวันระหว่างสองวัน
=DATEDIF(A2, B2, "M")             -- จำนวนเดือนเต็มระหว่างสองวัน
=DATEDIF(A2, B2, "Y")             -- จำนวนปีเต็ม (มีประโยชน์สำหรับอายุ)
=NETWORKDAYS(A2, B2)              -- วันทำงานระหว่างสองวัน (ไม่รวมวันหยุดสุดสัปดาห์)
=EDATE(A2, 3)                     -- วันที่ 3 เดือนหลังจาก A2
=EOMONTH(A2, 0)                   -- วันสุดท้ายของเดือนใน A2

การทำงานกับข้อมูล: รูปแบบที่ใช้จริง

ลบข้อมูลซ้ำด้วย UNIQUE (รุ่นใหม่)

=UNIQUE(A2:A100)                   -- รายการค่าที่ไม่ซ้ำ

Dynamic arrays ด้วย FILTER

=FILTER(A2:C100, B2:B100="Active")   -- แถวที่ B = "Active"
=FILTER(A2:C100, (B2:B100="Active")*(C2:C100>1000))  -- ทั้งสองเงื่อนไข

เรียงลำดับแบบ dynamic ด้วย SORT

=SORT(A2:C100, 2, -1)              -- เรียงตามคอลัมน์ 2 จากมากไปน้อย

การแปลงและส่งออกข้อมูล

เมื่องาน Spreadsheet เสร็จสิ้นและต้องการย้ายข้อมูลไปยังระบบอื่น:

• ส่งออกเป็น CSV เพื่อความเข้ากันได้ทั่วไปด้วยตัวแปลง Excel to CSV ของเรา
• รวมไฟล์ CSV หลายไฟล์ด้วย CSV Merger
• แปลงเป็น JSON สำหรับใช้กับ API ด้วย JSON to Excel (ใช้ได้ทั้งสองทิศทาง)

ข้อผิดพลาดที่ควรหลีกเลี่ยง

1. ใส่ค่าตัวเลขตรง ๆ ในสูตร — ใช้การอ้างอิงเซลล์แทน =B2*0.08 จะดูเป็นปริศนาในภายหลัง แต่ =B2*$D$1 ที่มีป้ายกำกับ D1 ว่า "Tax Rate" นั้นอ่านเข้าใจได้ทันที
2. ไม่ล็อกการอ้างอิงเมื่อคัดลอก — ลืมใส่ $ ทำให้สูตรอ้างอิงเซลล์ผิด
3. ใช้ VLOOKUP กับ TRUE (การค้นหาแบบประมาณ) — ต้องการข้อมูลที่เรียงลำดับแล้ว และให้ผลลัพธ์แปลกประหลาดเมื่อข้อมูลไม่ได้เรียง
4. การอ้างอิงแบบวงกลม — สูตรที่อ้างอิงตัวเอง (หรือเซลล์ที่อ้างอิงกลับมา) Excel จะแจ้งเตือน
5. ผสมประเภทข้อมูล — การเก็บตัวเลขในรูปแบบข้อความ (ชิดซ้ายในเซลล์) จะทำให้ SUM และการเรียงลำดับใช้ไม่ได้

การเชี่ยวชาญ Spreadsheet คือพลังพิเศษอย่างหนึ่ง สูตรที่กล่าวมาข้างต้นครอบคลุมงานข้อมูลในชีวิตจริงถึง 90% ฝึกฝนกับชุดข้อมูลจริง แล้วคุณจะหยิบใช้สูตรซับซ้อนได้อย่างคล่องแคล่ว