22 ข้อจำกัดของ Excel ที่อาจทำให้คุณผิดหวัง

และ จัดรูปแบบเซลล์ เป็นแบบนี้

โดยสรุป ฟังก์ชัน TEXT เป็นตัวเลือกที่ดีสำหรับคุณในการจัดรูปแบบค่าของคุณ ตัวอย่างเช่น คุณสามารถใช้รูปแบบ YYYY-MM-DD เพื่อแสดง 2015-05-23 .

2. การลบเลขศูนย์นำหน้า

หากคุณป้อนหมายเลขประกันสังคม หมายเลขโทรศัพท์ หมายเลขบัตรเครดิต หรือรหัสไปรษณีย์ลงในเวิร์กบุ๊ก Excel จะถือว่าหมายเลขนั้นเป็นตัวเลขและใช้รูปแบบตัวเลขทั่วไปกับหมายเลขดังกล่าว เป็นผลให้ นำศูนย์ จะถูกลบออกจากรหัสตัวเลขเหล่านั้น ข้อจำกัดของ Excel นี้เกิดขึ้นเสมอ ตัวอย่างเช่น คุณจะได้รับ 123 หากคุณป้อน 000123 เข้าสู่เซลล์ B5 ของภาพด้านล่าง.. ในทำนองเดียวกัน Excel จะแสดง 1234 ถ้าคุณใส่ 001234 ลงในเซลล์ B6 . แน่นอนว่านี่ไม่ใช่สิ่งที่คุณต้องการ ค่าในเซลล์ตั้งแต่ B5 ผ่าน B7 คือสิ่งที่คุณต้องการ แล้วต้องทำอย่างไร?
เช่นเดียวกับปัญหาข้างต้น เราสามารถใช้ ฟังก์ชัน TEXT ที่นี่เพื่อใส่รหัสตัวเลขด้วยเลขศูนย์นำหน้า .
อีกวิธีในการเพิ่มศูนย์นำหน้าคือการใส่เครื่องหมายอัญประกาศเดี่ยว (‘) ก่อนรหัสตัวเลข
คุณสามารถเพิ่มฟังก์ชัน TEXT ใน D5 เซลล์แบบนี้

=TEXT($C5,"000000")

ผลลัพธ์หลังจากกด ENTER และใช้ Fill Handle เป็นแบบนี้

3. การจัดรูปแบบตัวเลขขนาดใหญ่เป็นสัญกรณ์วิทยาศาสตร์

ตัวเลขจำนวนมากที่ป้อนลงใน Excel จะถูกจัดเก็บเป็นตัวเลขและย่อด้วยสัญกรณ์วิทยาศาสตร์ เป็นข้อจำกัดทั่วไปของ Excel สมมุติว่าใส่ตัวเลขยาวๆ เช่น 12409003888123 ในเซลล์ B2 . Excel จะใช้ตัวเลขทั่วไปกับตัวเลขนั้นและ 12409003888123 จะถูกนำเสนอในรูปแบบสัญกรณ์วิทยาศาสตร์ว่า 1.2409E+13 . เป็นไปได้ไหมที่จะป้องกันไม่ให้ Excel ทำเช่นนี้?
วิธีที่ง่ายที่สุดคือ คลิกขวา บนเซลล์ B2 . จากนั้นเลือก จัดรูปแบบเซลล์ เพื่อเปิด จัดรูปแบบเซลล์ กล่องโต้ตอบ ในข้อความแจ้ง จัดรูปแบบเซลล์ กล่องโต้ตอบ เลือก หมายเลข และกำหนดจำนวนตำแหน่งทศนิยมเป็น 0 . จนถึงตอนนี้ ตัวเลขที่ยาวจะแสดงแทนสัญกรณ์วิทยาศาสตร์

4. การใช้ฟังก์ชัน VLOOKUP เพื่อจับคู่โดยประมาณ

ข้อจำกัดทั่วไปของ Excel คือหลายคนบ่นว่าคุณไม่เคยแน่ใจว่า VLOOKUPคืออะไร ทำกับ การจับคู่โดยประมาณ . ในภาพด้านล่าง หากค่าการค้นหาของคุณคือ chun , Excel จะส่งกลับ $93,500 . เมื่อเปรียบเทียบกับตารางข้อมูลในช่วง B5:E12 คุณจะพบว่าค่านี้ไม่ใช่สิ่งที่เราต้องการ ในการแสดงผลของเรา ค่าที่ถูกต้องควรเป็น $151,200 เป็น ชุน คล้ายกับ ชุง .

VLOOKUP การจับคู่โดยประมาณ ไม่ได้เป็นไปตามที่เราคิด โดยจะย้ายผ่านตารางค้นหาทีละแถวและหยุดในแถวที่ค่าน้อยกว่าหรือเท่ากับค่าการค้นหาในที่สุดเมื่อค่าในแถวถัดไปมากกว่าค่าการค้นหา
มาดูตัวอย่างด้านบนกันอีกครั้ง นักขาย น้อยกว่า ชุน ขณะที่ ชุง มีค่ามากกว่า ชุน ดังนั้น Excel จึงหยุดในวันที่ 6 ^th แถวและคืนค่า $93,500 ใน คอลัมน์ C . ในทำนองเดียวกัน Excel หยุดทำงานในวันที่ 5 ^th แถวหลังจากที่มีมูลค่า $71,900 ซึ่งน้อยกว่า $89,450 และค่าในแถวถัดไปจะมากกว่า $89,450 . สี่แยก ^ที่ แถวและ คอลัมน์ D ให้อัตราค่าคอมมิชชัน 6% .

อ่านเพิ่มเติม:  วิธีการสร้าง Add-in ของ Excel [บรรยายด้วยตัวอย่าง]

อย่างไรก็ตาม หากคุณดูช่วงข้อมูลอย่างใกล้ชิด คุณจะพบว่าอัตราค่าคอมมิชชันสำหรับ $89,450 คือ %7 ซึ่งแตกต่างจาก %6 ที่เราเพิ่งได้จากรูปที่ 4.1 มีอะไรผิดปกติหรือไม่? ใช่ เราต้องเรียงลำดับตารางค้นหาจากน้อยไปมากก่อนจึงจะใช้การจับคู่โดยประมาณ เนื่องจาก VLOOKUP จะเดินลงมาตามตารางค้นหาทีละแถว
รูปด้านล่างแสดงตารางค้นหาอื่นในช่วง B5:E12 . ตารางนี้จัดเรียงตาม ยอดขาย ใน จากน้อยไปมาก ลำดับ. ตอนนี้ คุณจะพบว่าอัตราค่าคอมมิชชัน $89,450 ถูกต้องแล้ว

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

5. ฟังก์ชัน VLOOKUP ไม่คำนึงถึงขนาดตัวพิมพ์

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

การรวมกันของ INDEX, MATCH, และ แน่นอน สามารถใช้ฟังก์ชันที่นี่เพื่อรับหมายเลขที่ถูกต้อง 23 . ช่วง G5:G7 ให้สูตรส่งคืนตัวเลขในช่วง F5:F7 . สูตรเป็นแบบนี้ค่ะ

=INDEX($B$4:$C$12,MATCH(TRUE,INDEX(EXACT($E5,$B$4:$B$12),0),0),2)

ที่นี่ MATCH(TRUE,INDEX(EXACT($E5,$B$4:$B$12),0),0) เป็นกุญแจสำคัญในการแก้ไขปัญหา ซึ่งจะให้การอ้างอิงแถวที่ตรงกันทุกประการ (มารี ในกรณีของเรา) มีอยู่ ลองด้วยตัวคุณเอง

อีกวิธีที่ง่ายกว่าคือการใช้ OFFSET . ร่วมกัน และ ฟังก์ชัน MATCH . คุณสามารถเขียนสูตรใน F15 เซลล์แบบนี้

=OFFSET($B$4,MATCH($E15,$B$4:$B$12,0),1)

ในที่สุด คุณจะได้ผลลัพธ์ 23 หลังจากกด ENTER . คุณจะได้ผลลัพธ์ทั้งหมดพร้อมกันโดยใช้ เติม ด้ามจับ .

6. ไม่สามารถเริ่มจากคอลัมน์หรือแถวตามอำเภอใจที่มีฟังก์ชัน VLOOKUP

เป็นเรื่องที่น่ารำคาญที่เราสามารถทำได้เฉพาะการค้นหาจากซ้ายไปขวาด้วย ฟังก์ชัน VLOOKUP . แต่ถ้าคุณได้อ่านบทความแนะนำใน มาตรา 5 คุณจะรู้ว่า การ ฟังก์ชัน OFFSET/MATCH สามารถช่วยให้คุณเริ่มการค้นหาจากคอลัมน์หรือแถวใดก็ได้ ในตัวอย่างต่อไปนี้ ค่าในเซลล์ C10 จะเปลี่ยนไปเมื่อคุณเปลี่ยนค่าในเซลล์ C9 และ B10 .

7. ฟังก์ชัน VLOOKUP ไม่ปรับการชดเชยคอลัมน์ขณะลบคอลัมน์

สมมติว่าเรามีช่วงการค้นหาที่เริ่มต้นจาก C5 ผ่าน E12 . และเราทำ VLOOKUP การจับคู่โดยประมาณ อยู่ในช่วง G4:I6 . ลองเอาสูตรในเซลล์ H5 ตัวอย่างเช่น. สูตรจะเป็นถ้าเราต้องการดึงค่าคอมมิชชั่นโดยประมาณสำหรับ ชุน .

=VLOOKUP(G5,B5:E12,3,TRUE)

หลังจากลบคอลัมน์ C คุณจะพบว่าสูตรข้างต้นจะถูกแทนที่ด้วยสูตรต่อไปนี้

=VLOOKUP(O3,K3:M10,3,TRUE)

ในที่สุด ตารางค้นหาก็เปลี่ยนโดยอัตโนมัติ แม้แต่การอ้างอิงเซลล์สำหรับค่าการค้นหาก็เปลี่ยนจาก “G5 ” ถึง “F5 ” ทั้งหมดนี้ถูกต้อง แต่การชดเชยคอลัมน์ยังคงเป็น 3 . อันที่จริงมันควรจะเป็น 2 เนื่องจากอัตราค่าคอมมิชชันอยู่ในคอลัมน์ที่สองของตารางค้นหาในขณะนี้

8. ความยากในการดูเนื้อหาแบบยาวของเซลล์

ในรูปด้านล่าง คุณจะเห็นว่าการอ้างอิงเซลล์จะเปลี่ยนไปหลังจากที่คุณคัดลอกสูตร =COUNTIFS($C3:$C10,”=”&$F3) จากเซลล์ G3 ไปยังเซลล์ G4 . สูตรคือ =COUNTIFS($C4:$C11,”=”&$F4) ในเซลล์ G4 และสูตรนี้จะคืนค่า 4 . แต่ถ้าดูช่วง B2:D10 อย่างใกล้ชิดจะพบว่าจำนวนนักเรียนชายน่าจะ 5 แทนที่จะเป็น 4 . เราผิดพลาดที่นี่ เหตุผลก็คือคุณใช้การอ้างอิงแบบสัมพัทธ์ – $C3:$C10 และสามารถเปลี่ยนแปลงได้หลังจากที่คุณคัดลอกไปยังเซลล์อื่น วิธีที่ถูกต้องคือการใช้การอ้างอิงแบบสัมบูรณ์และแทนที่ “$C3:$C10” กับ “$C4:$C11” . เช่นเดียวกับที่เราทำในช่วง F7:H9 . การวางสูตรผิดพลาดได้ง่ายมาก และคุณควรระมัดระวัง

9. ไม่ง่ายที่จะดูเนื้อหาทั้งหมดหากเนื้อหาของเซลล์ยาวเกินไป

เพื่อแสดงปัญหานี้ ฉันเพิ่งคัดลอกย่อหน้าจากบทความนี้ลงในเซลล์ B6 . รูปด้านล่างแสดงให้เห็นว่ามีเพียงหนึ่งบรรทัดในเวิร์กชีตและเป็นการยากที่จะดูเนื้อหาทั้งหมด

หลังจากเพิ่มตัวแบ่งบรรทัดภายในเซลล์ B6 โดยกด ALT + ENTER , เราจะได้ของดังรูปข้างล่างนี้. มี 11 ตอนนี้เลย

10. ไม่สามารถทำงานร่วมกับ Microsoft Word ได้อย่างราบรื่น

Excel แบ่งหนึ่งแถวที่มี การขึ้นบรรทัดใหม่ ลงในหลายแถวเมื่อคัดลอกตารางจากคำไปยัง excel สมมติว่ามีไฟล์เอกสาร – คัดลอก ตารางจาก word เป็น excel.docx – มีตารางด้านล่าง

ถ้าเราคัดลอกตารางนี้ลงในเวิร์กชีตโดยใช้ CTRL+C และ CTRL+V เราจะได้ของตามที่แสดงในรูปที่ 10.1 คุณจะเห็นได้ว่า วันสุดท้ายของการได้รับแบบฟอร์มใบสมัคร แบ่งออกเป็นสองแถว นี่ไม่ใช่สิ่งที่เราต้องการอย่างแน่นอน

เพื่อป้องกันไม่ให้สิ่งนี้เกิดขึ้น เราสามารถใช้ มาโคร VBA เพื่อแยกตารางจากไฟล์ Word ลงในไฟล์ Excel รหัสในตารางต่อไปนี้สามารถดึงข้อมูลจากตารางทั้งหมดภายในเอกสาร word ไปเป็นไฟล์ Excel ได้

Sub Import_Table()
Dim wdDoc As Object
Dim wdFileName As Variant
Dim tableNo As Integer 'table number in Word
Dim iRow As Long 'row index in Excel
Dim iCol As Integer 'column index in Excel
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer
On Error Resume Next
ActiveSheet.Range("A:AZ").ClearContents
wdFileName = Application.GetOpenFilename("Word files (*.docx),*.docx", , _
"Browse for file containing table to be imported")
If wdFileName = False Then Exit Sub '(user canceled import file browser)
Set wdDoc = GetObject(wdFileName) 'open Word file
With wdDoc
tableNo = wdDoc.tables.Count
tableTot = wdDoc.tables.Count
If tableNo = 0 Then
MsgBox "This document contains no tables", _
vbExclamation, "Import Word Table"
ElseIf tableNo > 1 Then
tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
"Enter the table to start from", "Import Word Table", "1")
End If
resultRow = 1
For tableStart = 1 To tableTot
With .tables(tableStart)
'copy cell contents from Word table cells to Excel cells
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
ThisWorkbook.Worksheets("Import_Table").Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
Next iCol
resultRow = resultRow + 1
Next iRow
End With
resultRow = resultRow + 1
Next tableStart
End With
End Sub

หลังจากคลิกที่ปุ่มนำเข้าตาราง เราจะได้ตารางที่คล้ายกับในรูปที่ 10.2 ฉันเปลี่ยนความกว้างของคอลัมน์ที่นี่ด้วยตนเองเพื่อแสดงให้เห็นว่า “วันสุดท้ายสำหรับการรับการกรอกแบบฟอร์มใบสมัคร” ไม่ได้ถูกแบ่งออกเป็นสองแถว

11. ใช้เวลานานในบางครั้งขณะคำนวณสูตรใหม่

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

♦ ปิดการคำนวณใหม่อัตโนมัติ

ขั้นแรก ให้คลิกที่ ไฟล์ แท็บ -> ตัวเลือก .

ประการที่สอง คลิก สูตร หมวดหมู่.

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

รูปด้านล่างแสดงวิธีอื่นในการปิดการคำนวณใหม่อัตโนมัติ นอกจากนี้ยังมีสามตัวเลือกให้คุณเลือกอีกด้วย

• อัตโนมัติ ,
• อัตโนมัติ ยกเว้นตารางข้อมูล และ
• คู่มือ .

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

♦ เปลี่ยนจำนวนครั้งที่ Excel วนซ้ำสูตร

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

12. ไม่มีวิธีง่ายๆ ในการเปลี่ยนชื่อแผ่นงานหลายแผ่น

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

Sub rename_tab()
Application.DisplayAlerts = False
'Define variable
Dim wbk As Workbook
Dim ws As Worksheet
'Open workbookFile
Set fnm = ThisWorkbook.Sheets(1).Cells(1, 2)
Set wbk = Workbooks.Open(fnm)
'Set workbook as active workbook
wbk.Activate
For i = 2 To ThisWorkbook.Worksheets(2).UsedRange.Rows.Count
'Return number of all worksheet in the active workbook
n = Worksheets.Count
'Add worksheet after the last worksheet
Set ws = Sheets.Add(After:=Worksheets(Worksheets.Count))
'Give the newly added worksheet a name
ws.Name = ThisWorkbook.Worksheets(2).Cells(i, 2)
Next i
'Close and save active workbook
wbk.Close Savechanges:=True
End Sub

รูปภาพแสดงวิธีออกแบบและใช้มาโครนี้ สมมติว่ามาโครถูกบันทึกใน แทรกและเปลี่ยนชื่อหลายแท็บ.xlsm ไฟล์. มีสองแท็บใน xlsm . นี้ ไฟล์. อันแรกคือการรวมไฟล์ที่คุณต้องการแทรกแท็บ คุณต้องป้อนชื่อเส้นทางของไฟล์ลงในเซลล์ B1 . แมโครจะเปิดไฟล์ที่แสดงอยู่ในเซลล์ B1 (WS.xlsx ในกรณีของเรา) แผ่นงานที่สอง (แผงด้านขวาของรูป) มีชื่อแท็บที่จะใช้โดยมาโคร VBA . คุณสามารถเพิ่มชื่อแท็บลงในเวิร์กชีตที่สองได้มากเท่าที่ต้องการ

รูปด้านล่างแสดงภาพหน้าจอสำหรับ WS.xlsx ก่อนและหลังเรียกใช้ VBA . ด้านบน รหัส.

13. PivotTables ไม่มีค่ามัธยฐาน

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

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

ใน รายการฟิลด์ PivotTable ที่ปรากฏ กล่องโต้ตอบ (แผงด้านขวาของภาพด้านล่าง ) ลาก เพศ และ อายุ ไปที่ ป้ายกำกับแถว พื้นที่. แล้วลาก น้ำหนัก สู่ คุณค่า พื้นที่. สุดท้าย PivotTable รวมทั้งเซลล์จาก H5 ผ่าน I19 ถูกสร้างขึ้น

ในที่สุด จากตารางเดือย ผลรวมของน้ำหนักสำหรับ นักเรียนชาย ที่ 11 คือ 57.5 . และผลรวมน้ำหนักสำหรับ นักศึกษาหญิง ซึ่งเป็น 13 คือ 121.8 .
นอกจากนี้ สถิติผลรวม เรายังสามารถนับจำนวนนักเรียนที่อายุ 13 หรือคำนวณน้ำหนักเฉลี่ย คลิกขวาที่เซลล์ใดก็ได้ในตาราง Pivot (ช่วง H5:I19 ) จากนั้นเลือก การตั้งค่าฟิลด์ค่า . ใน ช่องค่า กล่องโต้ตอบการตั้งค่า (แผงด้านขวาของรูป ) คุณสามารถเลือกประเภทการคำนวณที่คุณสนใจได้
ที่สำคัญ ประเภทของการคำนวณประกอบด้วย จำนวน เฉลี่ย สูงสุด ต่ำสุด ฯลฯ  หากคุณอ่านการคำนวณทุกประเภท คุณจะสังเกตเห็นว่า EXCEL ไม่ได้ระบุค่ามัธยฐาน สถิติใน PivotTable . แล้วเราควรทำอย่างไรถ้าเราต้องการคำนวณ ค่ามัธยฐาน สถิติ? ค่ามัธยฐาน ถือเป็นตัวเลือกที่ดี

ก่อนใช้ฟังก์ชัน MEDIAN จะดีกว่าที่จะจัดเรียงข้อมูลตาม เพศ และ อายุ . ที่นี่เราได้จัดเรียงชุดข้อมูลตาม เพศ และ อายุ .

ตอนนี้ มาใช้ฟังก์ชัน MEDIAN เพื่อคำนวณ ค่ามัธยฐาน สำหรับแต่ละกลุ่มย่อย สูตรในเซลล์ J5 เป็นแบบนี้

=MEDIAN(E27:E35)

ที่นี่ E27:E35 หมายถึง น้ำหนัก ของ นักศึกษาหญิง .

ในที่สุด หลังจากกด ENTER มันคืนค่า 62.5 . มันบอกว่า น้ำหนักมัธยฐาน สำหรับfนักเรียนชาย ซึ่งมีอายุระหว่าง 11 และ 15 คือ 62.5 กก .

ในทำนองเดียวกันคุณต้องเขียนสูตรด้านล่างในกรณีของนักเรียนชาย

=MEDIAN(E14:E23)

ที่นี่ E14:E23 หมายถึง น้ำหนัก ของ นักเรียนชาย .

ซ้ำๆ หากคุณกด ENTER คุณจะพบผลลัพธ์เป็น 64.15 .

14. PivotTables ไม่สามารถนับค่าที่ไม่ซ้ำได้

บางครั้ง เราอาจต้องการหาจำนวนค่าที่ไม่ซ้ำกันในช่วงที่มีค่าที่ซ้ำกัน แต่ PivotTables ไม่นับค่าที่ไม่ซ้ำกัน รูปด้านล่างแสดงสิ่งนี้ คุณจะพบว่ามี 2 นักเรียนที่อายุ 11 อายุและ 5 นักเรียนที่อายุ 12 ปี. แต่ถ้าคุณต้องการทราบช่วงของการกระจายอายุ คุณต้องนับด้วยตนเอง
ก่อนอื่น คุณต้องสร้าง PivotTable . สมมติว่า PivotTable อยู่กับ ระดับแถว และ ผลรวมของอายุ .

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

=ROWS(H5:H10)

ที่นี่ H5:H10 หมายถึง ระดับแถว

สุดท้ายให้กด ENTER เพื่อให้ได้ผลลัพธ์เป็น 6 .

และที่นี่ รูปด้านล่างแสดงวิธีการนับค่าที่ไม่ซ้ำกันสำหรับกลุ่มเพศต่างๆ สิ่งที่เราทำที่นี่คือการใช้ฟังก์ชัน ROWS ให้กับแต่ละกลุ่มย่อย สำหรับ กลุ่มหญิง ให้ป้อนสูตรแบบนี้ลงในเซลล์ D5 .

=ROWS(B6:B10)

ในทำนองเดียวกัน กด ENTER ให้นับเป็น 5 .

และสำหรับ กลุ่มชาย ให้ป้อนสูตรลงใน D11 เซลล์แบบนี้

=ROWS(B12:B17)

ในที่สุดก็จะกลับมา 6 .

15. ไม่มีค่าเทียบเท่า SUMIF / COUNTIF / AVERAGEIF สำหรับฟังก์ชัน เช่น ค่าสูงสุดหรือค่ามัธยฐาน

ฟังก์ชัน SUMIFS สามารถรวมเซลล์ที่ตรงตามเกณฑ์ได้หลายข้อ ฟังก์ชัน SUMIF ใช้เกณฑ์ได้เพียงข้อเดียวในขณะที่ฟังก์ชัน SUMIFS สามารถใช้กับเกณฑ์มากกว่าหนึ่งชุดที่มีช่วงมากกว่าหนึ่งช่วง

Sum_range อาร์กิวเมนต์ในฟังก์ชันหมายถึงช่วงที่จะรวม เกณฑ์_ช่วง และ เกณฑ์ จัดให้เป็นคู่
โดยหลักแล้ว สูตรสำหรับ I5 เซลล์คือ.

=SUMIFS($E$5:$E$23,$C$5:$C$23,"F=")

ช่วงผลรวมสำหรับเซลล์ I5 คือ E5:E23 . เกณฑ์แรกคือ C5:C23,”=F” ในขณะที่เกณฑ์ที่สองคือ E5:D23,=11 . ผลรวมน้ำหนักนักเรียนหญิงที่มีอายุ 11 อายุ 51.3 กก .

โดยทั่วไป ฟังก์ชัน AVERAGEIFS มีรูปแบบคล้ายกับ SUMIFS . รูปด้านล่างแสดงวิธีใช้ฟังก์ชัน AVERAGEIFS .

นอกจากนี้ ฟังก์ชัน COUNTIFS แตกต่างจากฟังก์ชัน SUMIFS . เล็กน้อย และฟังก์ชัน AVERAGEIF .
ที่สำคัญ ไวยากรณ์ประกอบด้วย criteria_range คู่ที่แตกต่างกันเท่านั้น และเกณฑ์ . ช่วงเช่น sum_range หรือ ช่วงเฉลี่ย ควรถอดออก รูปด้านล่างแสดงวิธีใช้ฟังก์ชัน COUNTIF .

ในที่สุดฟังก์ชั่นเหล่านี้ก็มีประโยชน์ใช่ไหม? อย่างไรก็ตาม ไม่มีฟังก์ชันเทียบเท่าเช่น ฟังก์ชัน MEDIANIF .

16. ฟีเจอร์ที่พร้อมใช้งานใน Excel สำหรับ Windows ที่ไม่มีใน Excel สำหรับ Mac

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

• ปกติ
• เค้าโครงหน้าและ
• ตัวแบ่งหน้า

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

17. ไม่สามารถเข้าถึงตารางอย่างรวดเร็วในกล่องโต้ตอบตัวเลือกของ Excel

หากคุณเปิด ตัวเลือก Excel กล่องโต้ตอบ คุณจะพบว่ามี 10 แท็บ และแต่ละฉากก็เต็มไปด้วยการตั้งค่ามากมาย การค้นหาตำแหน่งเฉพาะอาจเสียเวลามาก

18. ขีดจำกัดข้อมูลจำเพาะของ Excel

เป็นที่ทราบกันดีว่า Excel มีข้อ จำกัด ตัวอย่างเช่น การที่สามารถเปิดเวิร์กบุ๊กได้หรือไม่นั้นจะถูกจำกัดด้วยหน่วยความจำที่พร้อมใช้งานและทรัพยากรระบบ สูงสุด จำนวน แถว คือ 1,048,576 และ สูงสุด จำนวน คอลัมน์ คือ 16, 384 . นี่คือบทสรุปของขีดจำกัดของ Excel ทั้งหมด
แต่ข้อจำกัดบางอย่างก็น่ารำคาญ ตัวอย่างเช่น สูงสุด ความแม่นยำของตัวเลขคือ 15 . หมายความว่าจำนวนหลักในเซลล์สามารถมีได้เพียง 15 . Excel จะแทนที่ตัวเลข (หลัง 15 ^th หลัก) ด้วยศูนย์ หากคุณป้อน 12345678901234567890 เข้าไปในเซลล์ คุณจะได้ 12345678901234500000 แทน

19. ไม่มีการตรวจสอบการสะกดอัตโนมัติใน Excel

Microsoft Word เสนอการตรวจสอบการสะกดคำอัตโนมัติซึ่งสามารถตรวจสอบการสะกดและไวยากรณ์ของไฟล์ของคุณได้ มันสามารถเน้นข้อผิดพลาด อย่างไรก็ตาม Excel จะไม่เน้นข้อผิดพลาดโดยอัตโนมัติ แต่การใช้ บทวิจารณ์> การพิสูจน์อักษร> การสะกดคำ คำสั่ง คุณสามารถตรวจสอบข้อผิดพลาดกับไฟล์ Excel ของคุณ

20. Excel บดขยี้อย่างง่ายดายเมื่อเรียกใช้มาโคร

บางทีคุณอาจมีประสบการณ์แบบเดียวกันกับที่ Excel เพิ่งหยุดทำงานเมื่อคุณเรียกใช้ มาโคร VBA โดยเฉพาะอย่างยิ่งเมื่อคุณใช้ DO WHILE วนซ้ำหรือ เพื่อถัดไป วนซ้ำที่ต้องวนซ้ำหลายครั้ง (เช่น 1000 หรือ 10,000 ครั้ง) ฉันเคยขูดข้อมูลจากอินเทอร์เน็ตเพื่อดึงชื่อและข้อมูลติดต่อมากกว่า 10,000 บริษัท. ฉันใช้เวลาทั้งหมดเกือบ 24 ชั่วโมงในการทำงานให้เสร็จ เริ่มแรก มันหยุดทำงานหลังจากเรียกใช้ มาโคร เป็นเวลา 1 หรือ 2 ชั่วโมงเนื่องจากสภาพอินเทอร์เน็ตไม่ดี จู่ๆงานทั้งหมดก็หายไป ฉันรู้สึกผิดหวังมากหลังจากที่มันพังครั้งที่สอง จู่ๆ ก็เกิดขึ้นกับฉันว่าฉันสามารถบันทึกเวิร์กบุ๊กได้เมื่อหนึ่งลูปเสร็จสิ้น After adding the one statement “ThisWorkbook.Save ” at the end of each loop, I have never lost any work due to excel crashing at the wrong time.

21. CSV File Related Excel Issue

Excel has CSV file format issues. Sometimes, users using Excel 2013 and 2016 with language variation try to open a CSV file generated from a US application by double clicking on it. But Excel doesn’t format the file and shows it in CSV mode (comma separate) by default. Excel employs the List Separator character (such as a comma, semicolon, etc.) for regions. The solution to this may be.

• Select a region that uses a comma as a list separator (i.e. USA, Canada, etc.)
• Open and Create the CSV file in Excel and save it as a worksheet.
• Return the regional settings back to the original state.

22. Excel Limitations for Data Analytics

While data analysis in Excel you will face difficulties because of the issues.

• Excel limits visibility when creating complex models.
• Excel limitations make collaboration more challenging
• Excel is inefficient in managing templates and data entry
• Limitations of Excel make keeping track of multiple spreadsheets challenging.

บทสรุป

The purpose of this article is to focus on some important limitations while working with Excel. There may be some other limitations too. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.

Read More

• Use of Offset Function in Excel [Offset – Match Combo, Dynamic Range]
• An Excel VBA function with one argument
• VLOOKUP Function in Excel:Learn with Examples
• How to Split Cells in Excel (The Ultimate Guide)