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

ตอนนี้เรามาดูวิธีการทำงานของสูตรในตารางกันดีกว่า

บรรทัดผลรวม

ในการคำนวณผลรวมตารางจะมีแถวผลรวม หากต้องการแสดงให้เลือกเซลล์ใดก็ได้ภายในตารางและทำเครื่องหมายที่ช่องบนริบบิ้น ตัวสร้าง - ตัวเลือกรูปแบบตาราง - แถวผลรวม ... บรรทัด "ผลรวม" จะปรากฏด้านล่างตาราง เลือกเซลล์ใดก็ได้ในแถวนี้เพื่อคำนวณผลรวมของคอลัมน์ ในรายการดรอปดาวน์ให้เลือกสูตรที่โปรแกรมจะใช้คำนวณผลรวม ตัวอย่างเช่นผลรวมขั้นต่ำค่าเฉลี่ย ฯลฯ คลิกที่ "ฟังก์ชันเพิ่มเติม ... " เพื่อเลือกฟังก์ชันอื่น

แถวทั้งหมดใน Excel

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

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

การเขียนสูตรลงในคอลัมน์

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

สูตร Excel

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

การปรับแต่งสูตรตาราง Excel

อ้างถึงตารางตามชื่อ

คุณสามารถอ้างถึงเนื้อหาของตารางตามชื่อ ในการระบุชื่อของตารางให้ป้อนในฟิลด์บน Ribbon: ตัวสร้าง - คุณสมบัติ - ชื่อตาราง .

ตอนนี้เพื่อดำเนินการบางอย่างกับข้อมูลของตารางคุณสามารถอ้างอิงตามชื่อได้ ตัวอย่างเช่นโต๊ะของเราเรียกว่า People:

• Formula \u003d Sums (People) จะพยายามรวมเซลล์ตารางทั้งหมด
• สูตร \u003d ผลรวม (คน [Sales_Sum]) จะกำหนดผลรวมของข้อมูลในคอลัมน์ "Amount_sales" ของตาราง "People"
• \u003d คน [@Sales_Sum] / 100 - จะหารด้วยยอดขาย 100 รายการจากบรรทัดเดียวกัน

ถ้าหมายเลขบรรทัดของเซลล์ที่มีสูตรและเซลล์ในตารางไม่ตรงกัน Excel จะแสดงการอ้างอิงแบบเดิมพร้อมพิกัด

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

ขอบคุณสำหรับการอ่านโพสต์นี้. และในบทความต่อไปฉันจะบอกคุณ เจอกันบล็อกหน้า

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

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

วิธีเขียนสูตรใน Excel: แนวคิดพื้นฐาน

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

ความจริงก็คือเครื่องไม่เข้าใจอินพุตของชุดค่าผสมเช่น "2 x 2" หรือวางส่วนประกอบทั่วไปไว้นอกวงเล็บ ("2 + 2) 5") สำหรับสิ่งนี้มีการจัดเตรียมสัญลักษณ์หลายประเภทซึ่งแสดงไว้ในตารางด้านล่างไม่นับตัวดำเนินการเชิงตรรกะ

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

ผลรวมและผลต่าง

จะเขียนสูตรผลรวมหรือผลต่างใน Excel ได้อย่างไร? เริ่มจากวิธีที่ง่ายที่สุดเมื่อคุณต้องการคำนวณจำนวนเงิน ในบรรทัดของสูตร (ยิ่งไปกว่านั้นสำหรับการดำเนินการทั้งหมด) จะมีการป้อนเครื่องหมายเท่ากับก่อนหลังจากนั้นจึงป้อนสูตรที่ต้องการ ในกรณีของเครื่องคิดเลขทั่วไปคุณสามารถระบุสำหรับเซลล์ที่ติดตั้ง "\u003d 2 + 2"

หากดำเนินการรวมสำหรับค่าที่ป้อนโดยตรงในเซลล์อื่น (เช่น A1 และ A2) สูตรจะกลายเป็น "\u003d A1 + A2" ไม่ใช่เรื่องแปลกที่จะใช้วงเล็บเพื่อใช้ตัวดำเนินการเพิ่มเติม สำหรับความแตกต่าง - สิ่งเดียวกันมีเพียงเครื่องหมายลบแทนที่จะเป็นบวก

เมื่อคุณต้องการระบุจำนวนเซลล์หรือช่วงของเซลล์คุณสามารถใช้คำสั่ง sum พิเศษได้ (ในเวอร์ชันภาษารัสเซีย "SUM" เป็นภาษาอังกฤษ - SUM) เมื่อระบุหลายเซลล์จะมีลักษณะดังนี้: "\u003d SUM (A1; A2)" สำหรับช่วง - "SUM (A1: A10)" โดยมีเงื่อนไขว่าคุณจะต้องคำนวณผลรวมของตัวเลขทั้งหมดในเซลล์ตั้งแต่เซลล์แรกถึงเซลล์ที่สิบ โดยทั่วไปหากคุณตั้งค่า เซลล์ที่ใช้งานอยู่ซึ่งจะอยู่ถัดจากค่าสุดท้ายในคอลัมน์ที่มีค่าเริ่มต้นคุณไม่จำเป็นต้องป้อนสูตร แต่เพียงคลิกที่ปุ่มผลรวมอัตโนมัติ

การคูณการหารและการยกกำลัง

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

สำหรับผลิตภัณฑ์จะใช้แบบฟอร์ม "\u003d A1 * A2" สำหรับผลหาร - "A1 / A2" อย่างไรก็ตามคำสั่งเหล่านี้เป็นคำสั่งเดียวกับที่พบเมื่อใช้เครื่องคิดเลขมาตรฐานของ Windows

สัญลักษณ์ "^" ใช้สำหรับการยกกำลัง สำหรับค่าในเซลล์ A1 ซึ่งตัวอย่างเช่นต้องเป็นกำลังสองจะใช้สูตร "\u003d A1 ^ 2"

การคำนวณเปอร์เซ็นต์

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

ก็เพียงพอที่จะป้อนสูตรของรูปแบบ "\u003d A1 * 5%" หลังจากนั้นคุณจะได้รับห้าเปอร์เซ็นต์ของค่าในเซลล์ที่เกี่ยวข้อง

การใช้การป้อนสูตรตามการเลือกเซลล์

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

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

ประเภทเซลล์สัมบูรณ์สัมพัทธ์และแบบผสม

ควรสังเกตแยกกันว่าโปรแกรมสามารถใช้เซลล์หลายประเภทโดยไม่ต้องพูดถึงข้อมูลที่มีอยู่

เซลล์สัมบูรณ์ไม่มีการเปลี่ยนแปลงและแสดงเป็น $ A $ 1 สัมพัทธ์เป็นการอ้างอิงตำแหน่งปกติ (A1) แบบผสม - มีการอ้างอิงทั้งเซลล์สัมบูรณ์และเซลล์สัมพัทธ์ร่วมกัน ($ A1 หรือ A $ 1) โดยปกติรูปแบบดังกล่าวจะใช้เมื่อสร้างสูตรเมื่อข้อมูลเกี่ยวข้องกับแผ่นงานต่างๆของหนังสือหรือแม้แต่ในไฟล์ที่แตกต่างกัน

สูตร VLOOKUP

สุดท้ายมาดูวิธีการเขียนสูตร VLOOKUP ใน Excel เทคนิคนี้ช่วยให้คุณสามารถแทรกข้อมูลจากช่วงหนึ่งไปยังอีกช่วงหนึ่งได้ ในกรณีนี้วิธีการนี้ค่อนข้างคล้ายกับที่ใช้ในการแก้ปัญหาวิธีการเขียนสูตร "Condition" ใน Excel ซึ่งใช้สัญลักษณ์ที่แสดงในตารางด้านบน

โดยทั่วไปการคำนวณดังกล่าวเป็นเหมือนตัวกรองธรรมดาที่ใช้กับคอลัมน์ซึ่งคุณต้องการกรองเฉพาะค่าที่แน่นอนไม่ใช่ค่าโดยประมาณ

ในตัวเลือกนี้อันดับแรกผ่าน "ตัวช่วยสร้างฟังก์ชัน" จะใช้ช่วงของค่าของตารางดั้งเดิม (แรก) ช่วงที่สองพร้อมการแก้ไขเนื้อหา (F4) จะระบุไว้ในช่อง "ตาราง" จากนั้นหมายเลขคอลัมน์จะถูกระบุและค่า "FALSE" จะถูกตั้งค่าในช่องมุมมองช่วงเวลาหาก ในความเป็นจริงเมื่อกรองคุณจะต้องได้รับค่าที่ถูกต้องเท่านั้นไม่ใช่ค่าโดยประมาณ ตามกฎแล้วสูตรดังกล่าวจะถูกใช้มากขึ้นในคลังสินค้าหรือการบัญชีเมื่อไม่สามารถติดตั้งผลิตภัณฑ์ซอฟต์แวร์พิเศษใด ๆ ได้

สรุป

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

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

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

วิธีสร้างสูตรที่ซับซ้อนใน Excel

ในตัวอย่างด้านล่างเราจะสาธิตวิธีที่ Excel คำนวณสูตรที่ซับซ้อนตามลำดับการดำเนินการ ที่ ตัวอย่างนี้ เราต้องการคำนวณจำนวนภาษีขายสำหรับบริการอาหาร ในการดำเนินการนี้ให้เขียนนิพจน์ต่อไปนี้ในเซลล์ D4: \u003d (D2 + D3) * 0.075... สูตรนี้จะบวกมูลค่าของสินค้าทั้งหมดในใบแจ้งหนี้แล้วคูณด้วยจำนวนภาษีขาย 7,5% (เขียนเป็น 0.075)

Excel ทำตามคำสั่งและเพิ่มค่าในวงเล็บก่อน: (44.85+39.90)=$84.75 ... จากนั้นเขาก็คูณจำนวนนั้นด้วยอัตราภาษี: $84.75*0.075 ... ผลการคำนวณแสดงให้เห็นว่าภาษีขายจะเป็น $6.36 .

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

สร้างสูตรที่ซับซ้อนโดยใช้ลำดับการดำเนินการ

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

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

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

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

ใน Excel ทั้งหมดนี้ทำได้โดยใช้สูตรในเซลล์ สูตรเหล่านี้ใช้ในการคำนวณและการดำเนินการอื่น ๆ กับข้อมูลบนแผ่นงาน สูตรจะเริ่มต้นด้วยเครื่องหมายเท่ากับ (\u003d) เสมอหลังจากนั้นคุณสามารถป้อนตัวเลขตัวดำเนินการทางคณิตศาสตร์ (เช่นเครื่องหมาย + และ - สำหรับการบวกและการลบ) และฟังก์ชัน Excel ในตัวที่ช่วยขยายความสามารถของสูตรได้อย่างมาก

ด้านล่างนี้เป็นตัวอย่างของสูตรที่คูณ 2 ด้วย 3 และเพิ่ม 5 ในผลลัพธ์เพื่อให้ได้ 11

ต่อไปนี้เป็นตัวอย่างของสูตรที่สามารถใช้กับเวิร์กชีตได้

ส่วนต่างๆของสูตร Excel

สูตรยังสามารถมีองค์ประกอบอย่างน้อยหนึ่งรายการเช่น ฟังก์ชัน, ลิงค์, ผู้ประกอบการ และ ค่าคงที่.

ลำดับของการดำเนินการในสูตร

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

การใช้ฟังก์ชันและฟังก์ชันที่ซ้อนกันในสูตร Excel

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

ใช้ลิงก์ในสูตร Excel

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

การใช้ชื่อในสูตร Excel

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

ตัวอย่าง 1

ตัวอย่าง 2

คัดลอกข้อมูลตัวอย่างจากตารางด้านล่างและวางลงในเซลล์ A1 ของใหม่ แผ่นงาน Excel... ในการแสดงผลลัพธ์ของสูตรให้เลือกและกด F2 จากนั้นกด Enter นอกจากนี้คุณสามารถปรับความกว้างของคอลัมน์ตามข้อมูลที่มีได้

บันทึก: ในสูตรในคอลัมน์ C และ D ชื่อเฉพาะ Sales จะถูกแทนที่ด้วยการอ้างอิงถึงช่วง A9: A13 และชื่อข้อมูลการขายจะถูกแทนที่ด้วยช่วง A9: B13 หากหนังสือไม่มีชื่อเหล่านี้สูตรใน D2: D3 จะส่งกลับข้อผิดพลาด #NAME?

สำหรับข้อมูลเพิ่มเติมโปรดดูบทความกำหนดและใช้ชื่อในสูตร

การใช้สูตรอาร์เรย์และค่าคงที่อาร์เรย์ใน Excel

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

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

การใช้สูตรอาร์เรย์เพื่อคำนวณค่าหนึ่งค่าขึ้นไป

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

การใช้ค่าคงที่อาร์เรย์

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

ค่าคงที่อาร์เรย์สามารถมีตัวเลขข้อความค่าบูลีนเช่น TRUE หรือ FALSE หรือค่าความผิดพลาดเช่น # N / A ค่าคงที่อาร์เรย์หนึ่งค่าสามารถมีค่าได้หลายประเภทเช่น (1,3,4; TRUE, FALSE, TRUE) ตัวเลขในค่าคงที่อาร์เรย์อาจเป็นจำนวนเต็มทศนิยมหรือเลขชี้กำลัง ข้อความจะต้องอยู่ใน คำพูดคู่เช่น "วันอังคาร"

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

ค่าคงที่อยู่ในวงเล็บปีกกา ( { } ).

คอลัมน์ถูกคั่นด้วยลูกน้ำ ( , ). ตัวอย่างเช่นหากต้องการแสดงค่า 10, 20, 30 และ 40 ให้ป้อน (10,20,30,40) ค่าคงที่อาร์เรย์นี้เป็นเมทริกซ์ 1 คูณ 4 และสอดคล้องกับการอ้างอิงหนึ่งแถวและสี่คอลัมน์

ค่าของเซลล์จากเส้นต่างๆจะถูกคั่นด้วยอัฒภาค ( ; ). ตัวอย่างเช่นหากต้องการแสดงค่า 10, 20, 30, 40 และ 50, 60, 70, 80 ในเซลล์ด้านล่างซึ่งกันและกันคุณสามารถสร้างค่าคงที่อาร์เรย์ 2 คูณ 4: (10,20,30,40; 50, 60,70,80)

การลบสูตร

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

การใช้ลำไส้ใหญ่ ( : ) การอ้างอิงถึงเซลล์แรกและเซลล์สุดท้ายในช่วงจะถูกแยกออกจากกัน ตัวอย่างเช่น: A1: A5.

ระบุอาร์กิวเมนต์ที่จำเป็น

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

สูตรมีฟังก์ชันซ้อนกันไม่เกิน 64 ระดับ

ระดับการทำรังต้องไม่เกิน 64

ชื่อหนังสือและแผ่นงานอยู่ในเครื่องหมายคำพูดเดี่ยว

หากชื่อของแผ่นงานหรือเวิร์กบุ๊กที่คุณอ้างอิงเซลล์หรือค่ามีอักขระที่ไม่ใช่ตัวอักษรคุณต้องใส่เครื่องหมายคำพูดเดี่ยว ( " ).

มีการระบุเส้นทางไปยังหนังสือภายนอก

หมายเลขที่ป้อนโดยไม่มีการจัดรูปแบบ

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

สำคัญ: ผลลัพธ์จากการคำนวณของสูตรและฟังก์ชันแผ่นงาน Excel บางอย่างอาจแตกต่างกันเล็กน้อยในคอมพิวเตอร์ Windows x86 หรือ x86-64 และคอมพิวเตอร์ Windows RT ที่ใช้ ARM

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

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

สำหรับตัวอย่างที่ง่ายที่สุดลองคำนวณผลรวมของสองค่า เมื่อต้องการทำสิ่งนี้ในเซลล์ A1 ให้เขียนหมายเลข 5 และใน B1 หมายเลข 3 เพื่อให้คำนวณผลรวมโดยอัตโนมัติให้เขียนชุดอักขระต่อไปนี้ในเซลล์ C1 ซึ่งจะเป็นสูตร: \u003d SUM (A1; B1)

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

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

เพื่อให้งานซับซ้อนขึ้นเราขอแนะนำตัวเลขเศษส่วนในเซลล์ A1 และ B1 ซึ่งคำนวณได้ยากกว่ามาก ดังนั้น C1 จะถูกคำนวณใหม่และจะแสดงค่าที่ถูกต้อง

ใน Excel คุณสามารถทำได้ไม่เพียง แต่เพิ่ม แต่ยังรวมถึงการคำนวณทางคณิตศาสตร์อื่น ๆ : การลบการคูณการหาร สูตรควรขึ้นต้นด้วยสัญลักษณ์ "\u003d" เสมอและระบุพิกัดที่เรียกว่าของเซลล์ (ในตัวอย่างนี้คือ A1 และ B1)

สร้างสูตรด้วยตัวคุณเอง

ในตัวอย่างข้างต้นสูตร \u003d SUM (A1; B1) ช่วยให้คุณสามารถเพิ่มสองค่าได้ สัญลักษณ์ "\u003d" ถูกเข้าใจว่าเป็นจุดเริ่มต้นของสูตร SUM เป็นคำบริการที่แสดงถึงการคำนวณผลรวม A1 และ B1 เป็นพิกัดของค่าคงที่ซึ่งทำหน้าที่เป็นเงื่อนไขและต้องคั่นด้วยอัฒภาค ";"

พิจารณาตัวอย่างของการเพิ่มเซลล์ A1, B1, C1 สามเซลล์ สำหรับสิ่งนี้จะมีการเขียนสูตรที่เหมือนกันเกือบทั้งหมด แต่เซลล์ที่สามจะระบุเพิ่มเติมในวงเล็บ ดังนั้นจึงจำเป็นต้องเขียนชุดค่าผสมต่อไปนี้ \u003d SUM (A1; B1; C1) ในเซลล์ D1 เพื่อทำการคำนวณ

หากมีคำศัพท์จำนวนมากที่อยู่ในแถวเดียว (คอลัมน์) ตัวอย่างเช่น 10 สูตรสามารถทำให้ง่ายขึ้นโดยการระบุช่วงของพิกัด ดังนั้นสูตรจะมีลักษณะดังนี้ \u003d SUM (A1: A10) ในฐานะเซลล์สำหรับแสดงผลลัพธ์ที่ต้องการให้เลือกเซลล์ B6 ดังนั้นเราจะได้ผลลัพธ์ที่แสดงในภาพหน้าจอ

ในการคูณค่าที่ระบุทั้งหมดในคอลัมน์จำเป็นต้องเขียน PRODUCT ในสูตรแทน SUM

หมายเหตุ: หากผู้ใช้จำเป็นต้องดำเนินการเพิ่มเติมหรือการคำนวณทางคณิตศาสตร์อื่น ๆ ที่มีหลายคอลัมน์และแถวคุณสามารถระบุพิกัดตรงข้ามในแนวทแยงมุมและอาร์เรย์ของค่าจะถูกคำนวณ (เพื่อคำนวณผลคูณของค่า 10 แถวในคอลัมน์ A, B, C สูตรจะมีลักษณะดังนี้ : \u003d ผลิตภัณฑ์ (A1: C10))

สูตรรวม

สเปรดชีตมีความสามารถในการคำนวณไม่เพียง แต่คำนวณทางคณิตศาสตร์อย่างง่ายเท่านั้น แต่ยังรวมถึงการคำนวณทางคณิตศาสตร์ที่ซับซ้อนอีกด้วย

ตัวอย่างเช่นลองคำนวณผลรวมของค่าแล้วคูณด้วย 1.4 หากค่าน้อยกว่า 90 ในระหว่างการบวกถ้าผลลัพธ์มากกว่า 90 ก็ควรคูณด้วย 1.5 ในกรณีนี้สูตรมีตัวดำเนินการหลายตัวอยู่แล้วและดูซับซ้อนกว่ามาก: \u003d IF (SUM (A1: C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

มีการใช้สองฟังก์ชันที่นี่แทนหนึ่งฟังก์ชัน: IF และ SUM คำสั่งแรกคือคำสั่งเงื่อนไขที่ทำงานดังนี้:

• ถ้าผลรวมของค่าสามค่า (A1: C1) มากกว่า 90 จะทำการคูณด้วย 1.4 นั่นคือโดยสัมประสิทธิ์แรกที่ระบุไว้หลังเงื่อนไข
• ถ้า (A1: C1) น้อยกว่า 90 (ดังตัวอย่างที่เรากำหนด) นั่นคือไม่ตรงตามเงื่อนไขค่านี้จะคูณด้วยสัมประสิทธิ์ที่สอง

ในตัวอย่างของเรามีเพียงสองฟังก์ชั่น แต่ยังมีอีกมากมายโปรแกรมนี้ไม่ จำกัด ในทางปฏิบัติ

ฟังก์ชันในตัวในสเปรดชีต

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

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

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

ฟังก์ชัน VLOOKUP

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

ตัวอย่างเช่นหากเราต้องการค้นหานามสกุลซึ่งกำหนดให้เป็นหมายเลขประจำเครื่อง 007 เราจำเป็นต้องทำตามขั้นตอนที่แสดงไว้อย่างชัดเจนในภาพหน้าจอ

ค่าที่จะค้นหาถูกเลือกเป็นอาร์กิวเมนต์แรก ช่วงของค่าที่จะค้นหาถูกเลือกเป็นอาร์กิวเมนต์ที่สอง ตัวเลข 2 บ่งชี้ว่าควรแสดงค่าจากคอลัมน์ที่สองในเซลล์นี้

ผลลัพธ์คือผลลัพธ์ต่อไปนี้

ค่าการปัดเศษ

Excel สามารถปัดเศษได้อย่างแม่นยำเพื่อลดความซับซ้อนในการคำนวณ มีฟังก์ชันพิเศษ "ROUNDUP" สำหรับสิ่งนี้ ตัวอย่างเช่นหากเราต้องการปัดเศษตัวเลขที่ระบุในเซลล์ A1 ให้ป้อนในสูตรที่อยู่ติดกัน \u003d ROUNDUP (A1; 0) โดยที่อาร์กิวเมนต์ที่สองจะแสดงลำดับของตำแหน่งทศนิยมที่น่าเบื่อในการปัดเศษ (หากคุณระบุศูนย์ค่าทั้งหมดจะเป็นจำนวนเต็ม) ผลลัพธ์คือผลลัพธ์ต่อไปนี้

ดังที่คุณเห็นจากภาพหน้าจอการปัดเศษเกิดขึ้น เพื่อที่จะดำเนินการในขนาดเล็กมีฟังก์ชัน "ROUNDDOWN"

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