Hỏi cách tạo bảng thống kê, tính tổng theo những record có cùng mã (1 người xem)

[06th2d]

ví dụ mình có :
stt ---ten ---so luong
1------a--------5
2------c--------4
3------p--------8
4------a--------7
5------c--------2

làm như thế nào để ta có :
------bảng thống kê---------

stt ---ten ---so luong
1------a--------12
2------c--------6
3------p--------8

** tính tổng của những record có cùng tên....
nếu dùng sumif có thể tính được tổng nhưng làm sao để nó chỉ lấy ra a và số lượng = tổng???

thấy quen lắm mà tự nhiên giờ không nhớ ra phải làm sao(hình như trong sql thì phải) rối quá không nghĩ được hướng giải quyết các bạn giúp với

cảm ơn !!!

 

[phucbugis]

ví dụ mình có :

công thức chỉ "đơn giản" thế này thôi,

E11=IFERROR(INDEX(List;MATCH(MIN(IF(COUNTIF($E$10:E10;List)=0;COUNTIF(List;"<"&List)+1;9,9999E+307)); COUNTIF(List;"<"&List)+1;0));",")

'------
để có thể hiểu được công thức đó hoạt động thế nào thì xin cùng "ngâm cứu" link sau:
Unique distinct list from a column sorted A to Z using array formula in excel (www.get-digital-help.com)

Link: https://www.mediafire.com/?pm0nh4aa1dt79sc

 

[concogia]

ví dụ mình có :
stt ---ten ---so luong
1------a--------5
2------c--------4
3------p--------8
4------a--------7
5------c--------2

làm như thế nào để ta có :
------bảng thống kê---------

stt ---ten ---so luong
1------a--------12
2------c--------6
3------p--------8

** tính tổng của những record có cùng tên....
nếu dùng sumif có thể tính được tổng nhưng làm sao để nó chỉ lấy ra a và số lượng = tổng???

thấy quen lắm mà tự nhiên giờ không nhớ ra phải làm sao(hình như trong sql thì phải) rối quá không nghĩ được hướng giải quyết các bạn giúp với

cảm ơn !!!

Công thức ở [B11]:

=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B11,$B$2:$B$6),0)),"")

Nhập xong bấn Ctrl + Shift + Enter
Công thức ở [A11]:

=IF(B11="","",ROW(A1))

Công thức ở [C11]:

=IF(B11="","",SUMIF($B$2:$B$6,B11,$C$2:$C$6))

Kéo xuống
Thân

 

[ChanhTQ@]

Chỉ đơn giản là mình thích xài DSUM()

Họ hàm này, với mình có tương lai khả dĩ hơn mấy cái trên kia!

 

[phucbugis]

Họ hàm này, với mình có tương lai khả dĩ hơn mấy cái trên kia!

có lẽ file nén của bác có vấn đề,
hình như hôm qua em có down 1 file của bác cũng bị và hôm nay lại gặp (vì bài kia e ko có tham gia nên ko có ý kiến )
---> bác có thể ko nén và Up lại để mọi người xem thế nào nhé !

'----
tại Win 8 nó cảnh báo như thế này.
pic1

pic2

 

[SA_DQ]

Đúng là file có virus, cả đống luôn!

Mình gởi 2 file lên VirusTotal.COM

Hắn fát hiện cả đống luôn, tên rất khác nhau!

(Mà ở đó có cả BKAV.COM nữa nha!)

 

[06th2d]

thật là có lỗi quá công trình nghiên cứu của mấy bạn mà mình nói như vậy hơi lợi dụng tí..

• tại mình đang gấp quá nên cần giải quyết vấn đề nhanh gọn, rất mong các bạn thông cảm giúp, nếu mình có gì quá đáng
• rất cảm ơn các bạn nhiệt tình hỗ trợ
- giống các ý kiến ở trên mình không down cách của ChanhTQ@ được
- cách của concogia #3 mình không hiểu chỗ:

=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B11 ,$B$2:$B$6),0)),"")

- cách của phucbugis #2 (mặc dù cung cấp đầy đủ tài liệu tham khảo) thiệt tình đúng là "ngâm cứu", ngồi cả tiếng mình vẫn chưa hình dung được nguyên lý hoạt động của nó..

***vì tình hình cấp bách,rất mong các bạn thông cảm, nếu được các bạn giải thích dùm mình 1 chút nha , rất cảm ơn các bạn, có gì không phải xin bỏ qua ,
cảm ơn !!!!

 

[06th2d]

theo công thức củaphucbugis #2

IFERROR(INDEX(List;MATCH(MIN(IF(COUNTIF($E$10:E10;List)=0;COUNTIF(List;"<"&List)+1;9,9999E+307)); COUNTIF(List;"<"&List)+1;0));",")

đếm số lần lặp của tên trong ds =0 đúng thì
COUNTIF(List;"<"&List)+1;9,9999E+307 ???
đếm số lần lặp của tên trong ds =0 sai thì
COUNTIF(List;"<"&List)+1 ???

mình không hiểu 2 chỗ này và tại sao phải đưa vùng b2:b6 thành list mục đích của việc này để làm gì ??
hàm iferror tại sao khi giá trị sai phải trả về "," ...mình muốn sai trả về "" không được ? (hàm chạy không đúng như mong muốn)

 

[06th2d]

theo công thức củaphucbugis #2

IFERROR(INDEX(List;MATCH(MIN(IF(COUNTIF($E$10:E10;List)=0;COUNTIF(List;"<"&List)+1;9,9999E+307)); COUNTIF(List;"<"&List)+1;0));",")

đếm số lần lặp của tên trong ds =0 đúng thì
COUNTIF(List;"<"&List)+1;9,9999E+307 ???
đếm số lần lặp của tên trong ds =0 sai thì
COUNTIF(List;"<"&List)+1 ???

mình không hiểu 2 chỗ này và tại sao phải đưa vùng b2:b6 thành list mục đích của việc này để làm gì ??
hàm iferror tại sao khi giá trị sai phải trả về "," ...mình muốn sai trả về "" không được ? (hàm chạy không đúng như mong muốn)

 

[phucbugis]

theo công thức củaphucbugis #2

IFERROR(INDEX(List;MATCH(MIN(IF(COUNTIF($E$10:E10;List)=0;COUNTIF(List;"<"&List)+1;9,9999E+307)); COUNTIF(List;"<"&List)+1;0));",")

đếm số lần lặp của tên trong ds =0 đúng thì
COUNTIF(List;"<"&List)+1;9,9999E+307 ???
đếm số lần lặp của tên trong ds =0 sai thì
COUNTIF(List;"<"&List)+1 ???

mình không hiểu 2 chỗ này và tại sao phải đưa vùng b2:b6 thành list mục đích của việc này để làm gì ??...

- mình cũng như bạn, lần đầu tiên thấy số 9,9999E+307 trong công thức !!!
- nói về cách dùng công thức mảng thì "rất bao la" (ko thể ngày 1 ngày 2 mà hiểu hết được các quy luật), chung quy lại nó chuyển đổi thành các giá trị TRUE, FALSE hoặc 0, 1 để kiểm tra kết quả.
- hic, vùng b2:b6 đặt Name = List --> chỉ để cho gọn công thức, chẳng có gì cao siêu cả.

hàm iferror tại sao khi giá trị sai phải trả về "," ...mình muốn sai trả về "" không được ? (hàm chạy không đúng như mong muốn)

chỗ này, có lẽ khi bạn thay đổi công thức mà quên bấm Ctrl+Shift+Enter --> ko ra kết quả (nếu là công thức mảng thì sẽ xuất hiện 2 dấu {})

 

[ChanhTQ@]

• rất cảm ơn các bạn nhiệt tình hỗ trợ
- giống các ý kiến ở trên mình không down cách của ChanhTQ@ được
. . . . .
***vì tình hình cấp bách,rất mong các bạn thông cảm, nếu được các bạn giải thích dùm mình 1 chút nha , rất cảm ơn các bạn, có gì không phải xin bỏ qua , cảm ơn !!!!

Bạn muốn xem file cần có chương trình nén & giải nén tập tin;

Mà bạn nên diệt virus đi nha,lần sau khi đưa file lên!

 

[Vu Dinh Duy]

Cách của bác concogia cần chỉnh lại một chút là Ok
=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B11,$B$2:$B$6),0)),"") sửa thành
=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$1:B1,$B$2:$B$6),0)),"")
Nhờ bác concogia mà em học thêm được cái mới. Cảm ơn bác lần nữa nhé.

 

[06th2d]

hix file xls của mình có virus ạ...???? chắc máy nó bị gì mất rồi ..minh dùng avast ...mình định down file của ChanhTQ@ xuống xem mà nó cảnh báo ...nên ngại k dám down...mình tưởng mấy bạn ở trên nói file của ChanhTQ@ bị cảnh báo chứ, vậy là file xls của mình có virus à ???

nhân tiện ChanhTQ@ cho mình hỏi cách của ChanhTQ@ dùng hàm hay viết script vậy ạ..mình chưa nghiên cứu script vbs nên .... tính hỏi ChanhTQ@
ý tưởng giải quyết vấn đề theo ChanhTQ@ là như thế nào??? mình chưa có kinh nghiệm script nên sợ không hiểu

 

[06th2d]

Vu Dinh Duy đã hiểu ,có thể giải thích chỗ

MATCH(0,COUNTIF($B$1:B1,$B$2:$B$6),0))

giúp mình được không ?

 

[06th2d]

MATCH(0
tìm vị trí có số 0 , trong vùng [????], trả về 9 xác

phần "COUNTIF($B$1:B1,$B$2:$B$6)" có mục đích làm gì vậy ạ, có phải đếm lần xuất hiện hiện của chữ tương ứng với B1 trong vùng B2:B6..
sao lúc ctrl v công thức vào cột B của mình từ B11 trở xuống đếu = 0, nên A11 và C11 cũng được bỏ ""

 

[susu16b]

Cách của bác concogia cần chỉnh lại một chút là Ok
=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B11,$B$2:$B$6),0)),"") sửa thành
=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$1:B1,$B$2:$B$6),0)),"")
Nhờ bác concogia mà em học thêm được cái mới. Cảm ơn bác lần nữa nhé.

Sửa như vậy là sai nhé!
Bạn Fill công thức của bạn xuống dưới, rồi sửa ô B3 = "a" thử xem

 

[06th2d]

giải thích công thức =IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B11,$B$2:$B$6),0)),"")
giúp mình với
"....
MATCH(0
tìm vị trí có số 0 , trong vùng [????], trả về 9 xác

phần "COUNTIF($B$1:B1,$B$2:$B$6)" có mục đích làm gì vậy ạ, có phải đếm lần xuất hiện hiện của chữ tương ứng với B1 trong vùng B2:B6..
sao lúc ctrl v công thức vào cột B của mình từ B11 trở xuống đếu = 0, nên A11 và C11 cũng được bỏ ""<----------cái này là do mình chưa nhập sorry
..."

 

[concogia]

Cách của bác concogia cần chỉnh lại một chút là Ok
........
Nhờ bác concogia mà em học thêm được cái mới. Cảm ơn bác lần nữa nhé.

Híc, công thức này không phải mình viết, hình như của Bé Còi hoặc bạn Bí Bo viết hay sao í, mình chẳng nhớ _ Bé Còi & Bí Bo thông cảm nhé
Bạn 06th2d thân. Muốn hiểu rõ công thức thì cứ chọn tuần tự các hàm ở bên trong xem nó làm việc ra sao, từ kết quả nào ra kết quả nào.....nếu mãi vẫn chưa hiểu, bí quá thì réo.......ông Ba Tê mà hỏi
Híc

 

[Vu Dinh Duy]

Công thức đúng ở ô B11 là: =IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0)),"")
Giải thích thì hơi khó nhưng vẫn cố gắng, hy vọng bạn hiểu được:

1/ COUNTIF($B$10:B10,$B$2:$B$6) đếm xem từng phần tử của mảng $B$2:$B$6 xuất hiện bao nhiêu lần trong mảng $B$10:B10 (Chú ý mảng $B$10:B10 là mảng động, sẽ thay dổi khi bạn copy công thức xuống dưới).

Ví dụ ở ô B11:
+Các phần tử của mảng $B$2:$B$6 là {"a";"c";"p";"a";"c"} sẽ là giá trị do của hàm COUNTIF. Bạn có thể kiểm tra bằng cách tô vùng $B$2:$B$6 và nhấn F9.
+Các phần tử của mảng $B$10:B10 là {"ten"} là vùng dò của hàm COUNTIF.
Hàm COUNTIF sẽ cho kết quả là {0;0;0;0;0} do không có phần tử nào xuất hiện trong vùng dò.

Ví dụ ở ô B12:
+Các phần tử của mảng $B$2:$B$6 là {"a";"c";"p";"a";"c"} sẽ là giá trị do của hàm COUNTIF.
+Các phần tử của mảng $B$10:B11 là {"ten";"a"} là vùng dò của hàm COUNTIF.
Hàm COUNTIF sẽ cho kết quả là {1;0;0;1;0} do phần tử "a" xuất hiện 1 lần trong vùng dò.

Tương tự kết quả COUNIF:
Của B13 là {1;1;0;1;1} do các phần tử "a" và "c" xuất hiện 1 lần trong vùng dò,
Của B14 là {1;1;1;1;1} do các phần tử "a","c" và "p" xuất hiện 1 lần trong vùng dò.

2/ Hàm MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0) sẽ sử dụng kết quả hàm COUNIF để làm vùng dò. Hàm Match này sẽ trả về phần tử thứ mấy của mảng là phần tử đầu tiên có giá trị bằng 0.

Ví dụ ô B11:
+ Công thức "MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0)" sẽ trở thành "MATCH(0,{0;0;0;0;0},0)" và sẽ trả kết quả là 1 do phần tử thứ nhất của mảng là phần tử đầu tiên có giá trị bằng 0.

Ví dụ ô B12:
+ Công thức "MATCH(0,COUNTIF($B$10:B11,$B$2:$B$6),0)" sẽ trở thành "MATCH(0,{1;0;0;1;0},0)" và sẽ trả kết quả là 2 do phần tử thứ hai của mảng là phần tử đầu tiên có giá trị bằng 0.

Ví dụ ô B13:
+ Công thức "MATCH(0,COUNTIF($B$10:B12,$B$2:$B$6),0)" sẽ trở thành "MATCH(0,{1;1;0;1;1},0)" và sẽ trả kết quả là 3 do phần tử thứ ba của mảng là phần tử đầu tiên có giá trị bằng 0.

Ví dụ ô B14:
+ Công thức "MATCH(0,COUNTIF($B$10:B13,$B$2:$B$6),0)" sẽ trở thành "MATCH(0,{1;1;1;1;1},0)" và sẽ báo lỗi "#N/A" do không tìm được phần tử có giá trị bằng không.

3/ Hàm INDEX lấy giá trị của MATCH để làm giá trị số hàng (Row_num). Hàm này thì đơn giản nên bạn tự nghĩ nhé.

Giải thích dài dòng, cố gắng đọc nhé.

 

[06th2d]

mình đă thử tách từng thành phần của công thức
tách đến đoạn ........COUNTIF($B$1:B1,$B$2:$B$6) thì bí ..result là "0" ....nên mình thấy khó hiểu

theo cú pháp của hàm COUNTIF ( [trong vùng],[điều kiện đếm] )
ở đây điều kiện đếm là 1 vùng tăng dần (nếu kéo xuống nữa tăng nữa)

mình không hiểu mục đ1ich của việc này ....COUNTIF($B$1:B1,$B$2:$B$6----> kéo theo không hiểu cả công thức

-------ngoài lề-----------
làm sao gửi tin sos cho ba tê nhỉ ....thông báo không thể nhận tin...hix có ai đi ngang giúp với ??? tẩu hoả nhập ma rồi

 

[Vu Dinh Duy]

Đúng là công thức của mình vẫn chưa đúng. Cảm ơn bạn Susu16b nhé.

 

[Vu Dinh Duy]

Sửa như sau mới đúng thì phải:
"=IFERROR(INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0)),"")"
Mình cũng cập nhật lại phần giải thích. Bạn 6th2d có thể xem thử.
Hôm nay học được nhiều quá, Cảm ơn mọi người.

 

[06th2d]

cảm ơn Vu Dinh Duy
phần giải thích rất rõ ràng

nhưng sao KQ không được như mình mong muốn

hàm index sẽ lấy dữ liệu tương ứng với vị trí từ hàm match

xem hình , và file 2.xls

_________________
có chỗ mình chưa hiểu trong công thức :

b11=INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0))

* kết quả hàm index sẽ xuất ra trong ô b11 ,1 chữ nào đó tương ứng với vị trí là kq của count if ...vì từ B10 đến B10 nên chưa có tên nào
=> count if trả về 0
=>match số 0 sẽ là vi trí 1 trong b2:b6
vậy phải là a ....tại sao nó #n/a

 

[Vu Dinh Duy]

Gửi bạn 06th2d,
Công thức =INDEX($B$2:$B$6,MATCH(0,COUNTIF($B$10:B10,$B$2:$B$6),0)) chỉ đúng khi bạn để ở ô B11, bạn đưa qua ô G11 thì phải sửa lại thành
=INDEX($B$2:$B$6,MATCH(0,COUNTIF($G$10:G10,$B$2:$B$6),0)) mới ra kết quả đúng.
Công thức này hay nhưng suy nghĩ dễ bị rối thật.

 

[06th2d]

Vu Dinh Duy xem lai giúp mình nha
cái hình là mình minh họa 2 công thức đều bị #n/a từ giá trị đầu tiên
bạn thấy mình đang cho hiện công thực của ô b11(cũ), ô g11 là công thức bạn cập nhật lại
bạn thấy 2 công thức đó đâu giống nhau

ý là cả hai công thức đều bị #n/a (không hiểu tại sao), mình gửi kèm file 2 để bạn text nếu thấy ra kết qảu đúng không bị #n/a thì chắc do mình dùng office 2k7(lẽ nào lại vậy)
khó hiểu quá ...
qua thêm 1 ngày mà vấn đề chưa được giải quyết ..hic

 

[Vu Dinh Duy]

MÌnh cập nhật lại công thức cho ô B11 nên bạn phải sửa lại đúng ô B11, bạn chuyển qua G11 thì phải sửa lại chứ.
mình gửi file cho bạn tham khảo.

 

[06th2d]

àhh mình hiểu tại sao nó bị #n/a rồi xem lại công thức mình thấy là :
của mình nó không có { }
công thức của Vu Dinh Duy thì có , mấy bạn trước cũng có nói đến điều này , mình thực sự chưa hiểu lắm về tại sao lại phải :
-Ctrl + shift + enter
-tại sao trong cộng thức lại xuất hiện { } đầu cuối
-đang là công thức cũ =index.... mình sửa lại thành {=index.....} cũng không được

mình thử copy công thức cũ =index.... paste vào rồi crtl+shift+enter cũng không được
làm sao ra ô b11 được như vậy