Machine Translated by Google Kinh thánh công thức EXCEL Excel 2013 / 2016 Machine Translated by Google
Mục lục Mục lục Nội dung
1. TỔNG các chữ số khi ô chứa tất cả các số.................................. ................................... 1 2. TỔNG của các chữ số khi ô chứa cả số và không phải
số........... .. 1 3. Danh sách có duy nhất hay không (dù có trùng lặp) .................... ................................... 1 4. Số lượng duy nhất Các giá
trị................................................................. ................................................................. ................... 1 5. Đếm số giá trị duy
nhất có điều kiện .... ................................................................. ...................... 1
6. Cộng tháng vào hoặc trừ tháng từ một ngày nhất định ...................................... ................................... 2
7. Cộng năm vào hoặc trừ năm từ một ngày nhất định ...................................... ................................... 2
8. Chuyển số thành tên tháng................................................................. ................................................................. ............ 3
9. Chuyển đổi ngày sang quý dương lịch.................................................. ................................................................. ........... 3 10. Ngày
chuyển đổi sang Quý Năm tài chính Ấn Độ .................... ................................... 3 11. Tính tuổi từ ngày sinh
nhật .. ................................................................. ................................................................. ... 4
12. Chuyển đổi định dạng số sang ngày tháng.................................................. ................................................................. ......... 4
13. Chuyển đổi định dạng số sang thời gian.................................................. ................................................................. ........ 4
14. Đếm các ô bắt đầu (hoặc kết thúc) bằng một chuỗi cụ thể ................................. ...................... 4 15. Đếm Số Ô Chỉ Có
Số ............ ................................................................. ...................... 5 16. Đếm Số ô chỉ chứa Ký
tự .......... ................................................................. 5 17. Số ký tự trong một chuỗi không kể khoảng trống ...................................... ...........
5 18. Số lần một ký tự xuất hiện trong chuỗi .................... ................................................................. 5 19. Đếm các số không phải số trong một
chuỗi.................................................. ................................................................. ...................... 5 20. Đếm các số trong một
chuỗi ...................... ................................................................. ................................................................. . 6 21. Chỉ đếm các
bảng chữ cái trong một chuỗi .................... ................................................................. ................... 6 22. Giá trị xuất hiện thường xuyên nhất
trong một phạm vi .................... ................................................................. ............ 6
23. COUNTIF trên danh sách đã lọc.................................................. ................................................................. ...................... 6
24. SUMIF trên danh sách đã lọc.................................................. ................................................................. ................................. 7
25. Trích xuất tên từ họ và tên.................................................. ................................................................. ............ 7
26. Trích xuất họ từ tên đầy đủ ................................................. ................................................................. ............ 7
27. Trích xuất chữ đầu của tên đệm ................................................. ................................................................. ............ 7
28. Trích xuất tên đệm từ tên đầy đủ .................................... ................................................................. ...... 7
29. Bỏ tên đệm trong tên đầy đủ ................................................. ................................................................. .......... 7
30. Trích xuất phần nguyên và phần thập phân của một số.................................. .................... 8 31. Ngày đầu tiên của tháng cho một ngày nhất
định .... ................................................................. ................................... 8 32. Có bao nhiêu thứ Hai hoặc bất kỳ ngày nào khác Ngày trong tuần
giữa 2 ngày .................................... 8 33. Thời gian tối đa cho một mục nhập cụ thể Xuất hiện liên tiếp.................................................................. ......
9 34. Tìm tuần tiếp theo trong ngày .................... ................................................................. ...................... 9 35. Tìm Tuần Trước Trong
Ngày .......... ................................................................. ................................... 10 Machine Translated by Google
36. Lấy tên le thông qua công thức.................................................. ................................................................. ............
10 37. Lấy tên sổ làm việc thông qua công thức .................... ................................................................. ............ 11 38. Lấy tên
trang tính thông qua công thức .................... ................................................................. .................... 11 39. Lấy thư mục
Workbook từ công thức............ ................................................................. ...................... 11 40. Ngày cuối cùng của tháng cho một
ngày nhất định ............ ................................................................. ................... 11
41. Thực hiện VLOOKUP nhiều cột .................................... ................................................................. .......... 12
42. VLOOKUP từ phải sang trái................................................................. ................................................................. ...................... 13
43. VLOOKUP phân biệt chữ hoa chữ thường .................................... ................................................................. ...................... 13
44. Xếp hạng trong Nhóm .................................................... ................................................................. ....................
14 45. Xóa bảng chữ cái khỏi chuỗi .......... ................................................................. .................................... 14 46. Xóa số
từ chuỗi................................................................................. ................................................................. ............
15 47. Biểu diễn số La Mã .................... ................................................................. ...................... 15 48. Tổng các giá trị N
dưới cùng trong một phạm vi ....... ................................................................. ................................... 15 49. Tính tổng mọi hàng
thứ N ...... ................................................................. ................................................................. ................... 16
50. Chúng ta có AVERAGEIF. Còn MEDIANIF và MODEIF thì sao? ................................... 16
51. Số ngày trong một tháng.................................................. ................................................................. ......................
17 52. Làm sao để biết một năm là năm nhuận ............ ................................................................. .................... 17 53. Ngày làm việc
cuối cùng của tháng nếu có ngày . ................................................................. .................... 17 54. Ngày làm việc đầu tiên trong tháng
nếu có ngày .................... ................................................................. ...... 18 55. Ngày thứ N trong
năm .................... ................................................................. .................... 18 56. Tính trung bình hình học bằng cách bỏ qua giá
trị 0 và giá trị âm ....... ................................... 19
57. Chức năng tài chính - Tính toán EMI ................................................. ................................................................. .......... 19
58. Chức năng tài chính - Tính lãi Phần của EMI ................................................. ................... 20
59. Chức năng tài chính - Tính toán phần gốc của EMI .................................... ...................... 22 60. Chức năng tài chính - Tính toán số lượng
EMI để trả khoản vay ....... ................... 23
61. Chức năng tài chính - Tính lãi suất................................................................. ................................... 24
62. Chức năng tài chính – Tính lãi gộp ...................................... ...................... 25
63. Chức năng tài chính – Tính lãi thực tế ................................................. ................... 26
64. Viết tắt tên riêng ................................................................. ................................................................. ................... 27
65. Lấy tên cột cho số cột .................................................... ................................... 28
66. Lấy phạm vi cột cho số cột .................................................... ................................... 29 67 Tìm số lớn thứ n khi có số trùng
nhau................................................. .... 29 68. Trích xuất Ngày và Giờ từ Dấu thời gian
Ngày ....... ................................................................. ....... 30
69. Chuyển đổi một số thành năm và tháng .................................................... ................................... 30
70. COUNTIF cho phạm vi không liền kề .................................... ................................................................. ............ 31 71. Đếm
Số Từ trong Một Ô/Phạm vi .................... ................................................................. 31 72. Số học Tổng của các chữ số hay còn gọi là
Tổng các chữ số cho đến khi kết quả là một chữ số duy nhất .......... 32 73. Tạo các số liên tiếp và lặp lại
chúng ............ ................................................................. ............ 32 Machine Translated by Google
74. Lặp lại một số và tăng dần và lặp lại....................................... ...................................... 32 75. Tạo số ngẫu nhiên không lặp lại thông qua công
thức . .................................... 33 76. Năm tài chính Công thức (ví dụ 2015-16 hoặc FY16) ...................... .................... 34 77. Ngày làm việc đầu
năm ....... ................................................................. ................................................................. ...... 34 78. Ngày làm việc cuối cùng trong
năm .................... ................................................................. ...................... 34 79. Chuyển đổi từ Ngày Excel (Ngày Gregory) sang Ngày
Julian ........ ................................... 35 80 . Chuyển đổi từ Ngày Julian sang Ngày Excel (Gregorian) ...................... ................... 35
81. Trích xuất tên người dùng từ ID E-mail ................................................. ................................................................. ...... 36
82. Trích xuất tên miền từ ID E-mail ................................................. ................................................................. 36
83. Vị trí của số đầu tiên trong chuỗi.................................................. ................................................................. ............ 36 84. Vị trí của số
cuối cùng trong chuỗi .................... ................................................................. ................... 36 85. Tìm giá trị của ô không trống đầu tiên trong một phạm
vi........... ................................................................. ...... 36 86. Tìm giá trị số đầu tiên trong một phạm
vi .................... ................................................................. ............ 36 87. Tìm giá trị số cuối cùng trong một phạm
vi .................... ................................................................. ................... 36 88. Tìm giá trị không phải số đầu tiên trong một phạm
vi ....... ................................................................. ................... 37 89. Tìm giá trị không phải số cuối cùng trong một phạm
vi ....... ................................................................. ...................... 37 90. Tìm giá trị được sử dụng cuối cùng trong một phạm
vi ....... ................................................................. ................................... 37
91. TỐI ĐA ...................................... ................................................................. ................................................................. ...... 37 92.
TỐI THIỂU .................................... ................................................................. ................................................................. ............ 37
93. Tạo một danh sách duy nhất từ các mục trùng lặp .................................... ................................... 38 Machine Translated by Google Kinh thánh công thức Excel
1. TỔNG các chữ số khi ô chứa tất cả các số
Nếu ô của bạn chỉ chứa các số như A1:= 7654045 thì có thể sử dụng công thức sau để tìm tổng các chữ số
=SUMPRODVEL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
2. TỔNG các chữ số khi ô chứa số và không chứa số Cả hai con số
Nếu ô của bạn chứa các số không phải số ngoài các số như A1:= 76$5a4b045%d thì có thể sử dụng
công thức sau để tìm tổng các chữ số
=TỔNG HỢP((LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:9),")))*ROW(1:9))
Công thức trên có thể được sử dụng ngay cả khi có chứa tất cả các số.
3. Danh sách có duy nhất hay không (Cho dù nó có trùng lặp)
Giả sử danh sách của bạn nằm trong khoảng từ A1 đến A1000. Sử dụng công thức sau để biết danh sách có phải là duy nhất hay không.
=MAX(Tần số(A1:A1000,A1:A1000))
=MAX(INDEX(COUNTIF(A1:A1000,A1:A1000),,))
Nếu câu trả lời là 1 thì nó là Duy nhất. Nếu câu trả lời lớn hơn 1 thì nó không phải là duy nhất.
4. Đếm số giá trị duy nhất
Sử dụng công thức sau để đếm không. có giá trị duy nhất -
=SUMPRODV((A1:A100<>"")/COUNTIF(A1:A100,A1:A100&""))
5. Đếm số giá trị duy nhất có điều kiện
Nếu bạn có dữ liệu như bên dưới và muốn tìm số lượng duy nhất cho Vùng = “A”, thì bạn có thể sử
dụng công thức Mảng bên dưới –
=SUM(IF(FREQUENCY(IF(A2:A20<>"",IF(A2:A20="A",MATCH(B2:B20,B2:B20,0))),ROW(A 2:A20)-ROW(A2)+1),1))
Nếu bạn có nhiều điều kiện hơn, điều tương tự có thể được xây dựng sau A2:A20 = “A”.
Lưu ý - Công thức mảng không được nhập bằng cách nhấn ENTER sau khi nhập công thức của bạn mà bằng
cách nhấn CTRL+SHIFT+ENTER. Nếu bạn đang sao chép và dán công thức này, hãy nhấn F2 sau khi dán và
CTRL+SHIFT+ENTER. Điều này sẽ đặt dấu ngoặc { } xung quanh công thức mà bạn có thể thấy trong Thanh
Công thức. Nếu bạn chỉnh sửa lại, bạn sẽ phải thực hiện lại CTRL+SHIFT+ENTER.
Đừng đặt { } theo cách thủ công. © eforexcel.com Trang 1 trên 38 Machine Translated by Google Kinh thánh công thức Excel
6. Thêm tháng vào hoặc trừ tháng từ một ngày nhất định
Rất thường xuyên, bạn sẽ gặp vấn đề trong kinh doanh khi phải cộng hoặc trừ tháng kể từ
một ngày nhất định. Một kịch bản là tính toán cho Ngày EMI.
Giả sử bạn có ngày 22/10/14 (MM/DD/YY) trong A1 và bạn muốn thêm số lượng
tháng được chứa trong ô B1.
Công thức trong trường hợp này sẽ là =EDATE(A1,B1)
[Công thức phụ =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) ]
Bây giờ, bạn muốn trừ tháng có trong Ô B1. =EDATE(A1,-B1)
[Công thức phụ =DATE(YEAR(A1),MONTH(A1)-B1,DAY(A1)) ]
7. Thêm năm vào hoặc trừ năm từ một ngày nhất định
Trong nhiều bài toán kinh doanh, bạn có thể gặp phải tình huống cần cộng hoặc trừ số năm kể
từ một ngày nhất định.
Giả sử A1 chứa Ngày và B1 chứa số năm.
Nếu bạn muốn thêm Năm vào một ngày nhất định, công thức sẽ là - =EDATE(A1,12*B1)
=NGÀY(NĂM(A1)+B1,THÁNG(A1),NGÀY(A1)) © eforexcel.com Trang 2 trên 38 Machine Translated by Google Kinh thánh công thức Excel
Nếu bạn muốn trừ Năm cho một ngày nhất định, công thức sẽ là - =EDATE(A1,-12*B1)
=DATE(YEAR(A1)-B1,MONTH(A1),DAY(A1))
8. Chuyển số thành tên tháng Sử dụng công thức bên
dưới để tạo tháng có 3 chữ cái được đặt tên như Tháng 1, Tháng 2.... Tháng 12 =TEXT(A1*30,"mmm")
Thay thế "mmm" bằng "mmmm" để tạo tên đầy đủ của tháng như Tháng Một, Tháng Hai....Tháng
Mười Hai trong bất kỳ công thức nào trong bài viết này.
9. Chuyển đổi ngày thành quý theo lịch
Giả sử ngày ở ô A1. Bạn muốn chuyển đổi nó thành một phần tư (1, 2, 3 & 4). Tháng 1 đến tháng 3 là 1, tháng 4 đến
tháng 6 là 2, tháng 7 đến tháng 9 là 3 và tháng 10 đến tháng 12 là 4. =Trần(THÁNG(A1)/3,1) HOẶC = ROUNDUP(THÁNG(A1)/3,0) HOẶC
=CHỌN(THÁNG(A1),1,1,1,2,2,2,3,3,3, 4,4,4)
10. Ngày chuyển đổi sang quý của năm tài chính Ấn Độ Giả sử ngày ở ô A1. Bạn muốn chuyển nó thành Quý Năm tài chính của Ấn Độ. Tháng 1 đến tháng 3 là 4, tháng 4 đến tháng 6 là 1, tháng 7 đến tháng 9 là 2 và tháng 10 đến tháng 12 là 3.
=Trần(THÁNG(A1)/3,1)+IF(THÁNG(A1)<=3,3,-1) HOẶC
=ROUNDUP(THÁNG(A1)/3,0)+IF(THÁNG(A1)<=3,3,-1) HOẶC
=CHỌN(THÁNG(A1),4,4,4,1,1,1,2,2,2,3,3,3) © eforexcel.com Trang 3 trên 38 Machine Translated by Google Kinh thánh công thức Excel
11. Tính tuổi từ ngày sinh nhật
=DATEDIF(A1,TODAY(),"y")&" Năm "&DATEDIF(A1,TODAY(),"ym")&" Tháng
"&DATEDIF(A1,TODAY(),"md")&" Ngày"
12. Chuyển đổi định dạng số sang ngày
Nếu bạn có các số như 010216 và bạn muốn chuyển đổi số này sang định dạng ngày thì có thể sử dụng công thức sau
=--TEXT(A1,"00\/00\/00") cho năm có 2 chữ số
Lưu ý - Cần tối thiểu 5 chữ số để công thức trên hoạt động
Nếu bạn có các số như 01022016 và bạn muốn chuyển đổi số này sang định dạng ngày thì có thể sử dụng công thức sau
=--TEXT(A1,"00\/00\/0000") cho năm có 4 chữ số
Lưu ý - Cần tối thiểu 7 chữ số để công thức trên hoạt động
13. Chuyển đổi định dạng số sang thời gian
Nếu bạn có các số như 1215 và muốn chuyển đổi số này sang định dạng hh:mm thì có thể sử dụng công thức sau =--TEXT(A1,"00\:00")
Lưu ý - Cần tối thiểu 3 chữ số để công thức trên hoạt động
Để chuyển đổi sang định dạng hh:mm:ss =--TEXT(A1,"00\:00\:00")
Lưu ý - Cần tối thiểu 5 chữ số để công thức trên hoạt động
14. Đếm các ô bắt đầu (hoặc kết thúc) bằng một ô cụ thể Sợi dây
1. Giả sử bạn muốn đếm tất cả các ô bắt đầu bằng C =COUNTIF(A1:A10,"c*") c* không phân biệt chữ
hoa chữ thường. Do đó, nó sẽ đếm
các ô bắt đầu bằng cả c hoặc C.
Giả sử bạn muốn tìm tất cả các ô bắt đầu bằng Excel. =COUNTIF(A1:A10,"excel*") © eforexcel.com Trang 4 trên 38 Machine Translated by Google Kinh thánh công thức Excel 2. Để kết thúc =COUNTIF(A1:A10,"*c") c* không phân biệt chữ
hoa chữ thường. Do đó, nó sẽ đếm
các ô bắt đầu bằng cả c hoặc C.
Giả sử bạn muốn tìm tất cả các ô bắt đầu bằng Excel. =COUNTIF(A1:A10,"*excel")
15. Đếm số ô chỉ có số
Hàm COUNT chỉ đếm những ô có số.
Giả sử phạm vi của bạn là A1:A10, hãy sử dụng công thức sau =COUNT(A1:A10)
16. Đếm số ô chỉ chứa Nhân vật
Do đó, nếu ô của bạn có số 2,23 thì nó sẽ không được tính vì đây là một số.
Sử dụng công thức bên dưới xem xét phạm vi của bạn là A1:A10 =COUNTIF(A1:A10,"*")
17. Số ký tự trong chuỗi không tính khoảng trống
Giả sử bạn có một chuỗi như Vijay A. Verma và tôi cần biết nó có bao nhiêu ký tự.
Trong trường hợp này, nó có 12 bao gồm số thập phân và để trống. Sử dụng công thức dưới đây cho cùng - =LEN(ĐĂNG KÝ(A1," ",""))
18. Số lần một ký tự xuất hiện trong chuỗi
Giả sử bạn muốn đếm số lần, ký tự “a” xuất hiện trong một chuỗi
=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"a",""))
19. Đếm các số không trong chuỗi
Giả sử bạn có một chuỗi "abc123def45cd" và bạn muốn đếm các số không có trong chuỗi này.
Nếu chuỗi của bạn ở A1, hãy sử dụng công thức sau trong A1 © eforexcel.com Trang 5 trên 38 Machine Translated by Google Kinh thánh công thức Excel
=IF(LEN(TRIM(A1))=0,0,SUMPRODVEL(--NOT(ISNUMBER((--
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))))))
20. Đếm số trong một chuỗi Giả sử bạn có một
chuỗi "abc123def43cd" và bạn muốn đếm số trong chuỗi này. Nếu chuỗi của bạn ở
dạng A1, hãy sử dụng công thức sau -
=SUMPRODVEL(LEN(A1)-LEN(SUBSTITUTE(A1,ROW(1:10)-1,""))) HOẶC
=SUMPRODVEL(--ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))
21. Chỉ đếm các bảng chữ cái trong một chuỗi Giả sử bạn có một
chuỗi "Ab?gh123def%h*" và bạn chỉ muốn đếm Aphabets.
Giả sử chuỗi của bạn
ở dạng A1, hãy đặt công thức sau cho việc này. =TỔNG HỢP(LEN(A1)-
LEN(SUBSTITUTE(UPPER(A1),CHAR(ROW(INDIRECT("65:90"))),""))) HOẶC =TỔNG HỢP(--(ABS(77.5-
CODE(MID(UPPER(A1),ROW(INDIRECT("A1:A"&LEN(A1))),1)))<13))
22. Giá trị xuất hiện thường xuyên nhất trong một phạm vi Giả sử phạm vi của
bạn là A1:A10, hãy nhập công thức bên dưới dưới dạng Công
thức mảng, tức là không
phải bằng cách nhấn ENTER
sau khi nhập công thức mà bằng cách nhấn CTRL+SHIFT+ENTER. Điều này sẽ đặt dấu ngoặc { } xung quanh
công thức mà bạn có thể thấy trong Thanh Công thức. Nếu bạn chỉnh sửa lại,
bạn sẽ phải thực hiện
lại CTRL+SHIFT+ENTER. Đừng đặt { } theo cách thủ công.
=INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0))
Phiên bản không phải mảng của công thức trên
=INDEX(A1:A10,MATCH(MAX(INDEX(COUNTIF(A1:A10,A1:A10),,)),INDEX(COUNTIF(A1: A10,A1:A10),,),0))
23. COUNTIF trên danh sách đã lọc Bạn có thể sử
dụng SUBTOTAL để thực hiện COUNT trên danh
sách đã lọc nhưng không thể thực hiện COUNTIF trên danh sách đã lọc.
Công thức dưới đây có thể được sử
dụng để thực hiện COUNTIF trên danh sách được lọc
=SUMproduct(SUBTOTAL(3,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14)) © eforexcel.com Trang 6 trên 38 Machine Translated by Google Kinh thánh công thức Excel
Ở đây B2:B20>14 giống như một tiêu chí trong COUNTIF (=COUNTIF(B2:B20,">14"))
24. SUMIF trên danh sách đã lọc
Bạn có thể sử dụng SUBTOTAL để thực hiện SUM trên danh sách đã lọc nhưng không thể thực hiện SUMIF trên danh
sách đã lọc. Công thức dưới đây có thể được sử dụng để thực hiện SUMIF trên danh sách được lọc
=SUMproduct(SUBTOTAL(9,OFFSET(B2,ROW(B2:B20)-ROW(B2),))*(B2:B20>14))
Ở đây B2:B20>14 giống như một tiêu chí trong SUMIF.
25. Trích xuất tên từ tên đầy đủ
=LEFT(A1,TÌM(" ",A1&" ")-1)
26. Trích xuất họ từ tên đầy đủ
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)))
27. Trích xuất tên viết tắt của tên đệm
Giả sử bạn có tên John Doe Smith và bạn muốn hiển thị D là tên viết tắt ở giữa.
Giả sử dữ liệu của bạn nằm ở A1, bạn có thể sử dụng công thức sau
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,1)"")
Nếu tên có 2 hoặc 1 từ thì kết quả sẽ trống. Điều này chỉ hoạt động trên tên 3 từ vì phần
giữa chỉ có thể được quyết định cho tên 3 từ.
28. Trích xuất tên đệm từ tên đầy đủ
=IF(COUNTIF(A1,"* * *"),MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-(FIND(" ",A1)+1)),"")
=IF(COUNTIF(A1,"* * *"),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1)),2),FIND(" ",A1)+1,LEN(A1))),"")
=IF(COUNTIF(A1,"* * *"),LEFT(REPLACE(A1,1,FIND(" ",A1),""),FIND("
",REPLACE(A1,1,TÌM(" ",A1),""))-1))
29. Xóa tên đệm trong tên đầy đủ
=IF(COUNTIF(A1,"* * *"),LEFT(A1,FIND(" ",A1&" "))&TRIM(RIGHT(SUBSTITUTE(A1,"
",REPT(" ",LEN(A1))),LEN(A1))),"")
=IF(COUNTIF(A1,"* * *"),REPLACE(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1),""),"") © eforexcel.com Trang 7 trên 38 Machine Translated by Google Kinh thánh công thức Excel
30. Trích xuất phần nguyên và phần thập phân của một số
Để trích xuất phần Số nguyên, có thể sử dụng một trong các cách dưới đây - =INT(A1) =TRUNC(A1)
Giá trị dương trong A1 - Nếu A1 chứa 84,65 thì đáp án sẽ là 84.
Giá trị âm trong A1 - Nếu A1 chứa -24,39 thì đáp án sẽ là -24.
Nếu bạn chỉ muốn giá trị +ve cho dù giá trị trong A1 là -ve hay +ve thì công thức có thể có nhiều biến thể.
=INT(A1)*SIGN(A1) HOẶC =TRUNC(A1)*SIGN(A1)
=INT(ABS(A1)) HOẶC =TRUNC(ABS(A1))
=ABS(INT(A1)) HOẶC = ABS(TRUNC(A1))
Để trích xuất phần thập phân - =MOD(ABS(A1),1) =ABS(A1)-INT(ABS(A1))
Giá trị dương trong A1 - Nếu A1 chứa 84,65 thì đáp án sẽ là 0,65.
Giá trị âm trong A1 - Nếu A1 chứa -24,39 thì câu trả lời sẽ là 0,39.
31. Ngày đầu tiên của tháng cho một ngày nhất định
Giả sử bạn đã được cung cấp một ngày chẳng hạn như 22/10/14 (MM/DD/YY) và bạn muốn tính ngày
đầu tiên của Tháng Hiện tại. Do đó, bạn muốn đạt được kết quả là 1/10/2014 (MM/DD/YY).
Các công thức được sử dụng - =DATE(YEAR(A1),THÁNG(A1),1) =A1-NGÀY(A1)+1 =EOMONTH(A1,-1)+1
32. Có bao nhiêu thứ Hai hoặc bất kỳ ngày nào khác trong tuần giữa 2 ngày
Giả sử A1 = 23-Jan-16 và A2 = 10-11-16. Để tìm số thứ Hai giữa hai ngày này © eforexcel.com Trang 8 trên 38 Machine Translated by Google Kinh thánh công thức Excel
=SUMPRODVEL(--(TEXT(ROW(INDIRECT(A1& John&A2)),"ddd")="Mon"))
“Thứ Hai” có thể được thay thế bằng bất kỳ ngày nào khác trong tuần tùy theo nhu cầu.
33. Thời gian tối đa một mục nhập cụ thể xuất hiện Liên tiếp
Giả sử chúng ta muốn đếm số lần tối đa “A” xuất hiện liên tiếp, bạn có thể sử dụng công thức Mảng sau -
=MAX(TẦN SỐ(IF(A2:A20="A",ROW(A2:A20)),IF(A2:A20<>"A",ROW(A2:A20))))
Lưu ý - Công thức mảng không được nhập bằng cách nhấn ENTER sau khi nhập công thức của bạn mà bằng cách
nhấn CTRL+SHIFT+ENTER. Nếu bạn đang sao chép và dán công thức này, hãy nhấn F2 sau khi dán và
CTRL+SHIFT+ENTER. Điều này sẽ đặt dấu ngoặc { } xung quanh công thức mà bạn có thể thấy trong Thanh Công
thức. Nếu bạn chỉnh sửa lại, bạn sẽ phải thực hiện lại CTRL+SHIFT+ENTER.
Đừng đặt { } theo cách thủ công.
34. Tìm tuần tiếp theo trong ngày
Có 2 kịch bản trong trường hợp này. Ví dụ: nếu ngày hôm nay là ngày 2 tháng 1 năm 17 (Thứ Hai) và tôi cố gắng tìm
Thứ Hai tiếp theo, tôi có thể nhận được ngày 17 tháng 1 hoặc ngày 9 tháng 1 năm 17 tùy theo nhu cầu. Đối với Thứ Ba
đến Chủ Nhật, đó không phải là vấn đề vì chúng chỉ đến sau ngày 2 tháng 1 năm 2017.
Trường hợp 1 - Nếu Ngày rơi vào cùng một ngày thì chính ngày đó (Do đó, trong trường hợp ngày 17 tháng 1,
thứ Hai tuần sau sẽ chỉ là ngày 2 tháng 1 năm 17) Thứ Hai tới =Trần($A$1-2,7)+2 Thứ ba tới =Trần($A$1-3,7)+3 Thứ Tư tới =Trần($A$1-4,7)+4 © eforexcel.com Trang 9 trên 38 Machine Translated by Google Kinh thánh công thức Excel Thứ năm tiếp theo =Trần($A$1-5,7)+5 Thứ Sáu tới =Trần($A$1-6,7)+6 Thứ bảy tiếp theo =Trần($A$1-7,7)+7 Chủ nhật tiếp theo =Trần($A$1-8,7)+8
Trường hợp 2 - Nếu Ngày rơi vào cùng ngày thì vào ngày tiếp theo (Do đó, trong trường hợp ngày 2-1-17, ngày tiếp theo
Thứ Hai sẽ chỉ là ngày 9-17-1) Thứ Hai tới =Trần($A$1-1,7)+2 Thứ ba tới =Trần($A$1-2,7)+3 Thứ Tư tới =Trần($A$1-3,7)+4 Thứ năm tiếp theo =Trần($A$1-4,7)+5 Thứ Sáu tới =Trần($A$1-5,7)+6 Thứ bảy tiếp theo =Trần($A$1-6,7)+7 Chủ nhật tiếp theo =Trần($A$1-7,7)+8
35. Tìm tuần trước trong ngày
Có 2 kịch bản trong trường hợp này. Ví dụ: nếu ngày hôm nay là ngày 2 tháng 1 năm 17 (Thứ Hai) và tôi cố gắng tìm
ngày Thứ Hai trước đó, tôi có thể nhận được ngày 2 tháng 1 năm 17 hoặc ngày 26 tháng 12 năm 16 tùy theo nhu cầu.
Đối với Thứ Ba đến Chủ Nhật, đó không phải là vấn đề vì họ chỉ đến trước ngày 2 tháng 1 năm 2017.
Trường hợp 1 - Nếu Ngày rơi vào cùng một ngày thì chính ngày đó (Do đó, trong trường hợp ngày 17 tháng
1, thứ Hai trước đó sẽ chỉ là ngày 2 tháng 1 năm 17) Thứ Hai trước đó =Trần($A$1-8,7)+2 Thứ Ba trước đó =Trần($A$1-9,7)+3 Thứ Tư trước đó =Trần($A$1-10,7)+4 Thứ năm trước đó =Trần($A$1-11,7)+5 Thứ Sáu trước đó =Trần($A$1-12,7)+6 Thứ bảy trước đó =Trần($A$1-13,7)+7 Mặt trời trước đó =Trần($A$1-14,7)+8
Trường hợp 2 - Nếu Ngày rơi vào cùng ngày thì ngày trước đó (Do đó, trong trường hợp ngày 17 tháng 1,
thứ Hai trước đó sẽ chỉ là ngày 26 tháng 12 năm 16) Thứ Hai trước đó =Trần($A$1-9,7)+2 Thứ Ba trước đó =Trần($A$1-10,7)+3 Thứ Tư trước đó =Trần($A$1-11,7)+4 Thứ năm trước đó =Trần($A$1-12,7)+5 Thứ Sáu trước đó =Trần($A$1-13,7)+6 Thứ bảy trước đó =Trần($A$1-14,7)+7 Mặt trời trước đó =Trần($A$1-15,7)+8
36. Lấy tên le thông qua công thức
Trước khi nhận được điều này, hãy đảm bảo rằng tệp của bạn đã được lưu ít nhất một lần vì công thức này phụ thuộc
vào tên đường dẫn tệp mà chỉ có thể được lấy ra bằng hàm CELL nếu tệp đã được lưu ít nhất một lần. © eforexcel.com Trang 10 trên 38 Machine Translated by Google Kinh thánh công thức Excel =CELL("tên tệp",$A$1)
37. Lấy tên sổ làm việc thông qua công thức
Trước khi nhận được điều này, hãy đảm bảo rằng tệp của bạn đã được lưu ít nhất một lần vì công thức này phụ thuộc
vào tên đường dẫn tệp mà chỉ có thể được lấy ra bằng hàm CELL nếu tệp đã được lưu ít nhất một lần.
=REPLACE(LEFT(CELL(" lename",$A$1),FIND("]",CELL(" lename",$A$1))-
1),1,TÌM("[",CELL("tên tệp",$A$1)),"")
38. Lấy tên trang tính thông qua công thức
Trước khi nhận được điều này, hãy đảm bảo rằng tệp của bạn đã được lưu ít nhất một lần vì công thức này phụ thuộc
vào tên đường dẫn tệp mà chỉ có thể được lấy ra bằng hàm CELL nếu tệp đã được lưu ít nhất một lần. Sử dụng công thức sau -
=REPLACE(CELL("tên tệp",A1),1,TÌM("]",CELL("tên tệp",A1)),"")
Đảm bảo rằng A1 được sử dụng trong công thức. Nếu nó không được sử dụng, nó sẽ trích xuất tên trang tính cho trang tính hoạt
động cuối cùng và có thể không phải là tên trang tính mà chúng ta mong muốn.
Nếu bạn chỉ muốn tên trang tính cho trang hoạt động cuối cùng thì công thức sẽ trở thành
=REPLACE(CELL("tên tệp"),1,TÌM("]",CELL("tên tệp")),"")
39. Lấy thư mục của Workbook từ Công thức
Trước khi nhận được điều này, hãy đảm bảo rằng tệp của bạn đã được lưu ít nhất một lần vì công thức này phụ thuộc
vào tên đường dẫn tệp mà chỉ có thể được lấy ra bằng hàm CELL nếu tệp đã được lưu ít nhất một lần.
Nếu sổ làm việc của bạn nằm ở C:\Excel\MyDocs, công thức truy xuất thư mục cho việc này sẽ là
=LEFT(CELL("tên tệp",A1),TÌM("[",CELL("tên tệp",A1))-2)
40. Ngày cuối cùng của tháng cho một ngày nhất định
Giả sử, bạn được cung cấp một ngày chẳng hạn như 22/10/14 (MM/DD/YY) và chúng tôi muốn có ngày cuối cùng của tháng
cho ngày đã cho. Do đó, bạn cần câu trả lời là 31/10/14. Các công thức được sử dụng trong trường hợp này - =EOMONTH(A1,0) =DATE(YEAR(A1),THÁNG(A1)+1,0) © eforexcel.com Trang 11 trên 38 Machine Translated by Google Kinh thánh công thức Excel
=DATE(YEAR(A1),THÁNG(A1)+1,1)-1
41. Thực hiện VLOOKUP nhiều cột
Bạn biết đến hàm VLOOKUP, một trong những hàm được yêu thích nhất của Excel. Cú pháp là
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Ở đây look_value có thể là một giá trị chứ không phải nhiều giá trị.
Bây giờ, bạn đang gặp tình huống muốn thực hiện vlookup với nhiều hơn 1 giá trị. Với mục đích
minh họa khái niệm, giả sử chúng ta có 2 giá trị cần tra cứu.
Dưới đây là bảng tra cứu của bạn và bạn muốn tra cứu Emp - H và Giới tính - F cho Tuổi.
=INDEX(C2:C12,MATCH(1,INDEX(--((A2:A12=F2)*(B2:B12=G2)*(ROW(A2:A12)- HÀNG(A2)+1)<>0),,),0)) Phương pháp nối
=INDEX(C2:C10,MATCH(F2&"@@@"&G2,INDEX(A2:A10&"@@@"&B2:B10,,),0)) @@@ có thể được thay
thế bằng bất kỳ ký tự nào không nên có một phần của các cột đó.
Bằng cách nối, bạn có thể có nhiều cột nhất có thể.
THẬN TRỌNG - Kết quả của toàn bộ phép nối không được có độ dài quá 255.
Do đó, F2&"@@@"&G2 không được có nhiều hơn 255 ký tự.
Một cách khác là sử dụng công thức Mảng bên dưới -
=INDEX(C2:C12,MATCH(1,--NOT(ISLOGICAL(IF(A2:A12=F2,IF(B2:B12=G2,C2:C12)))),0))
Lưu ý - Công thức mảng không được nhập bằng cách nhấn ENTER sau khi nhập công thức của bạn mà
bằng cách nhấn CTRL+SHIFT+ENTER. Nếu bạn đang sao chép và dán công thức này, hãy nhấn F2 sau
khi dán và CTRL+SHIFT+ENTER. Điều này sẽ đặt dấu ngoặc { } xung quanh công thức mà bạn có thể
thấy trong Thanh Công thức. Nếu bạn chỉnh sửa lại, bạn sẽ phải thực hiện lại CTRL+SHIFT+ENTER.
Đừng đặt { } theo cách thủ công. © eforexcel.com Trang 12 trên 38 Machine Translated by Google Kinh thánh công thức Excel
42. VLOOKUP từ phải sang trái
VLOOKUP luôn tra cứu từ Trái sang Phải. Do đó, trong bảng bên dưới, tôi có thể tìm Ngày sinh của Naomi
bằng cách đưa ra công thức sau - =VLOOKUP("Naomi",B:D,3,0)
Tuy nhiên, nếu tôi phải tìm Emp ID tương ứng với Naomi thì tôi không thể thực hiện được bằng công thức
VLOOKUP. Để thực hiện VLOOKUP từ Phải sang Trái, bạn sẽ phải sử dụng kết hợp INDEX/MATCH. Do đó, bạn sẽ
phải sử dụng công thức sau -
=INDEX(A:A,MATCH("Naomi",B:B,0))
43. VLOOKUP phân biệt chữ hoa chữ thường
Giả sử bạn có dữ liệu như bảng bên dưới và bạn muốn thực hiện VLOOKUP phân biệt chữ hoa chữ thường
Nếu thực hiện VLOOKUP thông thường trên SARA, tôi sẽ nhận được câu trả lời là 4300. Nhưng trong VLOOKUP phân biệt chữ
hoa chữ thường, câu trả lời phải là 3200. Bạn có thể sử dụng công thức bên dưới cho VLOOKUP phân biệt chữ hoa chữ thường - © eforexcel.com Trang 13 trên 38 Machine Translated by Google Kinh thánh công thức Excel
=INDEX(B2:B10,MATCH(TRUE,INDEX(EXACT("SARA",A2:A10),,),FALSE)) 44. Xếp hạng trong Nhóm
Giả sử bạn có dữ liệu như bảng dưới đây và bạn muốn biết thứ hạng của sinh viên.
Bạn sẽ đơn giản đặt công thức sau vào D2 =HẠNG(C2,C2:C100)
Nhưng điều gì sẽ xảy ra nếu bạn được yêu cầu cung cấp thứ hạng của học sinh trong mỗi trường. Do đó, thứ hạng
của mỗi trường sẽ bắt đầu bằng 1..
Đặt công thức sau vào D2 cho trường hợp đó để xếp hạng theo thứ tự giảm dần. (Đối với thứ tự tăng dần,
thay ">" bằng "<" không có dấu ngoặc kép)
=SUMPRODVEL((B$2:B$100=B2)*(C$2:C$100>C2))+1 HOẶC
=COUNTIFS(B$2:B$100,B2,C$2:C$100,">"&C2)+1
45. Xóa bảng chữ cái khỏi chuỗi
Nếu chuỗi của bạn nằm trong ô A1, hãy sử dụng công thức sau để xóa tất cả các bảng chữ cái khỏi chuỗi © eforexcel.com Trang 14 trên 38 Machine Translated by Google Kinh thánh công thức Excel
= THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
SUBSTITUTE(LOWER(A1),"a",""),"b",""),"c",""),"d",""),"e",""),"f" ,""),
"g",""),"h",""),"i",""),"j",""),"k",""),"l"," "),"m",""),"n",""),"o",""),
"p",""),"q",""),"r","") ,"s",""),"t",""),"u",""),"v",""),"w",""),"x","")," y",""),"z","") 46. Xóa số khỏi chuỗi
Để xóa các số khỏi một chuỗi (ví dụ Vij1aY A. V4er7ma8 chứa các số không bắt buộc), chúng ta
có thể sử dụng hàm SUBSTITUTE lồng nhau để xóa các số. Sử dụng công thức bên dưới giả sử chuỗi nằm trong ô A1 -
= THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ( THAY THẾ(
A1,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""), 9,""),0,"")
Lưu ý - Vì công thức này nằm ở nhiều dòng nên bạn sẽ phải sao chép công thức này vào Thanh Công thức. Nếu
bạn sao chép công thức này vào một ô, nó sẽ sao chép công thức này thành ba hàng.
47. Đại diện La Mã của số
Sử dụng chức năng ROMAN.
Do đó ROMAN(56) sẽ cho LVI.
ROMAN chỉ hoạt động với các số từ 1 đến 3999.
48. Tổng các giá trị N dưới cùng trong một phạm vi
Giả sử bạn có các số trong phạm vi A1:A100 và bạn muốn tổng hợp N giá trị dưới cùng
=TỔNG SẢN PHẨM(NHỎ($A$1:$A$100,ROW(1:10)))
Trong trường hợp bạn muốn bỏ qua giá trị 0 (và khoảng trống)
=SUMPRODV(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)))
Cả hai công thức trên sẽ chỉ hoạt động nếu có ít nhất N giá trị theo ROW(1:N).
Do đó, đối với các công thức trên, nó chỉ hoạt động nếu có ít nhất 10 số từ A1 đến A100.
Để khắc phục hạn chế này - Nhập các công thức
dưới đây dưới dạng Công thức mảng
=SUM(IFERROR(SMALL($A$1:$A$100,ROW(1:10)),0)) © eforexcel.com Trang 15 trên 38 Machine Translated by Google Kinh thánh công thức Excel
=SUM(IFERROR(SMALL(IF($A$1:$A$100<>0,$A$1:$A$100),ROW(1:10)),0))
Phiên bản không mảng của các công thức trên (Dành cho Excel 2010 trở lên)
=TỔNG HỢP(TỔNG HỢP(15,6,$A$1:$A$100,ROW(1:10)))
=SUMproduct(AGGREGATE(15,6,$A$1:$A$100/($A$1:$A$100<>0),ROW(1:10))) 49. Tổng mỗi hàng thứ N
Nếu số của bạn nằm trong phạm vi A1:A100, hãy sử dụng công thức bên dưới
=TỔNG HỢP((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,2)=0))
Công thức trên dành cho mỗi hàng thứ 2. Thay thế 2 bằng N. Do đó, với mỗi hàng thứ 5 -
=TỔNG HỢP((A1:A100)*(MOD(ROW(A1:A100)-ROW(A1)+1,5)=0))
Đây là một công thức chung và sẽ áp dụng được cho mọi phạm vi. Nếu phạm vi của bạn là B7:B50, công thức của bạn sẽ trở thành
=TỔNG HỢP((B7:B50)*(MOD(ROW(B7:B50)-ROW(B7)+1,2)=0))
50. Chúng ta có AVERAGEIF. Còn MEDIANIF và CHẾ ĐỘ?
Excel không cung cấp MEDIANIF và MODEIF. Bạn sẽ phải sử dụng công
thức mảng để đạt được các chức
năng này. Giả sử rằng dữ liệu của chúng tôi giống như dưới đây -
Để tính MEDIANIF và MODEIF, hãy nhập các công thức bên dưới, tức
là không phải bằng cách nhấn ENTER
sau khi nhập công thức của bạn mà bằng cách nhấn CTRL+SHIFT+ENTER. Điều
này sẽ đặt dấu ngoặc { }
xung quanh công thức mà bạn có thể thấy trong Thanh Công thức.
Nếu bạn chỉnh sửa lại, bạn sẽ phải
thực hiện lại CTRL+SHIFT+ENTER. Đừng đặt { } theo cách thủ công. © eforexcel.com Trang 16 trên 38