แฮ็ก Excel สำหรับผู้ที่เกี่ยวข้องกับการรายงานและการประมวลผลข้อมูล

2024 ผู้เขียน: Malcolm Clapton | [email protected]. แก้ไขล่าสุด: 2024-01-13 03:09

ในโพสต์นี้ Renat Shagabutdinov ผู้ช่วยผู้อำนวยการทั่วไปของ Mann, Ivanov และ Ferber Publishing House ได้แบ่งปันเคล็ดลับดีๆ เกี่ยวกับชีวิตใน Excel เคล็ดลับเหล่านี้จะเป็นประโยชน์สำหรับทุกคนที่เกี่ยวข้องกับการรายงาน การประมวลผลข้อมูล และการสร้างงานนำเสนอต่างๆ

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

ทางเลือกง่ายๆ สำหรับ VLOOKUP และ HLOOKUP หากค่าที่ต้องการไม่อยู่ในคอลัมน์แรกของตาราง: LOOKUP, INDEX + SEARCH

ฟังก์ชัน VLOOKUP และ HLOOKUP จะทำงานก็ต่อเมื่อค่าที่ต้องการอยู่ในคอลัมน์หรือแถวแรกของตารางที่คุณวางแผนจะรับข้อมูลเท่านั้น

มิฉะนั้น มีสองตัวเลือก:

1. ใช้ฟังก์ชัน LOOKUP

   มีไวยากรณ์ต่อไปนี้: LOOKUP (lookup_value; lookup_vector; result_vector) แต่เพื่อให้ทำงานได้อย่างถูกต้อง ค่าของช่วง view_vector ต้องเรียงลำดับจากน้อยไปมาก:

   

2. ใช้ฟังก์ชัน MATCH และ INDEX ร่วมกัน

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

   

   ไวยากรณ์ของฟังก์ชัน:

   • SEARCH (search_value; search_array; match_type) - สำหรับกรณีของเรา เราจำเป็นต้องมีประเภทการจับคู่ "ตรงทั้งหมด" ซึ่งตรงกับตัวเลข 0

   • INDEX (array; line_number; [column_number]) ในกรณีนี้ คุณไม่จำเป็นต้องระบุหมายเลขคอลัมน์ เนื่องจากอาร์เรย์ประกอบด้วยหนึ่งแถว

วิธีเติมเซลล์ว่างในรายการอย่างรวดเร็ว

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

เลือกคอลัมน์ "หัวเรื่อง" คลิกที่ริบบิ้นในกลุ่ม "หน้าแรก" ปุ่ม "ค้นหาและเลือก" → "เลือกกลุ่มเซลล์" → "เซลล์ว่าง" และเริ่มป้อนสูตร (นั่นคือใส่เท่ากับ เข้าสู่ระบบ) และอ้างอิงถึงเซลล์ที่ด้านบน เพียงแค่คลิกลูกศรขึ้นบนแป้นพิมพ์ของคุณ หลังจากนั้นกด Ctrl + Enter หลังจากนั้น คุณสามารถบันทึกข้อมูลที่ได้รับเป็นค่าได้ เนื่องจากไม่จำเป็นต้องใช้สูตรอีกต่อไป:

วิธีค้นหาข้อผิดพลาดในสูตร

การคำนวณส่วนต่าง ๆ ของสูตร

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

1. ในการคำนวณส่วนหนึ่งของสูตรในแถบสูตร ให้เลือกส่วนนั้นแล้วกด F9:

   

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

2. คลิกปุ่มคำนวณสูตรในกลุ่มสูตรบนริบบิ้น:

   

   ในหน้าต่างที่ปรากฏขึ้น คุณสามารถคำนวณสูตรทีละขั้นตอนและกำหนดขั้นตอนและฟังก์ชันใดที่เกิดข้อผิดพลาด (ถ้ามี):

   

วิธีการกำหนดว่าสูตรขึ้นอยู่กับหรืออ้างอิงถึงอะไร

เมื่อต้องการกำหนดเซลล์ที่สูตรขึ้นอยู่กับ ในกลุ่มสูตรบน Ribbon ให้คลิกปุ่มที่มีผลต่อเซลล์:

ลูกศรปรากฏขึ้นเพื่อระบุว่าผลการคำนวณขึ้นอยู่กับอะไร

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

เมื่อคลิกที่มัน เราจะเห็นได้ชัดเจนว่าเซลล์หรือช่วงที่มีอิทธิพลนั้นอยู่ที่ใด:

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

ปุ่ม "ลบลูกศร" ซึ่งอยู่ในบล็อกเดียวกัน ทำให้คุณสามารถลบลูกศรที่ส่งอิทธิพลต่อเซลล์ ลูกศรไปยังเซลล์ที่อยู่ติดกัน หรือลูกศรทั้งสองประเภทพร้อมกัน:

วิธีค้นหาผลรวม (จำนวน, ค่าเฉลี่ย) ของค่าเซลล์จากหลายแผ่น

สมมติว่าคุณมีแผ่นงานประเภทเดียวกันหลายแผ่นซึ่งมีข้อมูลที่คุณต้องการเพิ่ม นับ หรือประมวลผลด้วยวิธีอื่น:

เมื่อต้องการทำเช่นนี้ ในเซลล์ที่คุณต้องการดูผลลัพธ์ ให้ป้อนสูตรมาตรฐาน เช่น SUM (SUM) และระบุชื่อแผ่นงานแรกและแผ่นสุดท้ายจากรายการของแผ่นงานที่คุณต้องการดำเนินการ อาร์กิวเมนต์คั่นด้วยเครื่องหมายทวิภาค:

คุณจะได้รับผลรวมของเซลล์ที่มีที่อยู่ B3 จากแผ่นงาน "Data1", "Data2", "Data3":

ที่อยู่นี้ใช้ได้กับแผ่นงานที่อยู่ สม่ำเสมอ … ไวยากรณ์เป็นดังนี้: = FUNCTION (first_list: last_list! Range reference)

วิธีสร้างวลีเทมเพลตโดยอัตโนมัติ

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

• เราเชื่อมข้อความโดยใช้เครื่องหมาย & (คุณสามารถแทนที่ด้วยฟังก์ชัน CONCATENATE ได้ แต่นั่นไม่สมเหตุสมผลเลย)
• ข้อความจะถูกเขียนด้วยเครื่องหมายคำพูดเสมอ การอ้างอิงไปยังเซลล์ที่มีข้อความมักจะไม่มี
• ในการรับอักขระบริการ "เครื่องหมายคำพูด" ให้ใช้ฟังก์ชัน CHAR พร้อมอาร์กิวเมนต์ 32

ตัวอย่างการสร้างวลีเทมเพลตโดยใช้สูตร:

ผลลัพธ์:

ในกรณีนี้ นอกจากฟังก์ชัน CHAR (เพื่อแสดงเครื่องหมายคำพูด) แล้ว ฟังก์ชัน IF ยังใช้อยู่ ซึ่งช่วยให้คุณเปลี่ยนข้อความโดยขึ้นอยู่กับว่ามีแนวโน้มการขายเป็นบวกหรือไม่ และฟังก์ชัน TEXT ช่วยให้คุณแสดง ตัวเลขในรูปแบบใดก็ได้ ไวยากรณ์อธิบายไว้ด้านล่าง:

TEXT (ค่า รูปแบบ)

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

ข้อความที่ซับซ้อนมากขึ้นสามารถเป็นแบบอัตโนมัติได้ ในทางปฏิบัติของฉัน มีระบบอัตโนมัติของ long แต่ความคิดเห็นประจำต่อการรายงานการจัดการในรูปแบบ “ตัวบ่งชี้ลดลง / เพิ่มขึ้น XX เมื่อเทียบกับแผน ส่วนใหญ่เกิดจากการเติบโต / ลดลงของ FACTOR1 โดย XX, การเติบโต / ลดลงของ FACTOR2 โดย ปปป …” พร้อมรายการปัจจัยที่เปลี่ยนแปลง หากคุณเขียนความคิดเห็นดังกล่าวบ่อยๆ และขั้นตอนการเขียนความคิดเห็นเหล่านี้สามารถกำหนดอัลกอริทึมได้ ก็คุ้มค่าที่จะสร้างสูตรหรือมาโครที่จะช่วยให้คุณประหยัดงานบางส่วนได้เป็นอย่างน้อย

วิธีจัดเก็บข้อมูลในแต่ละเซลล์หลังจากการต่อกัน

เมื่อคุณผสานเซลล์ ค่าเดียวจะถูกเก็บไว้ Excel เตือนเกี่ยวกับสิ่งนี้เมื่อพยายามผสานเซลล์:

ดังนั้น หากคุณมีสูตรขึ้นอยู่กับแต่ละเซลล์ สูตรจะหยุดทำงานหลังจากรวมเข้าด้วยกัน (ข้อผิดพลาด # N / A ในบรรทัดที่ 3-4 ของตัวอย่าง):

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

วิธีสร้างเดือยจากแหล่งข้อมูลหลายแหล่ง

หากคุณต้องการสร้าง pivot จากแหล่งข้อมูลหลายแหล่งในคราวเดียว คุณจะต้องเพิ่ม "ตัวช่วยสร้าง PivotTable และแผนภูมิ" ลงใน Ribbon หรือแผงการเข้าถึงด่วน ซึ่งมีตัวเลือกดังกล่าว

คุณสามารถทำได้ดังนี้: "ไฟล์" → "ตัวเลือก" → "แถบเครื่องมือด่วน" → "คำสั่งทั้งหมด" → "ตัวช่วยสร้าง PivotTable และแผนภูมิ" → "เพิ่ม":

หลังจากนั้น ไอคอนที่เกี่ยวข้องจะปรากฏขึ้นบนริบบิ้น โดยคลิกที่ตัวช่วยสร้างเดียวกัน:

เมื่อคุณคลิกที่มัน กล่องโต้ตอบจะปรากฏขึ้น:

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

ในหน้าต่างถัดไป ให้เพิ่มช่วงทั้งหมดตามที่จะสร้างเดือย และตั้งชื่อให้กับพวกเขา:

หลังจากนั้น ในกล่องโต้ตอบสุดท้าย ให้ระบุตำแหน่งที่จะวางรายงานตารางสาระสำคัญ - บนแผ่นงานที่มีอยู่หรือแผ่นงานใหม่:

รายงานตารางสาระสำคัญพร้อมแล้ว ในตัวกรอง "หน้า 1" คุณสามารถเลือกแหล่งข้อมูลได้เพียงแหล่งเดียว หากจำเป็น:

วิธีคำนวณจำนวนครั้งของข้อความ A ในข้อความ B ("อัตราภาษี MTS SuperMTS" - ตัวย่อ MTS สองครั้ง)

ในตัวอย่างนี้ คอลัมน์ A มีบรรทัดข้อความหลายบรรทัด และงานของเราคือค้นหาว่าแต่ละบรรทัดมีข้อความค้นหาที่อยู่ในเซลล์ E1 กี่ครั้ง:

ในการแก้ปัญหานี้ คุณสามารถใช้สูตรที่ซับซ้อนซึ่งประกอบด้วยฟังก์ชันต่อไปนี้:

1. DLSTR (LEN) - คำนวณความยาวของข้อความ อาร์กิวเมนต์เดียวคือข้อความ ตัวอย่าง: DLSTR ("เครื่องจักร") = 6
2. SUBSTITUTE - แทนที่ข้อความเฉพาะในสตริงข้อความด้วยข้อความอื่น ไวยากรณ์: SUBSTITUTE (ข้อความ; old_text; new_text) ตัวอย่าง: SUBSTITUTE (“รถยนต์”; “อัตโนมัติ”; “”) = “มือถือ”
3. UPPER - แทนที่อักขระทั้งหมดในสตริงด้วยตัวพิมพ์ใหญ่ อาร์กิวเมนต์เดียวคือข้อความ ตัวอย่าง: UPPER (“เครื่องจักร”) = “CAR” เราต้องการฟังก์ชันนี้ในการค้นหาตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ ท้ายที่สุด UPPER ("รถยนต์") = UPPER ("เครื่องจักร")

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

DLSTR (“Tariff MTS Super MTS”) - DLSTR (“Tariff Super”) = 6

แล้วหารส่วนต่างนี้ด้วยความยาวของสตริงที่เรากำลังมองหา:

6 / DLSTR (“MTS”) = 2

เป็นสองเท่าที่บรรทัด "MTS" รวมอยู่ในบรรทัดเดิม

ยังคงเขียนอัลกอริธึมนี้ในภาษาของสูตร (ให้เราแสดงด้วย "ข้อความ" ข้อความที่เรากำลังมองหาเหตุการณ์และโดย "ค้นหา" ซึ่งเป็นจำนวนเหตุการณ์ที่เราสนใจ):

= (DLSTR (ข้อความ) -LSTR (แทนที่ (บน (ข้อความ); UPPER (การค้นหา), ""))) / DLSTR (การค้นหา)

ในตัวอย่างของเรา สูตรจะมีลักษณะดังนี้:

= (DLSTR (A2) -LSTR (แทนที่ (บน (A2), บน ($ E $ 1), “”))) / DLSTR ($ E $ 1)