phân loại rồi tính tổng trong excel

[lietlong]

câu cuối khó quá, em nghĩ mãi ko ra

 

[VHA]

câu cuối khó quá, em nghĩ mãi ko ra

1- Lần sau bạn làm vào file excel nhé.
2- Câu cuối tôi phải làm cột phụ để phân loại nhóm và hạng (cột L&M) rồi dùng Pivot table. Trong file tôi lấy dữ liệu là tổng số tiền phải trả của từng loại, từng nhóm. Nếu bạn muốn lọc ra bất cứ dữ liệu nào khác như số tiền trong hay ngoài định mức... chỉ việc gắp thả data trong Pivot thôi nhé.
3- Ai có cách hay hơn post lên cùng ngâm cứu.

 

[BNTT]

1- Lần sau bạn làm vào file excel nhé.
2- Câu cuối tôi phải làm cột phụ để phân loại nhóm và hạng (cột L&M) rồi dùng Pivot table. Trong file tôi lấy dữ liệu là tổng số tiền phải trả của từng loại, từng nhóm. Nếu bạn muốn lọc ra bất cứ dữ liệu nào khác như số tiền trong hay ngoài định mức... chỉ việc gắp thả data trong Pivot thôi nhé.
3- Ai có cách hay hơn post lên cùng ngâm cứu.

Ôi trời, mấy cái công thức trong bảng chiết tính tiền điện, đúng, nhưng sao mà... cao siêu thế? Có cần phải dùng đến SUMPRODUCT không?

1. Trong định mức (cột E)
   

   =IF(D5-C5<=SUMPRODUCT(($B$20:$E$23)*($B$19:$E$19=LEFT($B5,2))*($A$20:$A$23=RIGHT($B5,1))), D5-E5, SUMPRODUCT(($B$20:$E$23)*($B$19:$E$19=LEFT($B5,2))*($A$20:$A$23=RIGHT($B5,1))))

   Xin thay bằng cái này:
   

   =MIN(D5-C5, HLOOKUP(LEFT(B5,2),$B$19:$E$23,CODE(RIGHT(B5))-63,0))

   
   
2. Vượt định mức (cột F)
   

   =IF(D5-C5>SUMPRODUCT(($B$20:$E$23)*($B$19:$E$19=LEFT($B5,2))*($A$20:$A$23=RIGHT($B5,1))),D5-C5-E5,0)

   Xin thay bằng cái này:
   

   =MAX(0, D5-C5-E5)

   
   
3. Tiền trong định mức (cột G)
   

   =SUMPRODUCT(($H$20:$K$23)*($H$19:$K$19=RIGHT($B5,1))*($G$20:$G$23=LEFT($B5,2)))*$E5

   Xin thay bằng cái này:
   

   =VLOOKUP(LEFT(B5,2),$G$20:$K$23,CODE(RIGHT(B5))-63,0)*E5

   
   
4. Tiền vượt định mức (cột H)
   

   =F5*2*SUMPRODUCT(($H$20:$K$23)*($H$19:$K$19=RIGHT($B5,1))*($G$20:$G$23=LEFT($B5,2)))

   Xin thay bằng cái này:
   

   =VLOOKUP(LEFT(B5,2),$G$20:$K$23,CODE(RIGHT(B5))-63,0)*F5*2

 

[VHA]

Cám ơn BNTT, nếu câu cuối dùng Pivot như cách của tôi, đằng nào cũng có cột phụ L&M thì bỏ quách cái LEFT với RIGHT trong công thức đi càng ngắn.

 

[BNTT]

Cám ơn BNTT, nếu câu cuối dùng Pivot như cách của tôi, đằng nào cũng có cột phụ L&M thì bỏ quách cái LEFT với RIGHT trong công thức đi càng ngắn.

Cái Pivot thì Ok rồi, không có gì bàn.
Bạn chỉ cần nghiên cứu lại mấy cái công thức. SUMPRODUCT là hàm mảng, nếu lạm dụng nó, tốc độ tính toán sẽ bị ảnh hưởng nhiều đấy.

 

[concogia]

Cái Pivot thì Ok rồi, không có gì bàn.
.

Thật tình mà nói, riêng cái "zụ" Pivot này mình mình "Nô Ô Kê" với Thầy, có cái phải bàn:
_ Nếu xác định đây là bài tập thực hành Pivot thì "tạm Ok"
_ Ông Bill mà biết chuyện ta phải thêm cột phụ rồi mới ...Pivot ổng sẽ buồn (nhiều khi ổng còn trách mình không biết tạo bảng dữ liệu nữa đó)
_ Với mớ dữ liệu bằng cái....nắm tay và yêu cầu thống kê không phức tạp mà ta Pivot thì hơi ....phí, trong khi dựa vào bảng kết quả ta vẫn tạo được công thức mà cũng chẳng cần cột phụ
Dù sao đây cũng chỉ là suy nghĩ riêng của mình
Hihi

 

[dat_butmuc]

Thật tình mà nói, riêng cái "zụ" Pivot này mình mình "Nô Ô Kê" với Thầy, có cái phải bàn:
_ Nếu xác định đây là bài tập thực hành Pivot thì "tạm Ok"
_ Ông Bill mà biết chuyện ta phải thêm cột phụ rồi mới ...Pivot ổng sẽ buồn (nhiều khi ổng còn trách mình không biết tạo bảng dữ liệu nữa đó)
_ Với mớ dữ liệu bằng cái....nắm tay và yêu cầu thống kê không phức tạp mà ta Pivot thì hơi ....phí, trong khi dựa vào bảng kết quả ta vẫn tạo được công thức mà cũng chẳng cần cột phụ
Dù sao đây cũng chỉ là suy nghĩ riêng của mình
Hihi

Cái "vụ Pivot" này thì bác nói đúng nhưng có một chuyện muốn "chọt" Bác già chơi,
Với trình độ như Bác mà dùng công thức mảng thì hơi bị "phí".
Em xin phép được thay cái "mảng" của Bác

=SUM(IF(RIGHT($B$5:$B$14)=$H28,1)*IF(LEFT($B$5:$B$14,2)=I$27,$I$5:$I$14))

bằng:

=SUMIF($B$5:$B$14,I$27&"*"&$H28,$I$5:$I$15)

tại Ô I28 (ngon lành cành đào chưa...?)

 

[Lê Duy Thương]

Ôi trời, mấy cái công thức trong bảng chiết tính tiền điện, đúng, nhưng sao mà... cao siêu thế? Có cần phải dùng đến SUMPRODUCT không?

1. Trong định mức (cột E)
   
   
   Xin thay bằng cái này:
   ​
   
   
2. Vượt định mức (cột F)
   
   
   Xin thay bằng cái này:
   ​
   
   
3. Tiền trong định mức (cột G)
   
   
   Xin thay bằng cái này:
   ​
   
   
4. Tiền vượt định mức (cột H)
   
   
   Xin thay bằng cái này:
   ​

cho em thay cái này được không thầy ?
Xin thay bằng cái này:
=MAX(0, D5-C5-E5)=($D5-$C5)-$E5
tông kêt thì =SUMIF($B$5:$B$14,B$26&"-"&$A27,$I$5:$I$14)

 

[BNTT]

cho em thay cái này được không thầy ?
Xin thay bằng cái này:
=MAX(0, D5-C5-E5)=($D5-$C5)-$E5
tông kêt thì =SUMIF($B$5:$B$14,B$26&"-"&$A27,$I$5:$I$14)

Sao mà thay như thế được? Nhỡ số lượng điện tiêu thụ nhỏ hơn định mức thì số lượng vượt định mức sẽ là số âm à? Chuyện phi lý!

Chính vì để ngừa cái chuyện định mức là âm, tôi mới sử dụng MAX(0, D5-C5-E5), nếu như (D5-C5-E5)<0 thì kết quả sẽ là 0.

=MAX(0, D5-C5-E5) chính là viết tắt của =IF(D5-C5-E5<=0, 0, D5-C5-E5)

Bạn hiểu chứ?

 

[Lê Duy Thương]

Sao mà thay như thế được? Nhỡ số lượng điện tiêu thụ nhỏ hơn định mức thì số lượng vượt định mức sẽ là số âm à? Chuyện phi lý!

Chính vì để ngừa cái chuyện định mức là âm, tôi mới sử dụng MAX(0, D5-C5-E5), nếu như (D5-C5-E5)<0 thì kết quả sẽ là 0.

=MAX(0, D5-C5-E5) chính là viết tắt của =IF(D5-C5-E5<=0, 0, D5-C5-E5)


Bạn hiểu chứ?

vâng cám ơn thầy , nhưng chuyện định mức mà là âm thì không xảy ra vì D5 bao giờ cũng lớn hơn hoặc = c5 trừ khi hộ sử dụng cho đồng hồ điện quay ngược
mong thầy chỉ giáo thêm

 

[BNTT]

vâng cám ơn thầy , nhưng chuyện định mức mà là âm thì không xảy ra vì D5 bao giờ cũng lớn hơn hoặc = c5 trừ khi hộ sử dụng cho đồng hồ điện quay ngược
mong thầy chỉ giáo thêm

Hì, đúng là tôi hơi máy móc, quên để ý cái chuyện D5-C5-E5 luôn luôn lớn hơn hoặc bằng 0.
Cảm ơn bạn.

 

[Hieu07061984]

Chào Bạn.
Đây là kết quả tính tiền điện.