Bắt đầu từ 1/3 giá điện sinh hoạt đã tăng lên và giá tiền điện được chia làm 7 bậc
1- 50KWH : giá 550
51- 100KWH : giá 865
101- 150KWH : giá 1135
151- 200KWH : giá 1495
201- 300KWH : giá 1620
301- 400KWH : giá 1740
401 KWH trở lên : giá 1790
Bài toán tính giá điện tiêu thụ này cũng khá quen thuộc, bạn nào có bài mẫu thì update lại hộ. Cảm ơn
Bắt đầu từ 1/3 giá điện sinh hoạt đã tăng lên và giá tiền điện được chia làm 7 bậc
1- 50KWH : giá 550
51- 100KWH : giá 865
101- 150KWH : giá 1135
151- 200KWH : giá 1495
201- 300KWH : giá 1620
301- 400KWH : giá 1740
401 KWH trở lên : giá 1790
Bài toán tính giá điện tiêu thụ này cũng khá quen thuộc, bạn nào có bài mẫu thì update lại hộ. Cảm ơn
dùng hàm if là duoc chu gi, bạn tự làm roig tínha ra
giả sử A1 = KWH khi đo tính
B1= if(A1>=401,1790,if(A1>=301,1740,if(A1>=201,1620,if(A1>=151,1495,if(A1>=101,1135,if(A1>=51,865,550))))))*A1
dùng hàm if là duoc chu gi, bạn tự làm roig tínha ra
giả sử A1 = KWH khi đo tính
B1= if(A1>=401,1790,if(A1>=301,1740,if(A1>=201,1620,if(A1>=151,1495,if(A1>=101,1135,if(A1>=51,865,550))))))*A1
Cảm ơn bạn.
Nhưng ý tối là muốn trình bài luôn cả cách lấy đơn giá, tức sẽ có bảng tiêu chuẩn là bảng giá. Khi gỏ vào số diện tiêu thụ thì sẽ trình bày cách tính.
Ví dụ khi gỏ vào số điện tiêu thụ là 285
thì bên dưới sẽ kê ra bảng tính
Điện tiêu thụ Đơn giá Thành tiền
50 Kwh 550
50 Kwh 865
50 Kwh 1135
.......
Public Function tiendien(congto As Long)
Select Case congto
Case Is > 400
tiendien = (congto - 400) * 1790 + 717250
Case Is > 300
tiendien = (congto - 300) * 1740 + 538250
Case Is > 200
tiendien = (congto - 200) * 1620 + 364250
Case Is > 150
tiendien = (congto - 150) * 1495 + 127500
Case Is > 100
tiendien = (congto - 100) * 1135 + 70750
Case Is > 50
tiendien = (congto - 50) * 865 + 27500
Case Is > 0
tiendien = congto * 550
End Select
End Function
Cảm ơn bạn.
Nhưng ý tối là muốn trình bài luôn cả cách lấy đơn giá, tức sẽ có bảng tiêu chuẩn là bảng giá. Khi gỏ vào số diện tiêu thụ thì sẽ trình bày cách tính.
Ví dụ khi gỏ vào số điện tiêu thụ là 285
thì bên dưới sẽ kê ra bảng tính
Điện tiêu thụ Đơn giá Thành tiền
50 Kwh 550
50 Kwh 865
50 Kwh 1135
.......
Có bạn PM hỏi tôi rằng tôi dùng cái gì trong file của tôi. Xin trả lời luôn ở đây:
File đó tôi protect lại để cho khỏi nhập bị xóa nhầm. Nhưng không có password gì hết, nếu muốn xem công thức thì chỉ việc Unprotect ra là thấy hết thôi. Còn không thì xem luôn ở đây cũng được, toàn công thức đơn giản, chẳng có gì ghê gớm lắm đâu:
Còn chuyện tại sao nó có thể chỉ hiện ra số lượng điện tiêu thụ, nghĩa là sử dụng đến mức nào thì hiện ra đến mức đó, còn thì toàn những ô trắng bóc. Xin thưa, đó chỉ là chút thủ thuật Conditional Formatting mà thôi.
Nếu kết hơp cách của Duchủynh hay anh Mỹ và của Tường lại là đúng ý đồ của chị nhất
Vì cái bảng điều kiện sẽ động hơn. Khi cần thây đổi bậ thang thì chỉ thay đổi biến từ bên ngoài. Và để lấy ý tưởng cho các cách áp dụng tương tự thì có thể vận dụng vào để thiết kế file với ứng dụng tương tự
Khi cần thây đổi bậ thang thì chỉ thay đổi biến từ bên ngoài. Và để lấy ý tưởng cho các cách áp dụng tương tự thì có thể vận dụng vào để thiết kế file với ứng dụng tương tự
Mình sửa file của Ptm0412 cho sát thực tế và có thể áp dụng được. Chỉ cần thay đổi số đầu và cuối trong danh sách rồi chọn khách và in ra. Mình chưa căn chỉnh giấy, các bạn điều chỉnh cho phù hợp.
Mình đã gửi bài ở đây, nhưng nay thấy bổ xung vào Topic này cho có hệ thống.Xin thông cảm
Mình xin góp thêm một cách tính và tạo in hóa đơn điện, sau khi nhập xong số liệu cho Sh DATA và chỉnh lại tháng năm là có thể in được.
Hàng tháng chỉ cần chép cột cuối kỳ sang đầu kỳ. Xóa rồi nhập mới cuối kỳ, hiệu chỉnh tiêu đề tháng là xong.
P/S:Hình như mình gửi nhầm, nhờ Mod chuyển giùm vào Topic của Xuan Tham cho hệ thống
Tôi đang quản lý cụm công ti7 điện của 1 HTX, vừa qua có ban hành cách tính tiền điện mới, chúng tôi không thể xử lý được nên nhờ các vị trong diễn đàn giúp đỡ cho, để tôi thực hiện công việc được tốt hơn. Xin các vị nhanh chóng hồi âm cho. Email: tuandovn@yahoo.com hoặc nickname: tuandovn (yahoo messnger). tôi có gửi file mẫu đính kèm.
Mình gửi các bạn hàm tính tiền điện để đơn giản việc phân chia mức giá bằng công thức
Cú pháp =t_dien(sosd,muc,vat)
Trong đó:
-sosd: Số điện sử dụng của KH
-muc: Mức giá cần tính (1,2,3...7)
-vat: 1,0 (True,false)
1(true) giá đã có thuế GTGT
0(false) giá chưa có thuế GTGT
Public Function tiendien(congto As Long)
Select Case congto
Case Is > 400
tiendien = (congto - 400) * 1790 + 717250
Case Is > 300
tiendien = (congto - 300) * 1740 + 538250
Case Is > 200
tiendien = (congto - 200) * 1620 + 364250
Case Is > 150
tiendien = (congto - 150) * 1495 + 127500
Case Is > 100
tiendien = (congto - 100) * 1135 + 70750
Case Is > 50
tiendien = (congto - 50) * 865 + 27500
Case Is > 0
tiendien = congto * 550
End Select
End Function
bạn có thể dùng hàm IF: cho nấc 1,2..7 :=IF($D$6<=50,$D$6,50);
=IF(AND(D$6>50,$D$6<=100),($D$6-50),IF(AND($D$6>100),50,0))
=IF(AND($D$6>100,$D$6<=150),($D$6-100),IF(AND($D$6>150),50,0))
=IF(AND($D$6>150,$D$6<=200),($D$6-150),IF(AND($D$6>200),50,0))
=IF(AND($D$6>200,$D$6<=300),($D$6-200),IF(AND($D$6>300),100,0))
=IF(AND($D$6>300,$D$6<=400),($D$6-300),IF(AND($D$6>400),100,0))
=IF(AND($D$6>400),($D$6-400),0)
để chia ra số sử dụng cho từng nấc ,rồi tương ứng nhân với giá là xong (ở đây tôi để $D$6 là ô chỉ số thực dùng còn tuỳ bảng tính của bạn mà file:Tinhgiadienmoi_2009.xls
Và bạn cũng có thể tham khảo tại đây : Violet.vn/tramui55 vào thư mục tư liệu CNTT có bảng chốt số điện và in hóa đơn cho từng hộ dùng điện trong tháng, bạn có thể điều chỉnh cho phù hợp
Chúc bạn thành công
Hiện nay, rất nhiều trường hợp một số hộ dùng chung 1 công tơ, các mức đơn giá phải chia cho số hộ. Ví dụ 03 hộ dùng 1 công tơ thì đơn giá 600đ cho 50 số đầu mỗi hộ sẽ được tính (50/3)*600 + mức tiếp theo.
Bạn có thể sửa thêm để mọi người dùng cho phù hợp.
Đâu chỉ có loại tiêu thụ 7 gia không đâu! Mà còn loai tiêu thụ khác như cho kinh doanh, trường học...Mà cái vấn đề ở đây không phải là chổ tính ra tiền, mà là quản lý và xuất hoá đơn ra sao, rồi còn tự cập nhật chỉ số công tơ cho tháng sau nưa chư.... muôn thứ luôn đó
anh ơi làm ơn cho em hỏi em cần tính tiền điện tiêu thụ trong tháng trong đó có các hình thức sử dụng là :sản xuất ,kinh doanh và tiêu dùng
trong đó tiền điện tính như sau:hộ sản xuất là :2000 đồng /số,hộ kinh doanh là :1000 đồng /số,hộ tiêu dùng là :800 đông /số
và tính số hộ thuộc loại sản xuất ,kinh doanh ,và tiêu dùng
anh chị nào biết bảo em với nhé !!!
em cảm ơn!!!!!
Các anh chị trên diễn đàn có ai có công thức tính tiền điện bậc thang theo giá mới cho tôi xin với. Tôi chưa biết xử lý như thế nào cả. Rất mong được sự giúp đỡ của các anh chị
Các anh chị trên diễn đàn có ai có công thức tính tiền điện bậc thang theo giá mới cho tôi xin với. Tôi chưa biết xử lý như thế nào cả. Rất mong được sự giúp đỡ của các anh chị
Chỉ cần dìng hàm if lồng là được
Xem file sửa với 4 hàm ì lồng vào
Bạn tự tìm hiểu và lồng vào 7 hàm if
Khi bảng giá co thay đôii thì tiền cũng thay đổi theo
Chủ đề này đã nói từ năm ngoái, khi giá điện chuyển sang tính lũy tiến từ 50KW (thay cho định mức 100KW trước đó).
Các bạn xem ở đây: Tính tiền điện tiêu thụ. Khi giá điện tăng, nhưng các mức tính lũy tiến vẫn như cũ, ta chỉ việc thay đơn giá mới vào là xong thôi mà.
Thôi thì đã lỡ mở ra, xin gửi lên đây bảng tính tiền điện tiêu thụ đã cập nhật giá mới. Để sử dụng, chỉ cần nhập vào 2 chỉ số điện tiêu thụ của tháng trước và tháng này.
Có lẽ bạn nên quản lý theo từng đối tượng : 1 hộ , 2 hộ, 3 hộ ...
mỗi loại 1 sheet
mối sheet bạn chỉ cần tăng số kw tiêu thụ lên gấp 2.3,...lần
Attach file cho bạn tham khảo
Tôi đang quản lý cụm công ti7 điện của 1 HTX, vừa qua có ban hành cách tính tiền điện mới, chúng tôi không thể xử lý được nên nhờ các vị trong diễn đàn giúp đỡ cho, để tôi thực hiện công việc được tốt hơn. Xin các vị nhanh chóng hồi âm cho. Email: tuandovn@yahoo.com hoặc nickname: tuandovn (yahoo messnger). tôi có gửi file mẫu đính kèm.
Hình như "Ông Điện" cũng học Excel nên rất may cũng quy định điện bậc thang 7 mức (Đúng bằng giới hạn lồng hàm if). Vậy thì ta cứ sử dụng công thức if "cổ truyền" xưa nay vẫn làm đảm bảo chính xác mà chẳng phải lệ thuộc vào cái bảng điện sử dụng theo từng mức. Mình Test thấy đạt yêu cầu (Ấy vậy mà lại khác 1 số kết quả so với các bạn. Các bạn cũng nên xem lại chút xem số nào đúng)
Mình gửi file có 2 cách tính (Trực tiếp và bảng chi tiết, trong sheet chi tiết mình để 1 dòng làm bảng giá để phù hợp giá điện nông thôn có quy định bổ xung phí tiện thay đổi) các bạn tham khảo nhé.
mình gửi file có 2 cách tính (trực tiếp và bảng chi tiết, trong sheet chi tiết mình để 1 dòng làm bảng giá để phù hợp giá điện nông thôn có quy định bổ xung phí tiện thay đổi) các bạn tham khảo nhé.
Trời! Mình nhắc bạn nên kiểm tra lại mà bạn không kiểm tra lấy đó so sánh và nhắc mình. Bạn xem nhé:
1/Hộ Nguyễn Hiếu (Số 1): 1 hộ-245 số:
Phân mức giá: =50+50+50+50+45
Thành tiền =50*660+50*951.5+50*1248.5+50*1644.5+45*1782.5 =305,437.50
2/Hộ Đoàn Quang Đặng: 4 hộ- 2,107 số:
Phân mức giá: =50*4+50*4+50*4+50*4+100*4+100*4+507
Thành tiền: =50*4*660+50*4*951.5+50*4*1248.5+50*4*1644.5+100*4*1782.5+100*4*1914+507*1969 = 3,377,783.00
Vậy là công thức của bạn có vấn đề mới đúng. Cụ thể hộ số 1 chỉ có 1 hộ đươc 100 số mức giá bậc 2 và 95 số mức giá bậc 3 là sai quy định. v.v...
Mình gửi file có 2 cách tính (Trực tiếp và bảng chi tiết, trong sheet chi tiết mình để 1 dòng làm bảng giá để phù hợp giá điện nông thôn có quy định bổ xung phí tiện thay đổi) các bạn tham khảo nhé.
Cho phép em "nâng cấp" cái bảng tính của anh lên một tí nhé, không xài IF, và cũng không phải mắc công phải gõ từng công thức cho từng cột.
Trước hết, chèn thêm 1 hàng vào (em chèn vào giữa hàng 5 và 6), nhập vào đó cái bước nhảy của từng mức giá điện sẽ được tính.
Cái hàng chèn thêm này, nếu không thích thấy nó, có thể ẩn nó đi (Hide), chẳng ảnh hưởng gì đến bảng tính hết.
Số điện tiêu thụ ở cột G (định mức ≤50KW):
=MIN(50*C8, F8)
Số điện tiêu thụ ở cột M (định mức >400KW):
=F8-SUM(G8:L8)
Số điện tiêu thụ ở những cột còn lại (từ cột H đến cột L), xài chung 1 công thức:
=MIN(H$6*$C8, $F8-SUM($G8:G8))
Công thức cho cột Thành tiền, em đề nghị dùng SUMPRODUCT, để tránh trường hợp có bạn không biết cách nhập công thức mảng:
=SUMPRODUCT(G8:M8, Gia)
Số lượng công thức cho bảng tính này sẽ giảm hẳn đi, và với những dữ liệu, chắc chắn tốc độ tính toán cũng sẽ nhanh hơn.
Bài 20 topic này, cũng đã tính tương tự như của BNTT, khác 1 chút là bảng giá để riêng 1 sheet, rất thuận tiện để thay đổi:
Từ
|
1
|
51
|
101
|
151
|
201
|
301
|
401
|
Đến
|
50
|
100
|
150
|
200
|
300
|
400
|
trở lên
|
Giá
|
660
|
951,5
|
1248,5
|
1644,5
|
1782,5
|
1914
|
1969
|
Bảng tính chính dùng công thức Min như BNTT, nhưng liên kết đến bảng giá trên.
Cái thuận tiện thứ 2 là giả sử khoảng giữa 2 mức giá liên tiếp cũng thay đổi, không phải 50 mà là 100 KWh chẳng hạn, cũng dễ dàng thay đổi.
Mình đọc vài bài viết liên quan đến cách tính tiền điện bằng excel của các bạn gửi lên, mình cũng hiểu cách của các bạn làm, và mình cũng áp dụng cho bài của mình làm,nhưng không hiểu sao mình làm thế nào cũng không cho ra kết quả đúng. trong một cột mình tính ra thì vẫn có một hàng kết quả không hợp lệ, hoạc thay đổi các con số thì lại cho kết quả sai. Bạnnaof excell pro giúp đỡ mình với, mình xin cảm ơn các bạn nhiều.
Mình đọc vài bài viết liên quan đến cách tính tiền điện bằng excel của các bạn gửi lên, mình cũng hiểu cách của các bạn làm, và mình cũng áp dụng cho bài của mình làm,nhưng không hiểu sao mình làm thế nào cũng không cho ra kết quả đúng. trong một cột mình tính ra thì vẫn có một hàng kết quả không hợp lệ, hoạc thay đổi các con số thì lại cho kết quả sai. Bạnnaof excell pro giúp đỡ mình với, mình xin cảm ơn các bạn nhiều.
Bluedragon
Chào cả nhà
Em cần cả nhà giúp ạ
Em có bảng tính tiền điện những cột số KW chịu đơn giá em không hiểu phải làm công thức nào , em đọc trong topic thì những công thức đó áp dụng khi mình có bảng đơn giá hẳn hoi nhưng ở đây em chỉ được cho dữ liệu tính thôi
Xin hướng dẫn dùm em công thức tính ạ
Em cám ơn nhiều
Chào diễn đàn. Tôi muốn theo dõi tiền điện gia đình hàng tháng, đã down file từ GPE (xin lỗi trước là không nhớ từ ai) để tự làm nhưng kết quả chưa được như ý, cụ thể là: nếu đọc số công tơ xong rồi ghi vào cột A, các cột còn lại tự động hiện số liệu. Tôi đã tự làm đến cột I, riêng cột J là kết quả cuối cùng thì không biết làm. Xin gửi file mong được giúp đỡ. Chân thành cám ơn
Chào diễn đàn. Tôi muốn theo dõi tiền điện gia đình hàng tháng, đã down file từ GPE (xin lỗi trước là không nhớ từ ai) để tự làm nhưng kết quả chưa được như ý, cụ thể là: nếu đọc số công tơ xong rồi ghi vào cột A, các cột còn lại tự động hiện số liệu. Tôi đã tự làm đến cột I, riêng cột J là kết quả cuối cùng thì không biết làm. Xin gửi file mong được giúp đỡ. Chân thành cám ơn View attachment 147643
Đã xem nhiều, đã phân tích từng hàm, nhưng mình không hiểu nổi nên không tự viết được công thức (hàm) cho bảng tính của mình. File đính kèm là số liệu 2 đồng hồ mà nhà đang dùng theo hóa đơn. Nhờ các bạn viết giúp hàm ở cột Thành Tiền. Cám ơn!
Đã xem nhiều, đã phân tích từng hàm, nhưng mình không hiểu nổi nên không tự viết được công thức (hàm) cho bảng tính của mình. File đính kèm là số liệu 2 đồng hồ mà nhà đang dùng theo hóa đơn. Nhờ các bạn viết giúp hàm ở cột Thành Tiền. Cám ơn!
Đã xem nhiều, đã phân tích từng hàm, nhưng mình không hiểu nổi nên không tự viết được công thức (hàm) cho bảng tính của mình. File đính kèm là số liệu 2 đồng hồ mà nhà đang dùng theo hóa đơn. Nhờ các bạn viết giúp hàm ở cột Thành Tiền. Cám ơn!
2 Công thức này giống như đang dùng thêm cột phụ, cũng giống như CT trên nếu nhiều mức quá thì không tính tay như vậy được mà phải dùng cột phụ!!!
Cách khác, dùng CT tổng quát cho nhiều n mức, dùng CT mảng:
Bắt đầu từ 1/3 giá điện sinh hoạt đã tăng lên và giá tiền điện được chia làm 7 bậc
1- 50KWH : giá 550
51- 100KWH : giá 865
101- 150KWH : giá 1135
151- 200KWH : giá 1495
201- 300KWH : giá 1620
301- 400KWH : giá 1740
401 KWH trở lên : giá 1790
Bài toán tính giá điện tiêu thụ này cũng khá quen thuộc, bạn nào có bài mẫu thì update lại hộ. Cảm ơn
Chào các bạn,
Mình có thắc mắc về cách tính tiền theo phương pháp lũy tiến trong excel, xin các bạn giúp đỡ giải đáp dùm mình.
Chẳng hạn danh sách có 6 chủ hộ (xin xem file đính kèm), câu hỏi là (câu 2 ấy) tính Tiền Nước biết rằng : Tiền Nước = Tiêu Thụ * Đơn Giá, trong đó Đơn Giá được tính theo phương pháp luỹ tiến như sau:
Trong file có 2 bài giải, bảng bên trái giải bởi webketoan, bảng bên phải là do mình giải (mình có chú thích trong file).
Nếu hộ nào tiêu thụ từ 0 – 50 & trên 100 – kết quả của mình & webketoan là như nhau nên mình nghĩ là mình làm đúng???
Nếu hộ nào tiêu thụ từ 51-100 – kết quả của 2 bài giải lại khác nhau. Mình kiểm tra công thức của mình thì mình thấy là đúng với yêu cầu của đề bài nhưng thấy kết quả khác nhau nên cũng hơi hơi bị nghi ngờ không biết là mình có sai hay không nữa. Xin các bạn kiểm tra file dùm mình xem là mình có làm sai chỗ nào hay không vì mình đã kiểm tra kỹ lưỡng mà chẳng rõ là sai chỗ nào. Thêm nữa cột tiền nước Q kết quả của mình lại “trật đường rầy” với kết quả tham khảo nên mình nghĩ là mình sai đâu đó rồi, tìm “lung tung” mà chẳng biết sai chỗ nà, chẳng biết là đề bài yêu cầu “gà” mà mình giải “vịt” ở đâu nữa. Xin các bạn giúp đỡ.
Chào các bạn,
Mình có thắc mắc về cách tính tiền theo phương pháp lũy tiến trong excel, xin các bạn giúp đỡ giải đáp dùm mình.
Chẳng hạn danh sách có 6 chủ hộ (xin xem file đính kèm), câu hỏi là (câu 2 ấy) tính Tiền Nước biết rằng : Tiền Nước = Tiêu Thụ * Đơn Giá, trong đó Đơn Giá được tính theo phương pháp luỹ tiến như sau: View attachment 185793
Trong file có 2 bài giải, bảng bên trái giải bởi webketoan, bảng bên phải là do mình giải (mình có chú thích trong file).
Nếu hộ nào tiêu thụ từ 0 – 50 & trên 100 – kết quả của mình & webketoan là như nhau nên mình nghĩ là mình làm đúng???
Nếu hộ nào tiêu thụ từ 51-100 – kết quả của 2 bài giải lại khác nhau. Mình kiểm tra công thức của mình thì mình thấy là đúng với yêu cầu của đề bài nhưng thấy kết quả khác nhau nên cũng hơi hơi bị nghi ngờ không biết là mình có sai hay không nữa. Xin các bạn kiểm tra file dùm mình xem là mình có làm sai chỗ nào hay không vì mình đã kiểm tra kỹ lưỡng mà chẳng rõ là sai chỗ nào. Thêm nữa cột tiền nước Q kết quả của mình lại “trật đường rầy” với kết quả tham khảo nên mình nghĩ là mình sai đâu đó rồi, tìm “lung tung” mà chẳng biết sai chỗ nà, chẳng biết là đề bài yêu cầu “gà” mà mình giải “vịt” ở đâu nữa. Xin các bạn giúp đỡ.
Chào bạn quocgiacan,
Rất chân thành cám ơn file giải của bạn, cám ơn đã bỏ thời gian giúp đỡ mình.
Sau khi "ngâm cứu" file của bạn thì mình nhận ra lỗi sai của mình rồi. Trong cột tính số tiêu thụ lũy tiến từ 51-100, thay vì phải đặt điều kiện cho mức tiêu thụ từ 51-100 & trên 100 thì mình lại bỏ qua khâu này, chỉ đặt điều kiện cho mức tiêu thụ <=50 thôi.
Thay vì dùng công thức đúng như sau (mình kiểm tra kết quả của công thức này & kết quả của công thức của bạn, thấy giống nhau nên nghĩ là đúng???)
=IF(N4<=50,0,IF(AND(N4>50,N4<=100),N4-50,50))
=IF((N4>50),IF(N4<=100,N4-50,50),0) (công thức của bạn)
Mình lại dùng công thức sai này
=IF(N4<=50,0,50) → sai
Thiệt là “đi một ngày đàng, học một sàng khôn”, càng làm bài tập nhiều thì mới thấy "lòi" ra nhiều cái ngu
Trong cột U, bạn có hướng dẫn tính bằng 1 công thức duy nhất
= SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50)
Vì mình chưa học công thức mảng nên mình sẽ phân tích công thức trên theo cách mình hiểu như sau (nếu sai thì chỉnh dùm mình. Cám ơn)
=(IF(N15>0,(N15-0)*2*50)+IF(N15>50,(N15-50)*1*50)+IF(N15>100,(N15-100)*1*50))
Mình “vò đầu bứt tai” vẫn không hiểu sao bạn lại *2*50 hay là *1*50 ở đây vậy, nếu thấy không phiền lòng xin giải thích dùm mình chỗ chưa thông. Cám ơn
Mà sao cái mệnh đề If ở công thức trên chỉ có 1 điều kiện thôi mà excel cũng cho ra kết quả vậy??? [chẳng hạn như =IF(N15>0,(N15-0)*2*50) thay vì IF(N15>0,(N15-0)*2*50,…)]
Cho hỏi ngu thêm 1 câu hỏi, trong file bạn giải, bạn dùng chức năng gì mà excel thay thế hết các số 0 thành – vậy? Cái file của bạn nhìn nó gọn, đẹp & chuyên nghiệp hơn cái file của mình. Xin làm ơn chỉ dùm mình nếu bạn không cảm thấy phiền.
Khi lập điều kiện IF() bạn phải biết "dồn" dữ liệu về đâu: ví dụ
Khi N4>50, tức nó dồn các số lớn về phía TRUE, và dồn các số <=50 về phía FALSE. Nếu điều kiện FALSE thì nó bằng 0, hay nó đã thể hiện "lượng điện" nhỏ hơn mức thứ 2 là <=50 tại cột trước đó. Vậy tất cả số lớn "dồn lại" trong điều kiện N4>50, và để thể hiện "lượng điện" CHO cột này chỉ còn có hai dạng, hoặc <=100 hoặc >100, nếu <=100 thì lấy Nó trừ đi 50, nếu lớn hơn thì chỉ lấy mức MAX của nó là 50.
Bạn tự xem lại và nghiệm công thức của mình có "dồn" được số liệu hay không? =IF(N4<=50,0,IF(AND(N4>50,N4<=100),N4-50,50)). Mục màu đỏ là FALSE của điều kiện N4<=50, tức nó dồn số lớn về FALSE tức phải >50, vậy còn gài thêm N4>50 trong AND(N4>50,N4<=100) chi cho thừa thải!!?
2. Công thức: = SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) là công thức 'Mảng' chuyên trị các bài toán "lũy tiến" (còn 1 công thức khác nữa dạng "Trị giá" + Max() x đơn giá), như: tính giá điện lũy tiến, thuế Thu nhập cá nhân lũy tiến...v.v.. Nó nhìn đơn giản, nhưng đằng sau nó có 1 giải thuật hẵn hoi mà có thể gọi "tính giá bậc thang". Bạn có thể xem file "Giaithich_Bacthang.xlsb" trong đó có ví dụ của bạn đang hỏi. Dùng công thức này thì có thể tính liền ngay thành tiền mà không cần qua các cột phụ chi tiết bậc thang lũy tiến như trong ví dụ của bạn: 0-50; 51-100; >100.
Các sheet phía trước gồm: cách giải thích và cũng gom các công thức hay để hình thành công thức tính lũy tiến của nhiều thành viên: @eke_rula, @HieuCD, @huuthang_bd mà tôi tập hợp được.
Các link sau để bạn tham khảo hoàn cảnh ra đời của công thức:
Có lẽ anh quocgiacan "phủ mền" rồi nên mạn phép thay mặt anh:
* So sánh {100,50,50} và {2,1,1}*50 cái nào gọn hơn? Chẳng qua là rút thừa số chung trong phép nhân thôi
* SUM(...) ở đây không phải là gộp IFs lại như bạn hiểu, mà là cộng tất cả các phần tử trong mảng:
=SUM({1,2,3})=6
=SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50)
Phân tích ra:
=SUM((N15>0)*(N15-0)*100+(N15>50)*(N15-50)*50+(N15>100)*(N15-100)*50)
Trong đó các biểu thức luận lý quy ra 1 hay 0 để tham gia vào phép tính
Khi lập điều kiện IF() bạn phải biết "dồn" dữ liệu về đâu: ví dụ
Khi N4>50, tức nó dồn các số lớn về phía TRUE, và dồn các số <=50 về phía FALSE. Nếu điều kiện FALSE thì nó bằng 0, hay nó đã thể hiện "lượng điện" nhỏ hơn mức thứ 2 là <=50 tại cột trước đó. Vậy tất cả số lớn "dồn lại" trong điều kiện N4>50, và để thể hiện "lượng điện" CHO cột này chỉ còn có hai dạng, hoặc <=100 hoặc >100, nếu <=100 thì lấy Nó trừ đi 50, nếu lớn hơn thì chỉ lấy mức MAX của nó là 50.
Bạn tự xem lại và nghiệm công thức của mình có "dồn" được số liệu hay không? =IF(N4<=50,0,IF(AND(N4>50,N4<=100),N4-50,50)). Mục màu đỏ là FALSE của điều kiện N4<=50, tức nó dồn số lớn về FALSE tức phải >50, vậy còn gài thêm N4>50 trong AND(N4>50,N4<=100) chi cho thừa thải!!?
Sau khi đọc đi đọc lại hướng dẫn của bạn & huy động tất cả “dây thần kinh của não bộ” để suy nghĩ thì hình như mình cũng hiểu hiểu ra vấn đề. Vấn đề ở chỗ là công thức mình thiết lập bị dư điều kiện. Mình chỉnh lại công thức thành:
=IF(N4<=50,0,IF(N4<=100,N4-50,50)) → excel cũng cho ra kết quả giống như của bạn nên mình nghĩ là đúng???
=IF(N4<=50,0,IF(N4<=100,N4-50,50)) → có nghĩa nếu N4<=50 là TRUE thì kết quả = 0 còn N4>50 là FALSE. Cái FALSE này kết hợp với N4<=100, có nghĩa là nếu 50<N4<=100 thì kết quả = N4-50, còn không thì kết quả = 50.
Mình không được thông mình, lại đang “ngấp nghé tuổi già” nên đã bắt đầu lú lẫn & hay quên, gặp đó, biết đó nhưng chắc sẽ “té ngã” vì lỗi tương tự lần nữa. Mình hy vọng là sau nhiều lần “té đau” thì sẽ “nhớ đời” & tự rút ra kinh nghiệm. Nếu mình có lặp lại lỗi tương tự trong tương lai, xin “nhẹ nhàng” nhắc nhở mình, đừng khiển trách “ném đá” mình mà tội nghiệp “con bé”. Cảm ơn trước.
2. Công thức: = SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) là công thức 'Mảng' chuyên trị các bài toán "lũy tiến" (còn 1 công thức khác nữa dạng "Trị giá" + Max() x đơn giá), như: tính giá điện lũy tiến, thuế Thu nhập cá nhân lũy tiến...v.v.. Nó nhìn đơn giản, nhưng đằng sau nó có 1 giải thuật hẵn hoi mà có thể gọi "tính giá bậc thang". Bạn có thể xem file "Giaithich_Bacthang.xlsb" trong đó có ví dụ của bạn đang hỏi. Dùng công thức này thì có thể tính liền ngay thành tiền mà không cần qua các cột phụ chi tiết bậc thang lũy tiến như trong ví dụ của bạn: 0-50; 51-100; >100.
= SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) → công thức này nhờ bạn bebo021999 thì mình đã “ngộ ra chân lý”.
=(LOOKUP(N15,{0,50,100},{2,3,5}%)+1)*SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) → công thức này mình hiểu phần sau, còn phần đầu thì hơi hơi hiểu???
=(LOOKUP(N15,{0,50,100},{2,3,5}%)+1) → Vì là mình chưa học hàm lookup nên mình sẽ giải thích công thức này theo những gì mình hiểu nghen. Công thức này nghĩa là tìm N15, nếu N15=0 thì kết quả là 2%, nếu N15=50 thì kết quả là 3%, nếu N15=100 thì kết quả là 5%. Ủa, vậy nếu N15 nằm giữa những con số này (chẳng hạn 15,60…) thì sao? Hình như là mình hiểu công thức trên chưa chính xác lắm thì phải. Nếu không thấy phiền xin bạn giải thích dùm mình chỗ mình hiểu sai nghen. Cám ơn
Thêm nữa, mình không hiểu vì sao bạn +1 ở cuối công thức, mình biết là nếu không có +1 thì kết quả không đúng nhưng không biết bạn lập luận ra sao để cuối cùng có +1 mà thành ra kết quả đúng vậy?
Cái file bạn đính kèm mình có xem qua, hơi “choáng”, hình như kiến thức của mình đang “biểu tình” đòi “uýnh lộn”. Để từ từ mình sắp xếp lại kiến thức của mình rồi tham khảo file bạn đính kèm sau. Nếu có chỗ nào không hiểu thì mình sẽ “níu tay níu chân” bạn nhờ giúp đỡ, hy vọng là lúc đó bạn có thời gian hen. Cám ơn bạn trước.
Các sheet phía trước gồm: cách giải thích và cũng gom các công thức hay để hình thành công thức tính lũy tiến của nhiều thành viên: @eke_rula, @HieuCD, @huuthang_bd mà tôi tập hợp được.
Các link sau để bạn tham khảo hoàn cảnh ra đời của công thức:
Mình có xem sơ qua các links bạn list, tiếp tục “choáng tập 2”. Thôi cho mình “bỏ ống heo” để dành mấy cái kiến thức này vậy. Sau này khi nào rành rành excel tí ti, kiến thức mình “ngoan ngoãn” không “biểu tình đòi uýnh lộn” nữa thì mình lại "tìm về cố hương" sau. Cho mình nói “See you later” với mấy cái links đó nghen.
Cho mình “gạo ghẻ” câu hỏi cuối cùng, bạn dùng chức năng gì của excel mà đổi số 0 thành – vậy? Xin chỉ dẫn cho mình, nếu mình có hỏi ngu thì xin đừng ném đá. Chân thành cảm ơn
Cho mình “tọc mạch” xíu xiu. Lúc bạn post cái hướng dẫn này là khoảng 3h chiều tại VN, chiều còn đang rất rực rỡ mà quocgiacan đã “mộng đẹp” rồi sao? quocgiacan không sống ở VN à? quocgiacan đang sống ở đâu thế nhỉ?
Ối giời đất ơi, bạn giải thích quá là cặn kẽ, dễ hiểu luôn mặc dù mình chưa có học hàm mảng. Nhờ bạn mà mình tìm được “chân lý” của cái công thức này.
=SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) → cái này “không phải dạng dzừa đâu” à nghen, ít nhất là đối với mình
Hy vọng trong tương lai, mình có thắc mắc gì thì anh/chị/em/bạn GPE cũng giải thích cặn kẽ, dễ hiểu như bạn vậy hen. (Cái này chắc là “hên xui”???)
Mình chúc bạn một buổi tối vui vẻ & có nhiều "mộng đẹp"
Chào thân ái,
Ruby
p.s.1 cho mình plagiarize câu nói của bạn nghen, mình cũng “rất hân hạnh làm quen với bạn, mọi lúc mọi nơi.”
p.s.2 Bạn cũng là dân kế toán à? Lâu ngày gặp lại “người quen”, “mừng húm”! Mình cũng là dân kế toán nhưng mình chuyên về Magagement Accounting hơn là Financial Accounting. Bạn thiên về mảng nào thế?
1. LOOKUP(N15,{0,50,100},{2,3,5}%) tìm N15 có trong khoảng nào của mảng {0,50,100}, nó tương tự MATCH(N15,{0,50,100},1), Ví dụ: N15=30 nó ở khoảng 0->50: khoảng 1, N15=97 nó ở khoảng 2, N15=422 nó ở khoảng 3. Nhưng thay vì trả về số thứ tự, nó dẫn đến kết quả của mảng {2,3,5}% tương ứng số thứ tự mà nó tìm được. Công thức này tìm được số phần trăm phụ phí, nếu nhân cho trị giá thì ra Giá trị phụ phí cộng thêm vào.
2. SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) là công thức tính Trị giá điện theo lũy tiến.
3. (LOOKUP(N15,{0,50,100},{2,3,5}%)+1)*SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) trả về tổng Giá trị điện lũy tiến và phụ phí, cũng là dạng đem thừa số chung ra ngoài, như biểu thức toán: (a+1)b = ab+b
4. Chỉnh định dạng số 0 thành "-" chỉ cần đứng tại ô, hay Vùng có số/Vùng số muốn định dạng, nhấn ở trên Menu\Home thanh bar, có Icon hình dấu phẩy "," và Icon ".00->.0"
Cho mình “tọc mạch” xíu xiu. Lúc bạn post cái hướng dẫn này là khoảng 3h chiều tại VN, chiều còn đang rất rực rỡ mà quocgiacan đã “mộng đẹp” rồi sao? quocgiacan không sống ở VN à? quocgiacan đang sống ở đâu thế nhỉ?
hì hì, cao thủ Excel ở đây là vậy đó bạn. Khi nào có câu hỏi lý thú thì sẵn sàng thức thâu đêm để giải cho ra, còn lúc không có câu hỏi thì tranh thủ ...ngủ bù.
p.s.2 Bạn cũng là dân kế toán à? Lâu ngày gặp lại “người quen”, “mừng húm”! Mình cũng là dân kế toán nhưng mình chuyên về Magagement Accounting hơn là Financial Accounting. Bạn thiên về mảng nào thế?
Bổ sung thêm 1 chút cho giải thích của anh quocgiacan về hàm LOOKUP:
Ngay bản thân nó cũng nói lên mục đích của nó: là hàm tìm kiếm giá trị nào đó trong range và trả về giá trị nào đó trong chính vùng đó hay vùng khác có liên quan.
Có nhiều hàm tìm kiếm khác như VLOOKUP, HLOOKUP, INDEX. Cần phân biệt khi nào thì dùng hàm nào, tùy theo yêu cầu.
* Nếu vùng có nhiều cột và giá trị tìm nằm trong cột đầu tiên bên trái => VLOOKUP
* Nếu vùng có nhiều hàng và giá trị tìm trong dòng đầu tiên trên cùng => HLOOKUP
* Nếu giá trị tìm không nằm trong cột, dòng đầu tiên => INDEX
* Nếu giá trị tìm nằm trong cột, dòng mà các giá trị nằm trong cột, dòng đó được sắp xếp TĂNG DẦN: dùng LOOKUP.
Hàm LOOKUP đặc biệt được dùng trong các trường hợp:
1) Tìm gần đúng:
Ví dụ về tìm tỷ giá USD tại một thời điểm trong quá khứ. Có bảng thống kê các lần thay đổi tỷ giá như sau (vùng A2:B4):
------A--------------B------
-------Ngày -------Tỷ giá
01/01/2017------22,300
01/04/2017------22,500
01/11/2017------22,000
Yêu cầu: ô C1 chứa ngày bất kỳ. Tại C2 dùng công thức tính ra tỷ giá tại thời điểm đó.
Phân tích: A2:A4 là dãy thời gian tăng dần. C1 có thể nằm đâu đó trong vùng A2:A4, có thể nằm ngoài. (Để đơn giản, xét C1 là ngày tính từ 2017 trở đi)
Nếu C1=01/02/2017, tỷ giá của ngày đó là tỷ giá của lần thay đổi trước đó, là của ngày 01/01/2017, là 22300
LOOKUP sẽ tìm giá trị ngày 01/02/2017 trong vùng A2:A4 TỪ DƯỚI lên, cho đến khi gặp giá trị đầu tiên <=C1. Tức là ô A2
(Nếu C1=31/12/2017, sẽ dừng lại tại ô A4)
Lúc này, CT1 sẽ trả về chính ô A2, là ngày 01/01/2017
CT2 lại trả về vị trí tương ứng bên B2:B4 và trả về B2, tức là 22,300
Hàm LOOKUP còn nhiều công dụng đặc biệt khác nữa, khi nào đụng mình sẽ giới thiệu sau khi có thời gian.
hì hì, cao thủ Excel ở đây là vậy đó bạn. Khi nào có câu hỏi lý thú thì sẵn sàng thức thâu đêm để giải cho ra, còn lúc không có câu hỏi thì tranh thủ ...ngủ bù.
Bộ cao thủ Excel ở GPE không có “đi câu cơm” à? Hay là làm dziệc ở VN thích “mộng đẹp” lúc nào thì “mộng đẹp” lúc ấy? Sướng nhuể! Trong khi mình lúc trước làm việc, nhiều lúc con mắt nó dzíp lại mở không lên, phải cố gắng “chạy vòng vòng” hay “lò cò” trong văn phòng cho nó tỉnh ngủ để mà tiếp tục “chiến đấu” với cái “mớ” công dziệc. Tội nghiệp “con bé”!
Công dziệc của bạn giống bảng màu xanh đỏ hường vàng tím… à nha, chắc là thú dzị & kích thích trí “tò mò” sáng tạo lắm hen.
Cái dzụ Finance thì mình có học mấy cái selective units lúc “mài đũng quần” ở Sydney uni, bây giờ nếu “ra đường gặp người wen” chắc cũng biết “say Hello” (hy vọng “hão huyền” là vậy???)
p.s. Mình thích cái signature của bạn lắm à nghen, “oách ra phết”! Bạn tự “mày mò” “sáng chế” ra à? “Mèm ơi” bạn cũng “văn chương lãng mạng” gớm, cái dzụ này thì mình dzuyệt (ý mình là mình thích đó mà, vì mình cũng hay “sáng chế” “lung ta lung tung” lắm)
1. LOOKUP(N15,{0,50,100},{2,3,5}%) tìm N15 có trong khoảng nào của mảng {0,50,100}, nó tương tự MATCH(N15,{0,50,100},1), Ví dụ: N15=30 nó ở khoảng 0->50: khoảng 1, N15=97 nó ở khoảng 2, N15=422 nó ở khoảng 3. Nhưng thay vì trả về số thứ tự, nó dẫn đến kết quả của mảng {2,3,5}% tương ứng số thứ tự mà nó tìm được. Công thức này tìm được số phần trăm phụ phí, nếu nhân cho trị giá thì ra Giá trị phụ phí cộng thêm vào.
Cái dzụ này nhờ sự giải thích cặn kẽ của bạn nên mình đã tìm thấy “Chân-Thiện-Mỹ”. Haleluya!
Trong Match, kiểu dò 1 là kiểu dò không chính xác phải vậy không quocgiacan? Kiểu dò này có giống kiểu dò 1 của Vlookup không vậy? Chắc cũng là “bà con xa" gì đấy ấy nhuể?
3. (LOOKUP(N15,{0,50,100},{2,3,5}%)+1)*SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) trả về tổng Giá trị điện lũy tiến và phụ phí, cũng là dạng đem thừa số chung ra ngoài, như biểu thức toán: (a+1)b = ab+b
Sau khi đọc hướng dẫn của bạn & rồi so sánh giữa công thức của bạn với công thức của mình thì hình như mình đã “ngộ ngộ” ra “chân lý”.
V15=(LOOKUP(N15,{0,50,100},{2,3,5}%)+1)*SUM((N15>{0,50,100})*(N15-{0,50,100})*{2,1,1}*50) (công thức của “sư phụ”)
V15=(IF(N15<=50,R15*2%,IF(N15<=100,R15*3%,R15*5%)))+R15 (công thức của “đệ tử”)
V15=((IF(N15<=50,2%,IF(N15<=100,3%,5%)))*R15)+R15 (dạng rút thừa số chung của công thức trên).
V15=((IF(N15<=50,2%,IF(N15<=100,3%,5%)))+1)*R15
→Vậy là “chỗ ứ đọng” đã được “khai thông”, “mừng đến sắp phát khóc”!
4. Chỉnh định dạng số 0 thành "-" chỉ cần đứng tại ô, hay Vùng có số/Vùng số muốn định dạng, nhấn ở trên Menu\Home thanh bar, có Icon hình dấu phẩy "," và Icon ".00->.0"
Ai chẳng phải ăn cơm.
Anh quocgiacan thì không biết thế nào
Như bác Chanh TQ, em HaiYen17, concogia, về hưu rồi, cơm có sẵn, chỉ khi bác gái gọi dùng cơm thì buông thôi, còn thì mút chỉ cà tha
Mình và một số anh chị em khác, lúc nào "câu" thì câu, lúc chờ "cắn" thì tranh thủ
Kế toán thì qua kỳ báo cáo (như bây giờ) buông cần được rồi.
Bổ sung thêm 1 chút cho giải thích của anh quocgiacan về hàm LOOKUP:
Ngay bản thân nó cũng nói lên mục đích của nó: là hàm tìm kiếm giá trị nào đó trong range và trả về giá trị nào đó trong chính vùng đó hay vùng khác có liên quan.
Có nhiều hàm tìm kiếm khác như VLOOKUP, HLOOKUP, INDEX. Cần phân biệt khi nào thì dùng hàm nào, tùy theo yêu cầu.
* Nếu vùng có nhiều cột và giá trị tìm nằm trong cột đầu tiên bên trái => VLOOKUP
* Nếu vùng có nhiều hàng và giá trị tìm trong dòng đầu tiên trên cùng => HLOOKUP
* Nếu giá trị tìm không nằm trong cột, dòng đầu tiên => INDEX
* Nếu giá trị tìm nằm trong cột, dòng mà các giá trị nằm trong cột, dòng đó được sắp xếp TĂNG DẦN: dùng LOOKUP.
Hàm LOOKUP đặc biệt được dùng trong các trường hợp:
1) Tìm gần đúng:
Ví dụ về tìm tỷ giá USD tại một thời điểm trong quá khứ. Có bảng thống kê các lần thay đổi tỷ giá như sau (vùng A2:B4):
------A--------------B------
-------Ngày -------Tỷ giá
01/01/2017------22,300
01/04/2017------22,500
01/11/2017------22,000
Yêu cầu: ô C1 chứa ngày bất kỳ. Tại C2 dùng công thức tính ra tỷ giá tại thời điểm đó.
Phân tích: A2:A4 là dãy thời gian tăng dần. C1 có thể nằm đâu đó trong vùng A2:A4, có thể nằm ngoài. (Để đơn giản, xét C1 là ngày tính từ 2017 trở đi)
Nếu C1=01/02/2017, tỷ giá của ngày đó là tỷ giá của lần thay đổi trước đó, là của ngày 01/01/2017, là 22300
LOOKUP sẽ tìm giá trị ngày 01/02/2017 trong vùng A2:A4 TỪ DƯỚI lên, cho đến khi gặp giá trị đầu tiên <=C1. Tức là ô A2
(Nếu C1=31/12/2017, sẽ dừng lại tại ô A4)
Lúc này, CT1 sẽ trả về chính ô A2, là ngày 01/01/2017
CT2 lại trả về vị trí tương ứng bên B2:B4 và trả về B2, tức là 22,300
Hàm LOOKUP còn nhiều công dụng đặc biệt khác nữa, khi nào đụng mình sẽ giới thiệu sau khi có thời gian.
Ối zời đất ơi, phải nói là bạn có tài giải thích à nghen, nhờ vậy mà mình hình như “ngộ ngộ” ra “cách làm dziệc” của Lookup. Hình như Lookup dạng Vector thì mình hiểu hiểu còn Lookup dạng mảng thì mình hơi không hiểu hiểu (cái này còn “hên xui”!)
Mà đọc lý thuyết thì hiểu, nhưng muốn rành rành thì phải có bài tập thực hành & phải thực hành cho nhiều dzào!
Có ai có nhiều bài tập thực hành về hàm Lookup không dzậy? Làm ơn cho mình xin files với, mình chân thành cảm ơn trước.
Mình đính kèm cái file mà mình tập hợp các kiến thức mình biết về hàm Lookup, bạn nào cũng “lơ tơ mơ” về hàm Lookup như mình thì cứ “nhào” dzô mà tham khảo. Nhân tiện đây xem dùm mình là mình có sai ở đâu đó trong file không, nếu sai thì sửa dùm mình, đừng ném đá mình mà tội nghiệp vì mình cũng “lờ tờ mờ” về hàm Lookup lắm. Nếu có gì bổ sung thì cứ “mạnh miệng” mà “thêm thắt hoa lá cành” cho nó “hoàn chỉnh xôm tụ” để anh/chị/em/bạn ai còn “tay mơ” như mình có cái để mà tham khảo. Còn nếu mình có gì “xai xót” thì xin “mở lòng từ bi” mà “khoan hồng” cho "con nhỏ". Chân thành cảm ơn
Chúc bạn & toàn thể thành viên GEP đã/đang/sẽ wen biết một buối tối tràn đầy niềm dzui.
“Xí” để từ từ mình giải thích cái file bạn gởi theo cách mình “wen biết” về “em” Lookup không thôi mình “lịu” (just joking, đùa tí cho nó có không khí)
Unit Price =LOOKUP(2,1/(A2:A7=F2)/(B2:B7=G2),C2:C7)
Thứ nhất – Sao cái công thức này nhìn “lạ wắc lạ wơ” dzậy ta? Sao nó chẳng “sao y bản chính” của dạng Vector hay mảng gì “hết trơn hết trọi” dzậy? Chỉ là hơi giông giống thôi.
Thứ hai – Mình “nhìn wanh nhìn wất” thì thấy có “/” nên mình nghĩ đây là công thức mảng??? Đã gặp công thức có “/” đâu đó trong các bài giải trước, vẫn chưa rành lắm về cách sử dụng, vẫn còn “lờ tờ mờ” nên vẫn chưa dám "say Hello" với em ý. Có phải “/” nghĩa là chia không? Hay còn ý nghĩa khác???
Thứ ba – để mình giải thích công thức theo cách mình hiểu nghen, chả rõ là có đúng “đâu vào đâu” hay không nữa???
LOOKUP(2,1/(A2:A7=F2)/(B2:B7=G2),C2:C7) → tìm điều kiện thứ 2 trong vùng A2:A7(mà điều kiện này tương ứng với điều kiện F2???), tìm điều kiện thứ 1 trong vùng B2:B7(mà điều kiện này tương ứng với điều kiện G2???), tìm điều kiện H2 trong vùng C2:C7. Nếu mình hiểu ngu xin đừng ném đá. Cám ơn trước.
Ủa mà sao lại LOOKUP(2,1/(A2:A7=F2)/(B2:B7=G2),C2:C7)
Sao không LOOKUP theo thứ tự là (1,2/(B2:B7=G2)/(A2:A7=F2),C2:C7). Mình có thử cái công thức này trên excel, “thành thật chia buồn” kết quả là #N/A, biết là sai nhưng vẫn chưa hiểu lắm cái lý luận/logic đằng sau cho "sự ra đời" của công thức theo thứ tự đúng như dzậy. Nếu thấy không phiền lòng xin giải thích dùm mình. Mình cảm ơn
Thứ 4 – Sao cái cột unit price không có theo thứ tự tăng dần gì “hết chơn hết chọi” dzậy? Dzậy lỡ lookup không ra đúng kết wả thì sao nhuể? Lúc đó thì làm sao kiểm tra là biết có đúng hay không?
Thứ 5 – Unit price = INDEX(C2:C7,MATCH(F2&G2,A2:A7&B2:B7,0))
Hòi nào tới giờ mình toàn dùng index(bảng,match(),match()) để dò tìm bảng có hàng với cột không hà, chưa có kết hợp index(…,match()) để tìm cột không nên thấy không có wen. Sẽ từ từ làm wen, nếu có gì thì sẽ “níu tay níu chân” ai đó trên GPE nhờ giúp đỡ, hy dzọng lúc đó không ai chê câu hỏi ngu mà chẳng buồn "điếm xỉa"
Chúc bạn một ngày mới “chàn chề” năng lượng & niềm dzui.
Ủa mà sao lại LOOKUP(2,1/(A2:A7=F2)/(B2:B7=G2),C2:C7)
Sao không LOOKUP theo thứ tự là (1,2/(B2:B7=G2)/(A2:A7=F2),C2:C7). Mình có thử cái công thức này trên excel, “thành thật chia buồn” kết quả là #N/A, biết là sai nhưng vẫn chưa hiểu lắm cái lý luận/logic đằng sau cho "sự ra đời" của công thức theo thứ tự đúng như dzậy. Nếu thấy không phiền lòng xin giải thích dùm mình. Mình cảm ơn
Bạn xem Thầy @ndu96081631 giải thich ở đây. Dò tìm nhiều điều kiện:
Cú pháp chung:
=LOOKUP( 2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
.....1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n): Được gọi là "Véc tơ" chỉ hướng
Dựa vào biểu thức toán học: 1/n/m thì bằng 1/(n*m) (Vd: 1/2/8 = 1/ (2*8) = 1/ 16 = 0.0625 )
Với các biểu thức điều kiện, để giản lược, ta có thể áp dụng toán tử "*" (nhân) thay cho hàm AND(), và toán tử "+" (cộng) thay cho hàm OR().
Các biểu thức điều kiện này, theo từng phần của biểu thức thực hiện, sẽ trả kết quả từng mảng chứa các trị hoặc Đúng (TRUE hay 1) hoặc sai (FALSE hay 0), sau đó nó kết hợp lại như hàm AND(): 1/ (Mảng1 * Mảng2 *....* Mảngn). Ví dụ:
Do kết quả có hai giá trị 1 (xanh và đỏ), thì "Véc tơ" chỉ hướng sẽ báo cho LOOKUP chọn theo số 1 sau cùng (màu đỏ), Ví dụ như trên thì đếm theo số dòng nó ở là: 5, LOOKUP() sẽ lấy giá trị tương ứng với dòng 5 trong "Mảng cần lấy kết quả". Tức là khi có nhiều kết quả phù hợp với các điều kiện trong các biểu thức mang lại, "Véc tơ" chỉ hướng sẽ chọn kết quả phù hợp đứng thứ tự cuối cùng.
Để ý rằng các kết quả trả về trong "Véc tơ" chỉ hướng có thể chứa nhiều kiểu giá trị: số, chuỗi, luận lý (True-False), lỗi (#DIV/0!, #N/A....), tuy nhiên nó chỉ xét và tính trên các kết quả kiểu: số, chuỗi và luận lý, nó "phớt lờ" bỏ qua không tính các giá trị Lỗi. Đây là điểm mạnh của hàm LOOKUP(), nhờ điều này mà ta lọc ra được các giá trị phù hợp để truy xuất dữ liệu mà không cần phải dùng thêm bất kỳ hàm phụ trợ nào khác.
.....Tại sao là số 2?
Do biểu thức "Véc tơ" chỉ hướng dạng: 1/n, ta biết: 1/n<=1 , nên kết quả có số lớn nhất là 1. Nên "giá trị tìm" cho LOOKUP() ta có thể đặt bất cứ số nào tùy ý, miễn sao >=1.
Nói chung, tùy theo giá trị kết quả tại biểu thức "Véc tơ" chỉ hướng mà ta đặt số "giá trị tìm" cho phù hợp, tức đặt giá trị tìm lúc nào cũng lớn hơn hoặc bằng giá trị lớn nhất mà "Véc tơ" chỉ hướng trả về.
Lưu ý:
So sánh với kiểu truy lục của VLOOKUP(trị dò, bảng dò, cột dò, kiểu dò):
Với kiểu dò = 0 là dò chính xác,
Với kiểu dò = 1 là dò không chính xác, và bắt buộc dữ liệu phải được sort trước.
Vậy thì:
LOOKUP(trị dò, bảng dò, bảng kết quả) sẽ tương đương với VLOOKUP mà kiểu dò =1 ---> như vậy cũng như VLOOKUP, bắt buộc dữ liệu phải sort trước.
LOOKUP(2, 1/(....), ) sẽ tương đương với VLOOKUP mà kiểu dò =0 ---> Cũng như VLOOKUP, dữ liệu không cần sort.
Hôm wa, mới đọc sơ qua cái hướng dẫn thì “choáng toàn tập” lại “mất lửa” nên cụt hứng, không muốn xem nữa, bây giờ lại ngồi “rị mọ” thì thấy hơi hơi hiểu cách làm việc của Lookup
Vậy là mỗi khi dò tìm nhiều điều kiện thì mình sẽ ”lôi bí kíp” này ra mà áp dụng.
=LOOKUP( 2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
Mình nhìn kỹ công thức thì thấy có {}, trong trường hợp này mình thay thế {} =() thì công thức có đúng không vậy? Hay đây là công thức mảng, phải giữ nguyên như thế mới là đúng?
o Do kết quả có hai giá trị 1 (xanh và đỏ), thì "Véc tơ" chỉ hướng sẽ báo cho LOOKUP chọn theo số 1 sau cùng (màu đỏ), Ví dụ như trên thì đếm theo số dòng nó ở là: 5, LOOKUP() sẽ lấy giá trị tương ứng với dòng 5 trong "Mảng cần lấy kết quả". Tức là khi có nhiều kết quả phù hợp với các điều kiện trong các biểu thức mang lại, "Véc tơ" chỉ hướng sẽ chọn kết quả phù hợp đứng thứ tự cuối cùng.
Mình “nhìn wanh nhìn wất” chả thấy có màu xanh & đỏ đâu cả nhưng theo cách giải thích của bạn thì mình nghĩ là số 1 cuối cùng trong dãy số {1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}.
1. Do biểu thức "Véc tơ" chỉ hướng dạng: 1/n, ta biết: 1/n<=1 , nên kết quả có số lớn nhất là 1. Nên "giá trị tìm" cho LOOKUP() ta có thể đặt bất cứ số nào tùy ý, miễn sao >=1.
2. Nói chung, tùy theo giá trị kết quả tại biểu thức "Véc tơ" chỉ hướng mà ta đặt số "giá trị tìm" cho phù hợp, tức đặt giá trị tìm lúc nào cũng lớn hơn hoặc bằng giá trị lớn nhất mà "Véc tơ" chỉ hướng trả về.
Xin lỗi cho mình hỏi ngu 1 xíu, sau khi đọc 2 cái giải thích trên mình vẫn không “xâu chuỗi” lại được tại sao 2 cái lý luận này nó liên quan đến số 2 trong công thức của Lookup. Nếu thấy không phiền lòng, xin làm ơn giải thích dùm mình chỗ mình còn chưa thông. Cảm ơn bạn trước
→ Vậy là n có ít nhất 2 điều kiện
n>=1
n>= giá trị lớn nhất/max mà "Véc tơ" chỉ hướng trả về ??? (hiu hiu, cái dzụ này hỏng có hiểu!) → n>= giá trị lớn nhất/max của Mảng cần lấy kết quả???
...
Nếu mình hiểu sai đâu đó thì xin làm ơn chỉnh lại dùm mình, mình cám ơn trước
Mình đã phân biệt được với loại hàm Lookup nào thì dữ liệu cần tìm phải được sắp xếp theo thứ tự tăng dần “rùi”. Dzui wá “pà con” ơi!
Tuy bây giờ đã hiểu hiểu lý thuyết của Lookup nhưng phải đợi mình thực hành thì mình mới hơi hơi thành thạo, nếu sau này mình có hỏi ngu như dzậy nữa thì xin thứ lỗi cho mình bị là mình chưa có wen, từ lý thuyết đến thực hành đến "thành thục" đối với mình là cả một wá trình, xin đừng làm nhanh wá mà mình "lịu". Cảm ơn trước
Rất cám ơn bài viết này của bạn.
Mình chúc bạn một buổi chiều dzui dzẻ
p.s. bạn cũng là “phi miu” giống mình à? Mừng wá, lại gặp người wen nữa rồi.
Hôm wa, mới đọc sơ qua cái hướng dẫn thì “choáng toàn tập” lại “mất lửa” nên cụt hứng, không muốn xem nữa, bây giờ lại ngồi “rị mọ” thì thấy hơi hơi hiểu cách làm việc của Lookup
Vậy là mỗi khi dò tìm nhiều điều kiện thì mình sẽ ”lôi bí kíp” này ra mà áp dụng.
=LOOKUP( 2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
Mình nhìn kỹ công thức thì thấy có {}, trong trường hợp này mình thay thế {} =() thì công thức có đúng không vậy? Hay đây là công thức mảng, phải giữ nguyên như thế mới là đúng?
→ ( {1;1;0;0;1;1} * {1;0;0;0;1;0} ) → là 1 mảng đại diện cho Biểu thức điều kiện 1???
Mình “nhìn wanh nhìn wất” chả thấy có màu xanh & đỏ đâu cả nhưng theo cách giải thích của bạn thì mình nghĩ là số 1 cuối cùng trong dãy số {1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!}.
Xin lỗi cho mình hỏi ngu 1 xíu, sau khi đọc 2 cái giải thích trên mình vẫn không “xâu chuỗi” lại được tại sao 2 cái lý luận này nó liên quan đến số 2 trong công thức của Lookup. Nếu thấy không phiền lòng, xin làm ơn giải thích dùm mình chỗ mình còn chưa thông. Cảm ơn bạn trước
→ Vậy là n có ít nhất 2 điều kiện
n>=1
n>= giá trị lớn nhất/max mà "Véc tơ" chỉ hướng trả về ??? (hiu hiu, cái dzụ này hỏng có hiểu!) → n>= giá trị lớn nhất/max của Mảng cần lấy kết quả???
...
Nếu mình hiểu sai đâu đó thì xin làm ơn chỉnh lại dùm mình, mình cám ơn trước
Mình đã phân biệt được với loại hàm Lookup nào thì dữ liệu cần tìm phải được sắp xếp theo thứ tự tăng dần “rùi”. Dzui wá “pà con” ơi!
Tuy bây giờ đã hiểu hiểu lý thuyết của Lookup nhưng phải đợi mình thực hành thì mình mới hơi hơi thành thạo, nếu sau này mình có hỏi ngu như dzậy nữa thì xin thứ lỗi cho mình bị là mình chưa có wen, từ lý thuyết đến thực hành đến "thành thục" đối với mình là cả một wá trình, xin đừng làm nhanh wá mà mình "lịu". Cảm ơn trước
Rất cám ơn bài viết này của bạn.
Mình chúc bạn một buổi chiều dzui dzẻ
p.s. bạn cũng là “phi miu” giống mình à? Mừng wá, lại gặp người wen nữa rồi.
Hôm wa, mới đọc sơ qua cái hướng dẫn thì “choáng toàn tập” lại “mất lửa” nên cụt hứng, không muốn xem nữa, bây giờ lại ngồi “rị mọ” thì thấy hơi hơi hiểu cách làm việc của Lookup
Vậy là mỗi khi dò tìm nhiều điều kiện thì mình sẽ ”lôi bí kíp” này ra mà áp dụng. =LOOKUP( 2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
Chào bebo021999,
Sau “một thời zan” “đóng wân” ở “doanh chại” GPE & “hành wân” với “nằm dzùng” ở “chiến chường” Lookup (“hẻm” biết khi nào mới được “zải ngũ”(???), cứ “hành wân” kiểu này hoài chắc là “bỏ xác” ngoài “chiến chường” lúc nào hổng có hay) mình đã ngộ ngộ ra cách Lookup(2,1...) “pày wân pố chận”. Để mình giải thích hàm Lookup(2,1...) theo cách hiểu của mình nghen, nếu có gì sai, thì xin sửa dùm mình, không thôi lần sau “đạn lạc” mình “bỏ mạng” ở “chiến trường” trong lúc “tóc còn xanh”, tội nghiệp “con bé”. Cám ơn trước!
=LOOKUP(2,1/(A2:A7=D2)/(B2:B7=E2),C2:C7)
A2:A7=D2 → so sách từng cell trong vùng A2:A7 với D2 → Nếu cái nào BẰNG thì đương nhiên cho kết quả =TRUE (1), ngược lại là FALSE (0)
LOOKUP(2,1/(A2:A7=D2)/(B2:B7=E2),C2:C7) → tìm số 2 trong mảng kết quả trên (gồm có 1 hoặc là #DIV/0!).
Số lớn nhất là 1, mà lại đi tìm 2 thì chắc chắn không có. Tìm đến cuối vẫn không có thì nó nhảy lùi lại số có nghĩa gần nhất (số 1) & là vị trí có giá trị = D2 & E2
→ Tìm số 2 trong một mảng chỉ có số 1 & #DIV/0! thì đương nhiên nó sẽ lấy số 1 cuối cùng trong mảng, tra qua bên C2:C7 để lấy kết quả tương ứng
→ 2 trong mảng đó sẽ không có kết quả chính xác là 2 do đó Lookup sẽ cho kết quả nhỏ nhất so với 2
Số 2 trong công thức trên có thể thay bằng bất cứ số nào, miễn sao >= 1
Bây giờ tuy đã biết cách Lookup(2,1...) vận hành nhưng để thành thạo Lookup chắc sẽ phải mất thời gian, vì là mình chưa có wen.
Mà hổng biết khi nào mình mới đạt được trình độ "from Zero to Hero" nhể. Zero thì là “bạn chí cốt” của mình từ nhỏ đến lớn còn Hero thì mình chưa có wen, còn là “người” "lạ wắc lạ wơ", chắc là lúc “vô hòm nằm” thì mới có cơ hội (hy dzọng hão huyền là vậy) kết bạn cho nó đỡ cô wạnh, buồn tủi lúc wa thế giới bên kia nhể. Mà chắc gì nó chịu kết bạn với mình???
Bây giờ tuy đã biết cách Lookup(2,1...) vận hành nhưng để thành thạo Lookup chắc sẽ phải mất thời gian, vì là mình chưa có wen.
Mà hổng biết khi nào mình mới đạt được trình độ "from Zero to Hero" nhể. Zero thì là “bạn chí cốt” của mình từ nhỏ đến lớn còn Hero thì mình chưa có wen, còn là “người” "lạ wắc lạ wơ", chắc là lúc “vô hòm nằm” thì mới có cơ hội (hy dzọng hão huyền là vậy) kết bạn cho nó đỡ cô wạnh, buồn tủi lúc wa thế giới bên kia nhể. Mà chắc gì nó chịu kết bạn với mình???
Có khiếu văn chương có khác, trình bày đâu ra đó.
Một câu hỏi cho bạn:
Biểu thức này: (A2:A7=D2)/(B2:B7=E2)
Tại sao lại chia mà không phải là nhân? (A2:A7=D2)*(B2:B7=E2)
Good mórning bebo021999,
Ối zời ơi, đây là lần đầu tiên trong cuộc đời mình có người khen mình có khiếu văn chương, dzậy mà từ đó tới zờ toàn bị chê là văn chương "cụt ngủn", như "dzùi đục chấm mắn gừng" không hà. Chắc chiều nay phải ra Vietlott mua Jackpot mới được, hổng chừng ngày mai mình "chúng" thưởng thì "pái pai" "em nghèo" luôn để thành "đại gia" cho nó "oách" (trong "chường hợp" mình "chúng" "xơ xơ" "dzài" chục tỷ ấy) (just joking!)
Vì là mình dốt Toán nên để mình trình bày câu hỏi bạn theo cách hiểu của mình nghen, nếu có gì sai thì xin làm ơn sửa dùm mình. Chân thành cảm ơn
Hỏi:
Biểu thức này: (A2:A7=D2)/(B2:B7=E2)
Tại sao lại chia mà không phải là nhân? (A2:A7=D2)*(B2:B7=E2)
Đáp:
m/n = m*(1/n)
Công thức chung chính là dựa vào biểu thức toán học: 1/n/m = 1/(n*m) (Vd: 1/2/8 = 1/(2*8) = 1/16 = 0.0625 )
Hay nói cách khác:
1/(A2:A7=D2)/(B2:B7=E2) = 1/((A2:A7=D2)*(B2:B7=E2))
→LOOKUP( 2, 1/(Biểu thức điều kiện 1)/(Biểu thức điều kiện 2)/.... /(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
=LOOKUP( 2, 1/(Biểu thức điều kiện 1)*(Biểu thức điều kiện 2)*.... *(Biểu thức điều kiện n) , Mảng cần lấy kết quả)
Nếu mình hiểu "xai" đâu đó làm ơn "xửa" lại dùm mình dzới, mình cảm ơn nhiều.
Chúc bạn một ngày làm dziệc “chàn chề” năng lượng & niềm dzui.
p.s. Cuối tuần “rùi” bebo021999 có dzui hông dzậy? Cuối tuần "rùi" mình lại "lui cui" với Lookup.
Chưa nắm vấn đề!!!
Nếu nhân, trả về mảng 0 và 1.
Nếu chia, trả về mảng #DIV/0 và 1.
Mảng nào đáp ứng yêu cầu của LOOKUP là sắp dần tăng dần?
Mảng nhân chắc chắn không.
Mảng chia, LOOKUP hiểu là tăng dần, quăng cục lơ đến giá trị #DIV/0, coi như không có
Mảng nào đáp ứng yêu cầu của LOOKUP là sắp dần tăng dần?
Mảng nhân chắc chắn không.
Mảng chia, hiểu là tăng dần quăng cục lơ đến giá trị #DIV/0, coi như không có
Cho mình hỏi ngu xíu xui, đồng ý là nếu trong mảng chia chỉ có #DIV/0 và 1 mà nếu Lookup “ngó lơ” “zả bộ không wen biết” #DIV/0 thì trong mảng chỉ còn là số 1 thôi thì có thể hiểu đại loại là mảng được sort ascending. Nhưng Lookup(2,1…) thì dữ liệu đâu có cần sort ascending đâu, thì “wan tâm xăn xóc” đến cái dzụ này làm chi cho nó “mệt” thế???
Hu hu, “sấu hổ” wá
Ừa, giờ thì mình đã ngộ ra cái “trân lý”, đúng là hình như cái đầu của mình chỉ dùng để “đội mũ” hay sao ấy???
Nếu nhân, trả về mảng 0 và 1.
Nếu chia, trả về mảng #DIV/0 và 1.
Thanks a great deal!
Cho mình hỏi ngu xíu xui, đồng ý là nếu trong mảng chia chỉ có #DIV/0 và 1 mà nếu Lookup “ngó lơ” “zả bộ không wen biết” #DIV/0 thì trong mảng chỉ còn là số 1 thôi thì có thể hiểu đại loại là mảng được sort ascending. Nhưng Lookup(2,1…) thì dữ liệu đâu có cần sort ascending đâu, thì “wan tâm xăn xóc” đến cái dzụ này làm chi cho nó “mệt” thế???
Nếu bạn ham học thì cứ nghiên cứu đi. không biết thì cứ hỏi đừng ngại. Bạn đã gặp được 2 cao thủ GPE hướng dẫn nhiệt tình rồi đấy nhé.
Đồng chí quocgiacan và bebo là 2 chuyên gia công thức đó.
hình như cái đầu của mình chỉ dùng để “đội mũ” hay sao ấy??? .......Nhưng Lookup(2,1…) thì dữ liệu đâu có cần sort ascending đâu, thì “wan tâm xăn xóc” đến cái dzụ này làm chi cho nó “mệt” thế???
Nếu bạn là đại biểu QH thì còn thêm chức năng nữa của cái đầu: cử động lên xuống, gọi là "gật"
Trở lại công thức:
=LOOKUP(n, vùng tăng dần, vùng trả về)
Trong đó vùng tăng dần: {#DIV/0,#DIV/0,...1,#DIV/0,1,#DIV/0,...}
Có thể nhiều số 1 bên trong nếu có nhiều điều kiện thỏa, tuy nhiên LOOKUP sẽ tìm con số n từ phải sang (mảng ngang) hoặc từ dưới lên (mảng dọc) (Nếu dùng MATCH thì sẽ theo hướng ngược lại), tìm số đầu tiên nhỏ hơn n, trả về giá trị tương ứng bên vùng trả về . Do đó n có thể là 2,3 hay 100000000.
P/S: Cố gắng giữ gìn sự trong trắng của tiếng Việt nhé.
. Xin các bạn giúp đỡ.
