แผ่นสูตรสูตร Excel: คำแนะนำขั้นสูง

คุณผ่านพื้นฐานต่างๆ มาแล้ว จัดการเนื้อหาระดับกลาง และตอนนี้คุณก็พร้อมที่จะต่อสู้กับปืนใหญ่แล้ว — สูตรขั้นสูงของ Excel! Excel มีฟังก์ชันและสูตรขั้นสูงมากมายสำหรับการคำนวณที่ซับซ้อน ดังนั้นการมีคู่มือที่ชี้เฉพาะสิ่งที่คุณต้องการจึงเป็นประโยชน์

ข้อมูลสรุปเกี่ยวกับสูตร Excel นี้ครอบคลุมสูตรการคาดการณ์ขั้นสูง การวิเคราะห์ทางสถิติ ฟังก์ชันการจัดการข้อมูล การจัดการข้อผิดพลาด และอื่นๆ

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

โปรดดาวน์โหลดและพิมพ์แผ่นข้อมูลสรุปออกมาและเก็บไว้ให้สะดวก

ตกลง เริ่มกันเลย

Excel-สูตรโกงแผ่นขั้นสูง-1 ดาวน์โหลด

ก่อนอื่นมาเริ่มสูตร Array กันก่อน

สารบัญ

• สูตรอาร์เรย์
  • 1. ฟังก์ชั่นที่ไม่ซ้ำใคร
  • 2. ฟังก์ชัน SORT
  • 3. ฟังก์ชั่นกรอง
• การสุ่มฟังก์ชั่น Excel
  • 1. ฟังก์ชัน RANDBETWEEN
  • 2. ฟังก์ชันแรนดาร์เรย์
• สูตรการพยากรณ์ขั้นสูงใน Microsoft Excel
  • 1. ฟังก์ชัน FORECAST.ETS
  • 2. ฟังก์ชันเทรนด์
• สูตรทางสถิติขั้นสูง
  • 1. ฟังก์ชันเปอร์เซ็นไทล์
  • 2. ฟังก์ชันควอไทล์
• สูตรการวิเคราะห์และจัดการข้อมูลขั้นสูง
  • 1. ฟังก์ชันทรานสโพส
  • 2. ฟังก์ชันความถี่
  • 3. ฟังก์ชัน GETPIVOTDATA
• การจัดการข้อผิดพลาดขั้นสูง
• สูตรการค้นหาขั้นสูง
  • 1. ฟังก์ชัน XLOOKUP
  • 2. ฟังก์ชัน XMATCH
• ความคิดสุดท้าย

สูตรอาร์เรย์

ข้อมูลสรุปสำหรับผู้เริ่มต้นของเราแสดงวิธีจัดเรียงและกรองข้อมูลด้วยตนเอง ผู้ใช้ขั้นสูงทำเช่นนี้โดยทางโปรแกรมด้วยสูตรอาร์เรย์

สูตรอาร์เรย์ทำให้คุณสามารถคำนวณได้หลายเซลล์พร้อมกัน นี่คือหน้าที่หลักสามประการ:

1. มีเอกลักษณ์

2. เรียงลำดับ

3. กรอง

ฟังก์ชันเหล่านี้บางส่วนมีให้ใช้งานใน Microsoft Excel เวอร์ชันล่าสุดเท่านั้น

1. ฟังก์ชั่นที่ไม่ซ้ำใคร

ฟังก์ชัน UNIQUE ยอมรับช่วงและส่งกลับรายการค่าที่ไม่ซ้ำ

สมมติว่าคุณมีข้อมูลการขายสำหรับเสื้อผ้า หากต้องการค้นหารายการที่ไม่ซ้ำกันในคอลัมน์ A ให้ใช้สูตรนี้:

=เฉพาะ(A2:A6)

2. ฟังก์ชัน SORT

ฟังก์ชัน SORT จัดเรียงเนื้อหาของช่วง ไวยากรณ์คือ:

SORT(อาร์เรย์, [sort_index], [sort_order], [by_col])

• array : ช่วงของค่าที่จะเรียงลำดับ

• sort_index : คอลัมน์ที่จะเรียงลำดับ (1 ตามค่าเริ่มต้น)

• sort_order : 1 สำหรับจากน้อยไปมาก (ค่าเริ่มต้น) หรือ 2 สำหรับจากมากไปน้อย)

• by_col : TRUE เพื่อจัดเรียงตามคอลัมน์ (ค่าเริ่มต้น) หรือ FALSE เพื่อจัดเรียงตามแถว

อาร์กิวเมนต์สามตัวสุดท้ายเป็นตัวเลือก และค่าเริ่มต้นมักจะเป็นสิ่งที่คุณต้องการ

หากต้องการจัดเรียงข้อมูลตัวอย่างตามคอลัมน์แรก ให้ใช้สูตรนี้:

=เรียง(A2:C6)

3. ฟังก์ชั่นกรอง

ฟังก์ชัน FILTER ช่วยให้คุณกรองช่วงตามเงื่อนไขเฉพาะได้ นี่คือไวยากรณ์:

=FILTER(อาร์เรย์ รวม [if_empty])

• array : ช่วงที่จะกรอง

• รวม : เงื่อนไขที่กำหนดค่าที่จะกรอง

• if_empty : ระบุสิ่งที่จะส่งคืนหากไม่มีค่าที่ตรงกับเกณฑ์การกรอง (ค่าเริ่มต้นคือ “”)

สมมติว่าคุณต้องการกรองแถวในข้อมูลตัวอย่างเพื่อแสดงเฉพาะเมื่อมูลค่าการขายมากกว่า $15 ใช้สูตรนี้:

=ตัวกรอง(A2:C6, C2:C6>15)

การสุ่มฟังก์ชั่น Excel

สูตรโกงระดับกลางของเราแสดงวิธีใช้ฟังก์ชัน RAND ซึ่งสร้างตัวเลขสุ่มระหว่าง 0 ถึง 1

ผู้ใช้ Excel ขั้นสูงรู้วิธีใช้ฟังก์ชันสุ่มเพื่อสร้างข้อมูลตัวอย่างอย่างรวดเร็ว

1. ฟังก์ชัน RANDBETWEEN

ฟังก์ชัน RANDBETWEEN มีความยืดหยุ่นมากกว่า RAND เนื่องจากคุณสามารถระบุตัวเลขด้านล่างและด้านบนเป็นอย่างอื่นที่ไม่ใช่ 0 และ 1

หากต้องการสร้างข้อมูลที่มีตัวเลขระหว่าง 1 ถึง 100 ให้ป้อนสูตรนี้ลงในเซลล์ A1:

=RANDBETWEEN(1, 100)

จากนั้นคัดลอกเซลล์ไปยังแถวและคอลัมน์ได้มากเท่าที่คุณต้องการ ใช้เวลาไม่กี่วินาทีในการสร้างตารางตัวเลขสุ่ม:

2. ฟังก์ชันแรนดาร์เรย์

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

ไวยากรณ์คือ:

RANDARRAY([แถว], [คอลัมน์], [นาที], [สูงสุด], [จำนวนเต็ม])

• แถว : จำนวนแถว

• คอลัมน์ : จำนวนคอลัมน์

• min : จำนวนต่ำสุด

• max : จำนวนสูงสุด

• จำนวนเต็ม : ค่าเริ่มต้นเป็น TRUE มิฉะนั้นให้ใช้เลขฐานสิบ

หากต้องการสร้างตัวเลขสุ่มระหว่าง 1 ถึง 100 ใน 6 แถวและ 9 คอลัมน์ ให้ใช้สูตรนี้:

=RANDARRAY(6, 9, 1, 100, จริง)

สูตรการพยากรณ์ขั้นสูงใน Microsoft Excel

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

1. ฟังก์ชัน FORECAST.ETS

ฟังก์ชัน FORECAST ที่เก่ากว่าถูกแทนที่ด้วยชุดของฟังก์ชันที่ใหม่กว่าใน Excel 2016

คุณเลือกฟังก์ชันตามแบบจำลองการคาดการณ์ที่คุณต้องการ ตัวอย่างเช่น ฟังก์ชัน FORECAST.ETS ใช้อัลกอริทึมการปรับให้เรียบแบบเอ็กซ์โปเนนเชียล

ไวยากรณ์คือ:

FORECAST.ETS(target_date ค่า เส้นเวลา)

• target_date : วันที่ที่คุณต้องการค่าที่คำนวณได้

• ค่า : ข้อมูลประวัติ

• เส้นเวลา : ช่วงของวันที่

สมมติว่าคุณมีวันที่ตั้งแต่ 1 ถึง 5 มกราคมในคอลัมน์ A และยอดขายในคอลัมน์ B สูตรนี้จะทำนายยอดขายถัดไป:

=FORECAST.ETS(“6 มกราคม 2566”, B2:B6, A2:A6)

2. ฟังก์ชันเทรนด์

ฟังก์ชัน TREND จะฉายชุดของค่าโดยใช้วิธีกำลังสองน้อยที่สุด มันส่งกลับอาร์เรย์ ไวยากรณ์คือ:

เทรนด์(known_y, [known_x], [new_x], [const])

• Know_y : ช่วงของค่า y

• known_x : ช่วงของค่า x

• new_x : ช่วงของค่าที่คำนวณได้

บ่อยครั้งที่ Know_y เป็นจุดข้อมูล ในขณะที่ Know_x คือวันที่

โดยใช้ข้อมูลเดียวกับในตัวอย่างก่อนหน้านี้ คุณสามารถป้อนสูตรด้านล่างลงในเซลล์ C2 ชุดของค่าจะถูกสร้างขึ้น

=แนวโน้ม(B2:B6, A2:A6)

สูตรทางสถิติขั้นสูง

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

1. ฟังก์ชันเปอร์เซ็นไทล์

ฟังก์ชันนี้จะคำนวณเปอร์เซ็นต์ของจุดข้อมูลที่ต่ำกว่าค่าที่ระบุ ไวยากรณ์คือ:

PERCENTILE.INC(อาร์เรย์, k)

• อาร์เรย์ : ช่วงของเซลล์

• k : เปอร์เซ็นต์ไทล์จาก 0 ถึง 1

สมมติว่าคุณต้องการคำนวณเปอร์เซ็นไทล์ที่ 70 ของข้อมูลในคอลัมน์ B ให้ใช้สูตรนี้:

=PERCENTILE.INC(B2:B6, 0.7)

2. ฟังก์ชันควอไทล์

ฟังก์ชันนี้เป็นรูปแบบหนึ่งของฟังก์ชันเปอร์เซ็นไทล์ แต่ใช้ไตรมาสเพื่อแบ่งข้อมูล นี่คือไวยากรณ์:

QUARTILE.INC(อาร์เรย์ ควอร์ต)

• อาร์เรย์ : ช่วงของข้อมูล

• ควอร์ต : 1 สำหรับเปอร์เซ็นไทล์ที่ 25, 2 สำหรับเปอร์เซ็นไทล์ที่ 50, 3 สำหรับเปอร์เซ็นไทล์ที่ 75 และ 4 สำหรับค่าสูงสุด

สูตรด้านล่างจะคำนวณควอไทล์แรกของข้อมูลในคอลัมน์ A

=QUARTILE.INC(A2:A5, 1)

สูตรการวิเคราะห์และจัดการข้อมูลขั้นสูง

ฟังก์ชันขั้นสูงหลายฟังก์ชันช่วยให้คุณเปลี่ยนรูปแบบของข้อมูล วิเคราะห์การแจ��แจงความถี่ และแยกข้อมูลจากตาราง Pivot

1. ทรานสโพส

2. ความถี่

3. รับข้อมูล

1. ฟังก์ชันทรานสโพส

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

สมมติว่าคุณมีสินค้า "เสื้อยืด" "เสื้อกันหนาวหมวก" และ "กางเกงยีนส์" ในเซลล์ A2, A3 และ A4 คุณต้องการทำให้เป็นส่วนหัวของคอลัมน์ ฟังก์ชันนี้ส่งคืนค่าในแถวเดียว:

=TRANSPOSE(A2:A4)

2. ฟังก์ชันความถี่

ฟังก์ชันนี้จะคำนวณการแจกแจงความถี่ของค่าภายในชุดข้อมูล นี่คือไวยากรณ์:

ความถี่ (data_array, bins_array)

• data_array : ช่วงของค่า

• bins_array : ช่วงเวลาที่จะใช้

สมมติว่าคุณมีข้อมูลการขายในคอลัมน์ B และคุณต้องการวิเคราะห์การแจกแจงความถี่ของมูลค่าโดยพิจารณาจากจำนวน:

• ต่ำกว่า 20

• จาก 20 ถึง 80

• สูงกว่า 80

นั่นหมายถึงถังขยะสามใบและสามารถคำนวณได้ด้วยสูตรนี้:

=ความถี่(A2:A6, {20,80})

สำหรับข้อมูลเพิ่มเติมเกี่ยวกับการแจกแจงความถี่ใน Excel โปรดดูวิดีโอนี้:

3. ฟังก์ชัน GETPIVOTDATA

ฟังก์ชันนี้ช่วยให้คุณดึงข้อมูลสรุปจากตารางเดือย นี่คือไวยากรณ์:

GETPIVOTDATA(data_field, pivot_table, [field1, item1], [field2, item2], …)

• data_field : เขตข้อมูลหรือค่าที่คุณต้องการดึงจากตารางเดือย

• pivot_table : การอ้างอิงไปยังตารางสาระสำคัญ

• field1, item1, etc. : ฟิลด์/รายการคู่ที่จะกรอง

สมมติว่าคุณมีตารางเดือยตามสีของสินค้าที่ขาย หากต้องการแยกยอดขายสำหรับสินค้าสีแดง ให้ใช้สูตรนี้:

=GETPIVOTDATA(“ยอดขาย”, A1, “สี”, “สีแดง”)

การจัดการข้อผิดพลาดขั้นสูง

แม้แต่สูตร Excel พื้นฐานที่สุดก็สามารถสร้างข้อผิดพลาดได้ ผู้ใช้ระดับกลางควรรู้วิธีใช้ ISERROR เพื่อจัดการกับข้อผิดพลาด ผู้ใช้ขั้นสูงควรคุ้นเคยกับฟังก์ชัน ERROR.TYPE สำหรับการระบุข้อผิดพลาด

ฟังก์ชัน ERROR.TYPE ช่วยระบุประเภทข้อผิดพลาดเฉพาะภายในเซลล์หรือสูตร

โดยจะส่งคืนค่าตัวเลขที่สอดคล้องกับประเภทข้อผิดพลาดต่างๆ เช่น #N/A, #VALUE!, #REF! และอื่นๆ

สมมติว่าคุณมีข้อผิดพลาดในเซลล์ A1 และคุณต้องการระบุประเภทข้อผิดพลาด สูตรต่อไปนี้จะส่งกลับตัวเลขที่ตรงกับข้อผิดพลาดเฉพาะ:

=ประเภทข้อผิดพลาด(A1)

คุณสามารถรวมสิ่งนี้กับหลาย ๆ ฟังก์ชันเพื่อให้ตอบสนองแตกต่างกันไปตามประเภทของข้อผิดพลาด ข้อผิดพลาดที่พบบ่อยที่สุดและค่าเหล่านี้มีดังนี้

1. #โมฆะ! (ไม่พบเซลล์ทั่วไปในช่วง)

2. #DIV/0! ( หารด้วยศูนย์หรือเซลล์ว่าง)

3. #ค่า! (ชนิดข้อมูลหรืออาร์กิวเมนต์ที่ไม่เหมาะสมในสูตร)

4. #รีฟ! (เซลล์ที่อ้างอิงถูกลบหรือมีการอ้างอิงแบบวงกลม )

5. #ชื่อ? (Excel ไม่รู้จักฟังก์ชันหรือช่วง)

6. #NUM! (ค่าตัวเลขไม่ถูกต้อง)

7. #N/A (ไม่พบค่า)

สมมติว่าคุณต้องการจัดการข้อผิดพลาดสามประเภท ใช้สูตรนี้เพื่อแสดงข้อความแสดงข้อผิดพลาดเฉพาะตามประเภท:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, “หารด้วยข้อผิดพลาดศูนย์”, IF(ERROR.TYPE(C2)=3, “ข้อผิดพลาดค่าไม่ถูกต้อง”, IF(ERROR.TYPE(C2 )=7, “ข้อผิดพลาดไม่พบค่า”, “ข้อผิดพลาดอื่นๆ”))), C2)

สูตรการค้นหาขั้นสูง

ข้อมูลสรุปสำหรับผู้เริ่มต้นและระดับกลางของเราครอบคลุมฟังก์ชันการค้นหาที่เลือกไว้ ต่อไปนี้คือตัวเลือกขั้นสูง:

1. XLOOKUP

2. เอ็กซ์แมตช์

1. ฟังก์ชัน XLOOKUP

ฟังก์ชันการค้นหานี้ช่วยให้คุณค้นหาค่าในช่วงหนึ่งๆ และส่งกลับค่าที่สอดคล้องกันจากคอลัมน์หรือช่วงอื่น

มีความสามารถรอบด้านมากกว่าฟังก์ชันการค้นหาที่ง่ายกว่าเช่นVLOOKUP นี่คือไวยากรณ์:

XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

• lookup_value : ค่าที่คุณต้องการค้นหา

• lookup_array : ช่วงสำหรับการค้นหา

• return_array : ช่วงที่จะแสดงค่าที่สอดคล้องกัน

• match_mode : ตรงทั้งหมด (0), เล็กกว่าถัดไป (1), ใหญ่กว่าถัดไป (-1) หรือ จับคู่ ไวด์การ์ด (2)

• search_mode : -1 สำหรับจากบนลงล่าง 1 สำหรับจากล่างขึ้นบน หรือ 2 สำหรับการค้นหาไบนารี

• if_not_found : ตั้งค่าที่จะส่งคืนหากไม่พบการจับคู่

สมมติว่าคุณต้องการค้นหาช่วงของข้อมูลสำหรับการเกิดขึ้นครั้งแรกของรายการเสื้อผ้าและส่งคืนยอดขาย สูตรนี้จะค้นหาข้อความ “Hoodie” และส่งกลับค่าในเซลล์ที่อยู่ติดกันหากพบ:

=XLOOKUP(“เสื้อกันหนาวหมวก”, A2:A6, B2:B6)

2. ฟังก์ชัน XMATCH

ฟังก์ชันนี้ช่วยให้คุณค้นหาตำแหน่งของค่าที่ระบุภายในช่วงหรืออาร์เรย์ นี่คือไวยากรณ์:

XMATCH(lookup_value, lookup_array, [match_type], [โหมดการค้นหา])

• lookup_value : ค่าที่คุณต้องการค้นหา

• lookup_array : ช่วงที่คุณต้องการค้นหา

• match_type : ตรงทั้งหมด (0), เล็กที่สุดถัดไป (-1), ใหญ่ถัดไป (1)

• search_mode : การค้นหาแบบไบนารี (1) หรือการค้นหาเชิงเส้น (2)

สมมติว่าคุณต้องการค้นหารายการสีเหลืองที่เกิดขึ้นครั้งแรกในช่วงภายในคอลัมน์ B ให้ใช้สูตรนี้:

=XMATCH(“สีเหลือง”, B2:B6, 0)

ความคิดสุดท้าย

ข้อมูลสรุปนี้ครอบคลุมฟังก์ชันต่างๆ มากมาย ตั้งแต่การวิเคราะห์ทางสถิติ สูตรการค้นหา เทคนิคการจัดการข้อมูล และกลยุทธ์การจัดการข้อผิดพลาด

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

เมื่อคุณเริ่มรวมมันเข้ากับงาน Excel ของคุณ คุณกำลังจะเพิ่มทักษะ Excel ของคุณไปสู่ระดับสูง

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