3 สูตร Microsoft Excel บ้าๆ ที่มีประโยชน์สุดๆ

3 สูตร Microsoft Excel บ้าๆ ที่มีประโยชน์สุดๆ

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





ทำความรู้จัก Microsoft Excel

เราได้ครอบคลุมวิธีต่างๆ มากมายในการใช้ประโยชน์จาก Excel ให้ดียิ่งขึ้น เช่น จะหาเทมเพลต Excel ที่ยอดเยี่ยมที่ดาวน์โหลดได้ได้ที่ไหน , และ วิธีใช้ Excel เป็นเครื่องมือจัดการโครงการ .



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





มาดูวิธีที่คุณสามารถใช้สูตรและเครื่องมืออื่นๆ เพื่อใช้งาน Microsoft Excel ได้ดียิ่งขึ้น

การจัดรูปแบบตามเงื่อนไขด้วยสูตร Excel

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



ในการเข้าสู่ Conditional Formatting คุณเพียงแค่คลิกที่ บ้าน แท็บและคลิกที่ การจัดรูปแบบตามเงื่อนไข ไอคอนแถบเครื่องมือ

ภายใต้การจัดรูปแบบตามเงื่อนไข มีตัวเลือกมากมาย สิ่งเหล่านี้ส่วนใหญ่อยู่นอกเหนือขอบเขตของบทความนี้โดยเฉพาะ แต่ส่วนใหญ่เกี่ยวกับการเน้นสีหรือแรเงาเซลล์ตามข้อมูลภายในเซลล์นั้น



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

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



เมื่อคุณคลิกที่ จัดการกฎ ,มันจะพาคุณไปที่ ตัวจัดการกฎการจัดรูปแบบตามเงื่อนไข .

วิธีการติดตั้ง kodi บน raspbian

ขึ้นอยู่กับข้อมูลที่คุณเลือกก่อนเลือกชุดไอคอน คุณจะเห็นเซลล์ที่ระบุในหน้าต่างตัวจัดการพร้อมด้วยชุดไอคอนที่คุณเพิ่งเลือก

เมื่อคุณคลิกที่ แก้ไขกฎ คุณจะเห็นกล่องโต้ตอบที่เวทมนตร์เกิดขึ้น

นี่คือที่ที่คุณสามารถสร้างสูตรตรรกะและสมการที่จะแสดงไอคอนแดชบอร์ดที่คุณต้องการ

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

อย่างที่คุณเห็น แดชบอร์ดนี้แสดงให้เห็นว่าการจัดทำงบประมาณเวลาไม่ประสบความสำเร็จ

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

ถึงเวลาปรับโฟกัสและจัดการเวลาของคุณให้ดียิ่งขึ้น!

1. การใช้ฟังก์ชัน VLookup

หากคุณต้องการใช้ฟังก์ชันขั้นสูงของ Microsoft Excel ต่อไปนี้คือตัวเลือกสำหรับคุณที่จะลองใช้

คุณอาจคุ้นเคยกับฟังก์ชัน VLookup ซึ่งช่วยให้คุณค้นหารายการสำหรับรายการใดรายการหนึ่งในคอลัมน์เดียว และส่งคืนข้อมูลจากคอลัมน์อื่นในแถวเดียวกันกับรายการนั้น

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

ในตัวอย่างด้านล่าง จะเกิดอะไรขึ้นหากฉันต้องการค้นหางานที่ฉันทำเมื่อวันที่ 25/25/2018 จากข้อมูลต่อไปนี้

ในกรณีนี้ คุณกำลังค้นหาค่าทางด้านขวา และคุณต้องการคืนค่าที่สอดคล้องกันทางด้านซ้าย

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

คุณสามารถให้ VLookup ทำงานในลักษณะนี้โดยซ้อนฟังก์ชัน CHOOSE ลงไป ในกรณีนี้ สูตร Excel จะมีลักษณะดังนี้:

'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'

ฟังก์ชันนี้หมายความว่าคุณต้องการค้นหาวันที่ 25/25/2556 ในรายการค้นหา จากนั้นคืนค่าที่ตรงกันจากดัชนีคอลัมน์

ในกรณีนี้ คุณจะสังเกตเห็นว่าดัชนีคอลัมน์คือ '2' แต่อย่างที่คุณเห็น คอลัมน์ในตารางด้านบนคือ 1 จริงๆ ใช่ไหม

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

คุณกำลังกำหนดหมายเลข 'ดัชนี' อ้างอิงให้กับช่วงข้อมูล - กำหนดวันที่ให้กับดัชนีหมายเลข 1 และงานให้กับดัชนีหมายเลข 2

ดังนั้น เมื่อคุณพิมพ์ '2' ในฟังก์ชัน VLookup คุณกำลังหมายถึงดัชนีหมายเลข 2 ในฟังก์ชัน CHOOSE เจ๋งใช่มั้ย?

VLookup ตอนนี้ใช้ วันที่ คอลัมน์และส่งคืนข้อมูลจาก งาน คอลัมน์ แม้ว่างานจะอยู่ทางด้านซ้าย

ความเจ็บปวดของตัวรถมีความสำคัญต่อลูกค้าหลักเป็นภาษาอังกฤษ

เมื่อคุณรู้เกร็ดเล็กเกร็ดน้อยนี้แล้ว ลองนึกภาพว่าคุณจะทำอะไรได้อีก!

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

2. สูตรที่ซ้อนกันเพื่อแยกวิเคราะห์สตริง

นี่เป็นอีกหนึ่งสูตร Excel ที่บ้าที่สุดสำหรับคุณ! อาจมีบางกรณีที่คุณนำเข้าข้อมูลไปยัง Microsoft Excel จากแหล่งภายนอกซึ่งประกอบด้วยสตริงของข้อมูลที่คั่นด้วย

เมื่อคุณนำข้อมูลเข้ามาแล้ว คุณต้องการแยกวิเคราะห์ข้อมูลนั้นออกเป็นองค์ประกอบแต่ละส่วน ต่อไปนี้คือตัวอย่างข้อมูลชื่อ ที่อยู่ และหมายเลขโทรศัพท์ที่คั่นด้วย ';' อักขระ.

นี่คือวิธีที่คุณสามารถแยกวิเคราะห์ข้อมูลนี้โดยใช้สูตร Excel (ดูว่าคุณสามารถติดตามจิตใจไปพร้อมกับความวิกลจริตนี้ได้หรือไม่):

สำหรับฟิลด์แรก ในการแยกรายการซ้ายสุด (ชื่อของบุคคล) คุณเพียงแค่ใช้ฟังก์ชัน LEFT ในสูตร

'=LEFT(A2,FIND(';',A2,1)-1)'

ตรรกะนี้ทำงานอย่างไร:

  • ค้นหาสตริงข้อความจาก A2
  • ค้นหา ';' สัญลักษณ์ตัวคั่น
  • ลบหนึ่งสำหรับตำแหน่งที่เหมาะสมของส่วนท้ายของส่วนสตริงนั้น
  • หยิบข้อความซ้ายสุดไปยังจุดนั้น

ในกรณีนี้ ข้อความซ้ายสุดคือ 'ไรอัน' ภารกิจเสร็จสมบูรณ์.

3. สูตรซ้อนใน Excel

แต่ส่วนอื่น ๆ ล่ะ?

อาจมีวิธีที่ง่ายกว่านั้นในการทำเช่นนี้ แต่เนื่องจากเราต้องการลองสร้างสูตร Nested Excel ที่บ้าคลั่งที่สุดเท่าที่จะเป็นไปได้ (ซึ่งใช้งานได้จริง) เราจะใช้วิธีการที่ไม่เหมือนใคร

ในการแยกส่วนต่างๆ ทางด้านขวา คุณต้องซ้อนฟังก์ชัน RIGHT หลายฟังก์ชันเพื่อดึงส่วนของข้อความไปจนถึง ';' ก่อน สัญลักษณ์ และดำเนินการฟังก์ชัน LEFT อีกครั้ง นี่คือสิ่งที่ดูเหมือนสำหรับการดึงส่วนหมายเลขถนนของที่อยู่

'=LEFT((RIGHT(A2,LEN(A2)-FIND(';',A2))),FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))),1)-1)'

ดูเหมือนบ้า แต่ก็ไม่ยากที่จะประกอบเข้าด้วยกัน ทั้งหมดที่ฉันทำคือรับหน้าที่นี้:

RIGHT(A2,LEN(A2)-FIND(';',A2))

และสอดเข้าไปในทุกที่ใน ซ้าย ฟังก์ชั่นด้านบนที่มี 'A2'

สิ่งนี้จะแยกส่วนที่สองของสตริงอย่างถูกต้อง

เรียนรู้การเล่นกีตาร์แอพฟรี

แต่ละส่วนที่ตามมาของสตริงต้องมีการสร้างรังอื่น ตอนนี้สิ่งที่คุณต้องทำคือใช้ ' ขวา ' สมการที่คุณสร้างขึ้นในส่วนที่แล้ว และวางลงในสูตร RIGHT ใหม่ โดยวางสูตร RIGHT ก่อนหน้าลงในตำแหน่งที่คุณเห็น 'A2' นี่คือสิ่งที่ดูเหมือน

(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))

จากนั้น คุณต้องใช้สูตรนั้นและใส่ลงในสูตร LEFT เดิมทุกที่ที่มี 'A2'

สูตรดัดใจขั้นสุดท้ายมีลักษณะดังนี้:

'=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),FIND(';',(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),1)-1)'

สูตรนั้นแยก 'Portland, ME 04076' ออกจากสตริงดั้งเดิมได้อย่างถูกต้อง

หากต้องการแยกส่วนถัดไป ให้ทำซ้ำขั้นตอนข้างต้นทั้งหมดอีกครั้ง

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

ใช่ ตรงตามข้อกำหนดสำหรับ 'บ้า' แต่บอกตามตรง มีวิธีที่ง่ายกว่ามากในการทำสิ่งเดียวกันให้สำเร็จด้วยฟังก์ชันเดียว

เพียงเลือกคอลัมน์ที่มีข้อมูลคั่น แล้วใต้ ข้อมูล รายการเมนู เลือก ข้อความเป็นคอลัมน์ .

นี่จะแสดงหน้าต่างที่คุณสามารถแยกสตริงตามตัวคั่นที่คุณต้องการ เพียงแค่ใส่ ' ; ' และคุณจะเห็นว่าการแสดงตัวอย่างข้อมูลที่คุณเลือกเปลี่ยนแปลงไปตามนั้น

ในไม่กี่คลิก คุณสามารถทำสิ่งเดียวกับสูตรบ้าๆ ด้านบนนั้น... แต่ความสนุกของสิ่งนั้นมันอยู่ตรงไหน?

คลั่งไคล้สูตร Microsoft Excel

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

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

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

เครดิตรูปภาพ: kues/Depositphotos

แบ่งปัน แบ่งปัน ทวีต อีเมล หน้าที่ทางการเงิน 7 อันดับแรกใน Excel

ไม่ว่าคุณจะเป็นนักบัญชีหรือผู้เชี่ยวชาญด้านการเงิน คุณควรทราบสูตร Excel เหล่านี้

อ่านต่อไป หัวข้อที่เกี่ยวข้อง
  • ผลผลิต
  • เคล็ดลับสเปรดชีต
  • Microsoft Excel
  • Microsoft Office 2016
  • เคล็ดลับ Microsoft Office
  • Microsoft Office 2019
เกี่ยวกับผู้เขียน Ryan Dube(เผยแพร่บทความ 942 ฉบับ)

Ryan สำเร็จการศึกษาระดับปริญญาตรีสาขาวิศวกรรมไฟฟ้า เขาทำงาน 13 ปีในด้านวิศวกรรมระบบอัตโนมัติ 5 ปีในด้านไอที และปัจจุบันเป็นวิศวกรด้านแอป อดีตผู้จัดการบรรณาธิการของ MakeUseOf เขาพูดในการประชุมระดับชาติเรื่อง Data Visualization และได้รับการนำเสนอในโทรทัศน์และวิทยุแห่งชาติ

เพิ่มเติมจาก Ryan Dube

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก