Những điều lý thú của công thức mảng. (1 người xem)

[anhtuan1066]

Những điều lý thú của công thức mãng

Lấy ví dụ tôi có dữ liệu dạng như sau:
A1 = 12 569
A2 = 5 897
A3 = 456 4
.....
Nói chung cột A toàn là số, nhưng trong số lại có chứa những khoãng trắng.. và các khoãng trắng này nằm bất kỳ vị trí nào trong số, ko có quy luật...
Vậy: Làm sao SUM dc cột A đây?
ANH TUẤN

 

[BNTT]

Nếu bác cho phép dùng cột phụ thì em làm được.
B1 = VALUE(SUBSTITUTE(A1, " ", "")) và kéo xuống.
Sau đó SUM cột B.

 

[BNTT]

Ra rồi anh Tuấn ơi! Công thức này để ở đâu cũng được, miễn là không nằm trong cột A:

{=SUM(IF(A:A>0,VALUE(SUBSTITUTE(A:A," ",""))))}​

 

[salam]

Em dùng CT này thấy cũng OK rồi.
{=SUM(VALUE(SUBSTITUTE(A1:A4," ","")))}

 

[BNTT]

Em dùng CT này thấy cũng OK rồi.
{=SUM(VALUE(SUBSTITUTE(A1:A4," ","")))}

Không OK đâu Salam à, thử xóa dữ liệu trong A3 xem...
Mới đầu mình cũng nghĩ như Salam, nhưng test lại thì nó báo #VALUE!...

 

[salam]

Không OK đâu Salam à, thử xóa dữ liệu trong A3 xem...
Mới đầu mình cũng nghĩ như Salam, nhưng test lại thì nó báo #VALUE!...

Ô đúng là #value thật. Sao excel lại không tính ô trống bằng 0 nhỉ.
Và còn số âm thì sao nhỉ - 123 344 123, -123 44 có phải thêm <>0 vào trong công thức của Bác BNTT không. để em xem lại xem.

 

[anhtuan1066]

Công thức nào cũng đúng cã...
Cũa Salam nếu muốn bỏ qua lỗi #VALUE thì thêm tí điều kiện vào... Tất nhiên cell rổng có thể xem là =0 rồi.. nhưng ko cell rỗng này làm gì có ký tự " " đễ thay thế, nên #VALUE là đương nhiên...
Còn công thức cũa BNTT thì hình như ko xài dc theo kiểu A:A đâu... Tôi cũng lấy làm ngạc nhiên khi có 1 số công thức ko dùng dc khi chọn đũ 65536 dòng... Nếu sữa lại thành A1:A65535 thì OK
Mến
ANH TUẤN

 

[BNTT]

Công thức nào cũng đúng cã...
Cũa Salam nếu muốn bỏ qua lỗi #VALUE thì thêm tí điều kiện vào... Tất nhiên cell rổng có thể xem là =0 rồi.. nhưng ko cell rỗng này làm gì có ký tự " " đễ thay thế, nên #VALUE là đương nhiên...
Còn công thức cũa BNTT thì hình như ko xài dc theo kiểu A:A đâu... Tôi cũng lấy làm ngạc nhiên khi có 1 số công thức ko dùng dc khi chọn đũ 65536 dòng... Nếu sữa lại thành A1:A65535 thì OK
Mến
ANH TUẤN

Xài cho cột A được anh à. Mà... em test trong Excel 2007 đấy, nghĩa là có đến 1.048.576 hàng chứ không chỉ có 65.535 hàng đâu.

Có điều, hơi ép! Nó phải "rùng mình" một cái rồi mới cập nhật công thức được, khi em sửa hay xóa thử một ô nào đó trong cột A.

 

[salam]

Công thức nào cũng đúng cã...
Cũa Salam nếu muốn bỏ qua lỗi #VALUE thì thêm tí điều kiện vào... Tất nhiên cell rổng có thể xem là =0 rồi.. nhưng ko cell rỗng này làm gì có ký tự " " đễ thay thế, nên #VALUE là đương nhiên...
Còn công thức cũa BNTT thì hình như ko xài dc theo kiểu A:A đâu... Tôi cũng lấy làm ngạc nhiên khi có 1 số công thức ko dùng dc khi chọn đũ 65536 dòng... Nếu sữa lại thành A1:A65535 thì OK
Mến
ANH TUẤN

Ra là tại thằng Subs mảng. Cám ơn Bác

{=SUM(IF(A:A>0,VALUE(SUBSTITUTE(A:A," ",""))))}

Phải thêm <>0 trong trường hợp mảng nhỏ hơn 0.

Tôi cũng lấy làm ngạc nhiên khi có 1 số công thức ko dùng dc khi chọn đũ 65536 dòng... Nếu sữa lại thành A1:A65535 thì OK

Cái này phải hỏi Bác Bill thôi.

 

[anhtuan1066]

Ko có đâu... bạn BNTT ghi d/k A:A>0 là đũ rồi... Vì đễ ý cột A là TEXT sẽ ko phân biệt âm dương gì đâu... Theo mặc định thì nếu ta đặt d/k rằng A1>0 thì điều kiện này cũng sẽ cho kết quã TRUE khi A1 là text... Nói tóm lại TEXT thì > 0 nên ko cần sữa lại gì cã...
Bạn BNTT cho hỏi: Bạn đã test công thức kỹ chưa? Vì trên máy cũa tôi nếu dùng A:A sẽ ra kết quã #NUM! Nhưng nếu sữa lại thành A1:A65535 thì OK
------------------------
Ah... ha... tôi biết rồi... bạn làm nó trên Excel2007... cái này thì OK, còn nếu làm trên Excel2003 trở về trước thì thua ngay cái vụ A:A
ANH TUẤN

 

[BNTT]

Vậy mới thấy Excel 2007 có nhiều ưu điểm. Em vẫn cài song song cả 2003 lẫn 2007, đề phòng trường hợp phải test trong 2003 thì có cái mà xài. Còn thì bình thường em thích dùng 2007 hơn. Ngay cả cái ribbon của nó, mới đầu em cũng chán lắm, muốn xài cái Classic Menu kia, nhưng riết hồi thì lại đâm ghiền cái ribbon hơn, hay hơn, khoa học hơn nhiều (hic).

Còn cái vụ định dạng cột A thành TEXT, em không để ý, khi tìm cách giải cái đề anh đưa ra, em cứ để mặc định như vậy (General), không định dạng gì cho cột A cả. Sau khi đọc bài của Salam, em test thử, thấy rằng nếu nhập số âm thì nó không cộng thêm, mà đúng thôi, vì em đã đặt điều kiện là nếu >0 thì mới chạy SUBSTITUDE. Nên em mới thêm cho nó cái <>0, nhằm né cái số 0 hoặc là ô trống.

Không sao, lại học thêm được một chiêu, nếu cột A là text thì chỉ cần >0 là đủ rồi. Cảm ơn anh.

 

[anhtuan1066]

Oh... tất nhiên bạn đâu cần phải định dạng cột A làm gì... Vì khi nhập liệu vào mà có khoãng trắng thì đương nhiên Excel đã xem đó là TEXT rồi còn gì... Chính vì thế mà d/k >0 sẽ luôn đúng đối với cell có dữ liệu trong trường hợp này
Mến
ANH TUẤN

 

[anhtuan1066]

Giờ thêm 1 chiêu nữa:
Cột A sẽ có dử liệu dạng
gg256
tt54
se5986
....
Nói chung dử liệu là 1 TEXT với 2 ký tự đầu luôn là text, còn phần sau nó toàn là số viết liền nhau ko có khoãng trắng nào...
Giờ hãy tách ra số và SUM chúng lại với nhau
Hãy dùng công thức mãng và ko cho phép cột phụ nha
Mến
ANH TUẤN

 

[BNTT]

Có liền, anh Tuấn ơi, vừa làm xong mà:

{=SUM(IF(A1:A100 <> "", VALUE(RIGHT(A1:A100, LEN(A1:A100) - 2))))}​

Làm từ A1:A100 thôi cho nó chạy lẹ lẹ tí. Chứ nếu dùng A:A thì cũng OK.

 

[salam]

Oh... tất nhiên bạn đâu cần phải định dạng cột A làm gì... Vì khi nhập liệu vào mà có khoãng trắng thì đương nhiên Excel đã xem đó là TEXT rồi còn gì... Chính vì thế mà d/k >0 sẽ luôn đúng đối với cell có dữ liệu trong trường hợp này
Mến
ANH TUẤN

Bác Tuấn ơi em không định dạng cột A là text thì khi em gõ - 12 345 55 thì nó thành luôn số -123455 không có dấu cách à.

 

[BNTT]

Cái này là do đôi khi Excel coi dấu trừ (-) giống như dấu bằng (=)
Bạn đọc kỹ cái thông báo sau khi bạn gõ -123 456 vào, nó có bày mà.
Là dùng thêm một cái dấu móc đơn (') trước dấu trừ.

 

[anhtuan1066]

Hi... hi... Hay lắm...
Giờ nếu thêm 1 chiêu nữa... số lượng ký tự là TEXT tại đầu chuổi là ko biết trước, số lượng ký tự là NUMBER cũng ko biết trước... ví dụ:
A1 = hhh25
A2 = ht122
A3 = m1234
.... vân vân....
Cho biết trươc: Đầu chuổi luôn là text, cuối chuổi là số... chuổi dc viết liền nhau ko có khoãng trắng nào...
Vẫn yêu cầu trên: tách lấy số rồi cộng chúng lại...
Hơi khó 1 chút, các bạn cố gắng lên...
Gợi ý là dùng MID đễ lấy ra từng ký tự một... tiếp theo lồng hàm VALUE vào kết quã vừa ra... rồi sau đó lồng ISNUMBER đễ kiểm tra xem cái nào là số...
Tôi làm sẳn file gợi ý... Nhiệm vụ còn lại là
1> bỏ các cột phụ này..
2> làm sao cho trong mãng có dc số chạy từ 1 đến cuối chuỗi..
3> Xác định vị trí cũa TRUE đầu tiên trong bãng
4> Đếm xem có bao nhiêu TRUE
Kết hợp lại tất cã sẽ ra dc 1 công thức hoàn chỉnh
Mến
ANH TUẤN

 

[salam]

Nếu dùng cột phụ thì em làm thế này (ở file kèm) bác xem có được không.

 

[anhtuan1066]

Nếu dùng cột phụ thì em làm thế này (ở file kèm) bác xem có được không.

Bạn Salam làm đúng rồi... Giờ chỉ cần thêm bẩy lỗi cell rổng và các trường hợp cell toàn là Number hoặc cell toàn là Text...
Các bạn khác hãy tìm cho mình 1 giãi pháp.. Vì bài toán luôn luôn tồn tại nhiều cách giãi khác nhau...
Mến
ANH TUẤN

 

[BNTT]

Công thức của bạn SALAM bên cột B là:

VALUE(REPLACE(A1,1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,""))
​

Vậy nếu để bẫy lỗi cho những ô trống hoặc ô chứa toàn text thì, tôi nghĩ cách lẹ nhất là dùng ISERROR theo dạng IF(ISERROR(x),0,x)

IF(ISERROR(VALUE(REPLACE(A1,1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,""))),0,
REPLACE(A1,1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,"")))
​

Còn ô toàn số thì khỏi cần bẫy lỗi.

Nếu sử dụng Excel 2007, thì công thức trên sẽ ngắn hơn nhiều:

IFERROR(REPLACE(A1,1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1,"")), 0)
​

IFERROR là hàm riêng của Excel 2007, có dạng IFERROR(x, y), nghĩa là nếu x không có lỗi thì lấy x, còn nếu x có lỗi thì lấy y

 

[ThuNghi]

=IF(OR($A1=0,$A1="",ISERROR(RIGHT($A1,1)*1)),0,RIGHT($A1,SUMPRODUCT((CODE(RIGHT($A1,ROW(INDIRECT("1:"&LEN($A1)))))<58)*(CODE(RIGHT($A1,ROW(INDIRECT("1:"&LEN($A1)))))>47)))*1)

Text hộ công thức này thử

 

[salam]

Text hộ công thức này thử

CT thức OK nhưng copy vào thì bị có dấu cách. Là lỗi gì thế nhỉ.

 

[kongcom]

Cái dấu cách sau số 1 kia kià . Mình rất hay bị cái lối này khi copy paste từ GPE .... Không hiểu sao vậy nhỉ . Có lần mình tưởng nhầm là không cho vào thẻ Code nó thế hoá ra cũng không phải vậy .

 

[anhtuan1066]

Tôi nghĩ nên đặt name cho nó đở lóa mắt

DEM =SUM(ISNUMBER(MID(Sheet1!$A1,ROW(INDIRECT("1:"&LEN(Sheet1!$A1))),1)*1)*1)

Chú ý rằng công thức chủ lực trong đây chính là tách chuổi ra thành từng ký tự.. là cái này đây:

=MID(Sheet1!$A1,ROW(INDIRECT("1:"&LEN(Sheet1!$A1))),1)

Các bạn có thể click chọn công thức trên thanh Formula rồi bấm F9 đễ theo dõi...
Và công thức trên hoàn toàn giống với vòng lập FOR trong VBA... hàm SUM lồng bên ngoài đễ đếm xem mãng này có bao nhiêu phần tử TRUE
Đễ ý rằng name DEM sẽ có giá trị = 0 khi chuổi ko chứa số.. nên cuối cùng là công thức tại cột B sẽ như sau:

=IF(DEM=0,0,RIGHT(A1,DEM)*1)

-------------------------------
Qua công việc giãi quyết bài toàn trên, ta hãy thêm vào điều kiện rằng số số thể nằm bất cứ nơi đâu trong chuổi, miễn sao dãy số là liên tục... Vậy hãy tách lấy số trong chuổi đấy nhé
Mến
ANH TUẤN

 

[tigertiger]

CT thức OK nhưng copy vào thì bị có dấu cách. Là lỗi gì thế nhỉ.

Cái dấu cách sau số 1 kia kià . Mình rất hay bị cái lối này khi copy paste từ GPE .... Không hiểu sao vậy nhỉ . Có lần mình tưởng nhầm là không cho vào thẻ Code nó thế hoá ra cũng không phải vậy .

Nhưng nếu đặt vào thẻ

 ..... ["/"PHP]  (BỎ dấu nháy của "/" nhé)
thì công thức, hay cả code cũng ko bọ cách mà lại còn đổi màu đẹp nữa, phát hiện ra cái này -> khi có công thức, hay code đưa lên diễn đàn tigertiger toàn dùng thẻ PHP này thôi, rất hay đó

 

[tigertiger]

DEM =SUM(ISNUMBER(MID(Sheet1!$A1,ROW(INDIRECT("1:"&LEN(Sheet1!$A1))),1)*1)*1)

oh, thế này thì atuan chỉ tách số hay rồi nhỉ???
đâu có tính tổng hay gì đâu???

 

[anhtuan1066]

Giờ xin đưa ra 1 bài toán khác khó hơn, cũng vẫn dùng công thức mãng đễ tách số:
Giã sử tôi có dử liệu như sau:
A1 = h3k7h2
A2 = 4h2g7r
A3 = 9d0h2k8
A4 = h3d7k9h
....... vân vân....
Chuổi trên theo nguyên tắc:
1> Ko có khoãng trắng nào trong chuổi
2> Number và Text nằm xen kẽ nhau, hết num thì text hoặc hết text thì num
3> Ko biết trước đầu chuổi là number hay text
4> Độ dài chuổi chưa biết trước
Yêu cầu: Tách lấy số và ráp lại, chẳng hạn khi tách xong thì:
B1 = 372
B2 = 427
B3 = 9028
B4 = 379
... vân vân...
Giã sử ta đã tạo dc 1 mãng gồm toàn number thì bài toán này vẫn còn khó ở chổ phải ráp chúng lại với nhau...
Mời các cao thủ xuất chiêu...
Mến
ANH TUẤN

 

[anhtuan1066]

Hình như cũng hơi khó nhỉ? Xin gợi ý:
1> Kiễm ta ký tự đầu tiên là TEXT hay NUMBER, ta đặt name

KT =ISNUMBER(MID(Sheet1!$A1,1,1)*1)

2> Đếm xem trong chuổi có bao nhiêu số, đặt name:

DEM =SUM(ISNUMBER(1*MID(Sheet1!$A1,ROW(INDIRECT("1:"&LEN(Sheet1!$A1))),1))*1)

3> Tách lấy số bằng công thức mãng

{=1*MID($A1,2*ROW(INDIRECT("1:"&DEM))-KT,1)}

Hãy bôi đen công thức này trên thanh Formula rồi F9 đễ thấy kết quả tách số
4> Cuối cùng là ráp các số ấy lại bằng hàm SUMPRODUCT hoặc SUM nếu muốn dùng mãng... ???...
Công đoạn cuối cùng này hy vọng các bạn có thể nghĩ ra dc...
Chúc thành công!
Mến
ANH TUẤN

 

[salam]

Cái Bác Tuấn này nói về name thì khó có ai qua được nhỉ. Sao bác không mở một top hướng dẫn chi tiết về name cho bọn em học hỏi thêm.

 

[ThuNghi]

4> Cuối cùng là ráp các số ấy lại bằng hàm SUMPRODUCT hoặc SUM nếu muốn dùng mãng... ???...

Chắc AT1066 làm luôn đi, tôi thấy công thức này khó làm mà cũng khó hiểu nữa so với VBA.

 

[TranThanhPhong]

3> Tách số:
{=1*MID(Sheet1!$A1,2*ROW(INDIRECT("1:"&DEM))-KT,1))}[/code]

Lưu ý bước 3 chỉ đúng khi số và chữ xen kẽ nhau. Còn làm bước 4 thì em nghĩ như thế này chắc ổn.

=SUMPRODUCT(10^(DEM-ROW(INDIRECT("1:"&DEM))*1),(1*MID(Sheet1!$A1,2*ROW(INDIRECT("1:"&DEM))-KT,1)))

Chúng ta cùng nghĩ tiếp giải quyết lại bước 3 sao cho đúng mọi trường hợp số và chữ xen nhau bất kỳ nhé!

TP.

 

[tigertiger]

Lưu ý bước 2 chỉ đúng khi số và chữ xen kẽ nhau. Anh Tuấn Xem lại nhé.

Còn làm bước 4 thì em nghĩ như thế này chắc ổn (giả sử bước 3 đúng rồi).

Nếu thế đối với SUM dùng công thức mảng, thì như sau:

=SUM(10^(DEM-ROW(INDIRECT("1:"&DEM))*1)*(1*MID(Sheet1!$A1,2*ROW (INDIRECT("1:"&DEM))-KT,1)))

 

[anhtuan1066]

Cãm ơn... đúng cả rồi... quan trong là mãng 10^....
Có điều xin bổ sung: Đúng là nhiều khi suy nghĩ quá xa xôi... hì... hì... cái name DEM ấy tôi xin sữa lại gọn hơn:

DEM =INT((LEN(Sheet1!$A1)+KT)/2)

(Gữi file cho các bạn tham khảo)
---------------------------
Với Phong:

Lưu ý bước 3 chỉ đúng khi số và chữ xen kẽ nhau. Còn làm bước 4 thì em nghĩ như thế này chắc ổn

Thì đề bài tôi cho như thế mà...
---------------------------
Tôi đang nghiên cứu vấn đề nếu số nằm rãi rác lung tung trong chuổi thì theo hướng này có tách ra và ráp lại dc ko?... Thấy cũng khó ghê... ThuNghi.. Phong.. Tigertiger.. BNTT.. Salam và tất cả các bạn phụ giúp 1 tay với... Cãm ơn nhiều
Mến
ANH TUẤN

 

[anhtuan1066]

Đã có giãi pháp về việc tách số trong 1 chuổi lộn xộn...
Tôi dùng các name sau:

TIM1 =ISNUMBER(1*MID(Sheet1!$A2,ROW(INDIRECT("1:"&LEN(Sheet1!$A2))),1))

TIM =IF(TIM1=FALSE,"",(ROW(INDIRECT("1:"&LEN(Sheet1!$A2))))*(TIM1))

DEM =SUM((TIM1)*1)

VT =SMALL(TIM,ROW(INDIRECT("1:"&DEM)))

......
Và cuối cùng là công thức tại B2

=IF(DEM=0,"",SUMPRODUCT((1*MID(A2,VT,1))*(10^(COUNT(TIM)-ROW(INDIRECT("1:"&COUNT(TIM)))))))

Mời các tham khảo file và xem thử có cách nào gọn hơn ko? Vì tôi vẫn có cãm giác mấy name này hơi thừa, có thể rút gọn dc nhưng tạm thời chưa nghĩ ra dc sẽ rút gọn chổ nào...
----------------------------------------------
Xin nói thêm 1 chút: Thật sự cãm ơn bạn ttphong2007... vì kiến thức tạo name giống như vầy là tôi học dc từ bạn ấy, từ file trích xuất dử liệu duy nhất ko dùng cột phụ mà hiện nay các bạn đang áp dụng và trã lời cho rất nhiều topic
Một lần nữa chân thành cãm ơn bạn ttphong2007
Mến
ANH TUẤN

 

[TranThanhPhong]

Cách nghiên cứu Công thức mảng

Chào bà con cô bác anh chị,

Theo em thì để tiếp cận công thức mảng tốt trước tiên cần phải nắm 3 vấn đề sau:

1/ Nắm rõ cách sử dụng các hàm (thông thường thôi, riêng em thì nhớ sẵn trong đầu khoảng 30% số hàm hiện có của Excel). Hàm nào ít sử dụng thì khi nào đụng chuyện ta sẽ nghiên cứu sau. Để làm được điều này có thể đọc loạt bài gần đây của anh BNTT về công thức và hàm

2/ Hiểu và làm được thành thạo việc sử dụng các công thức lồng ghép nhiều hàm với nhau. Để làm được điều này các bạn có thể thực hành các bài tập trong chủ đề Học hàm Excel thông qua bài tập. Còn bạn nào muốn giỏi mà không chịu khổ luyện thì đợi đến tết "Công Gô" sẽ giỏi thôi

3/ Học các qui tắc của hàm mảng để hiểu cách thức hoạt động và cách bố trí dữ liệu trong bộ nhớ tạm. Các bạn có thể xem bài viết này tại đây

Sau khi nắm rõ 3 vấn đề trên thì hãy nghĩ tới chuyện vận dụng công thức mảng để giải quyết các bài toán, còn không thì phải nhờ anh Tuấn, anh ThuNghi viết dùm cho !

Ngoài ra để hiểu bài tách số ra khỏi chuổi bên dưới của anh Tuấn các bạn phải hiểu thêm về Name nữa. Các bạn theo dõi chủ đề mà anh tigertiger đang viết tại đây

Muốn thấy được trực quan cách hoạt động của công thức mảng, các bạn nên dùng tính năng có sẵn của Excel là Evaluate Formula:

Excel 2003 --> trước:
Chọn ô kết quả được tính ra từ 1 công thức mảng cần xem cách tính của nó rồi vào Tools | chọn Formula Auditing | chọn Evaluate Formula | rồi nhấn nút Evaluate từng bước xem hàm hoạt động như thế nào.

Excel 2007:
Chọn ô kết quả được tính ra từ 1 công thức mảng cần xem cách tính của nó rồi vào Ribbon | chọn ngăn Formulas | tại nhóm Formula Auditing | chọn Evaluate Formula | rồi nhấn nút Evaluate từng bước xem hàm hoạt động như thế nào.

Lưu ý: sau mỗi lần nhấn Evaluate các bạn phải nghĩ trong đầu là tại sao Excel tính ra kết quả đó. Sau này quen dần "cái đầu" của mình sẽ tự tính nhẩm và mình sẽ không cần đến Evaluate Formula nữa!

Một số lời khuyên cho các bạn muốn học công thức mảng!

Thanh Phong

 

[anhtuan1066]

Cho thêm 1 bài toán đơn giãn:
Dùng công thức mãng đễ xác định dử liệu trong 1 vùng (chẳng hạn từ A1:A20) có phải là dử liệu duy nhất hay ko? (Tức là có hay ko có dử liệu trùng ko vùng này)
ANH TUẤN

 

[BNTT]

Cho em thử nhé:

{=IF(SUM(COUNTIF($A$1:$A$20, A1:A20)) - COUNTA(A1:A20) > 0, "Có dữ liệu trùng trong vùng này", "Không có dữ liệu trùng trong vùng này")}
​

Sẵn trớn em thử luôn cho mảng nhiều cột nhiều hàng (ví dụ A1:C20)

{=IF(SUM(COUNTIF($A$1:$C$20, A1:C20)) - COUNTA(A1:C20) > 0, "Có dữ liệu trùng trong vùng này", "Không có dữ liệu trùng trong vùng này")}
​

Tuy nhiên, nó không phân biệt được chữ thường và chữ hoa.
Ví dụ, trong mảng này nếu vừa có "a" vừa có "A" thì nó báo là có dữ liệu trùng.
Muốn nó phân biệt "a" với "A" thì phải thêm cái chi nữa ?

 

[kongcom]

Nhìn lại đề bài thấy mình đi sai hướng . Xâu hổ quá

Bài này dùng để đếm số hàng có giá trị B1 trong mảng
Giả sử giá trị cần tìm là cell B1
tại ô kết quả mình ghi
{=SUM(IF(FREQUENCY(IF(A1:A20=B1,ROW(A1:A20)), IF(A1:A20=B1,ROW(A1:A20)))>0,1,0))}
Nó sẽ cho biết trong dãy kia có bao nhiêu giá trị B1 . Có phải không ANHTUAN ?

Cái này mình test lại thấy đúng cả trong nhiều cột và cả giá trị nội suy nữa .

 

[anhtuan1066]

Hãy dùng công thức mãng COUNTIF(Vung,Vung)... sau đó bôi đên công thức này trên thanh Formula và bấm F9 đễ xem có gì trong đó... chú ý khi Vung có dử liệu trùng và khi vùng ko có dử liệu trùng thì điều gì thay đỗi...
Bám vào đây sẽ ra ngay!
Mến
ANH TUẤN

Vậy cái công thức ở #41 của em đã đúng với đề bài bác ra chưa?

Về thuật toán thì hoàn toàn đúng... nhưng có lẽ bạn ko đễ ý: Bôi đen công thức COUNTIF(Vung, Vung) trên thanh Formula và bấm F9 sẽ thấy rằng: nếu ko có dử liệu trùng thì mãng này toàn số 1, ngược lại sẽ có sự xuất hiện cũa số lớn hơn 1
Vậy có phải nên làm theo cách gọn hơn: MAX(COUNTIF(...)) ko?
Nếu có dữ liệu trùng thì MAX này luôn > 1, ngược lại MAX cho giá trị = 1
He... he... ko cần SUM rồi COUNTA tùm lum... thừa...
ANH TUẤN

 

[BNTT]

Bác Kongcom ơi, nếu chỉ để đếm số lần xuất hiện của 1 giá trị trong một mảng thì đâu có cần dùng đến công thức mảng ?

Ví dụ để đếm số lần xuất hiện của giá trị tại B1 trong mảng A1:A20, chỉ cần gõ:

= COUNTIF(A1:A20, B1)​

 

[BNTT]

Hãy dùng công thức mãng COUNTIF(Vung,Vung)... sau đó bôi đên công thức này trên thanh Formula và bấm F9 đễ xem có gì trong đó... chú ý khi Vung có dử liệu trùng và khi vùng ko có dử liệu trùng thì điều gì thay đỗi...
...Về thuật toán thì hoàn toàn đúng... nhưng có lẽ bạn ko đễ ý: Bôi đen công thức COUNTIF(Vung, Vung) trên thanh Formula và bấm F9 sẽ thấy rằng: nếu ko có dử liệu trùng thì mãng này toàn số 1, ngược lại sẽ có sự xuất hiện cũa số lớn hơn 1
Vậy có phải nên làm theo cách gọn hơn: MAX(COUNTIF(...)) ko?
Nếu có dữ liệu trùng thì MAX này luôn > 1, ngược lại MAX cho giá trị = 1, không cần SUM rồi COUNTA tùm lum... thừa...

Cảm ơn anh. Có vậy mới học thêm được vài chiêu chứ. Vậy em sửa nha:

Công thức cơ bản (không có báo kết quả):
​

{MAX(COUNTIF($A$1:$C$20, A1:C20), 0)}

Công thức có báo kết quả:
​

{=IF(MAX(COUNTIF($A$1:$C$20, A1:C20), 0) > 1, "Có dữ liệu trùng trong vùng này", "Không có dữ liệu trùng trong vùng này")}
​

Đúng chưa anh ?

Nhưng còn chuyện em hỏi: Làm sao cho nó phân biệt giữa chữ thường và chữ hoa ? Anh gợi ý tiếp đi.

 

[ThuNghi]

Dạ thưa mấy anh, đúng rồi. Mà các ct này có trên GPE rồi. Và có thể dùng sumproduct.

Tìm trong những ct độc đáo (SoiBien)
=Sumproduct((1/countif(Rng,Rng))*1)
Hay là
if(SUMPRODUCT((COUNTIF(Rng,Rng)>1)*1)>0,"Y","N")
Chắc khỏi cần GT nhỉ.

Ct của BNTT có sai ở chữ thừơng hay chữ hoa đâu. Sao tôi không thấy.

 

[anhtuan1066]

Ũa... sao lại cho số 0 vào làm gì nhỉ?

[COLOR=DarkRed]={IF(MAX(COUNTIF($A$1:$C$20, A1:C20)) > 1..... )}

[/COLOR]là đũ rồi mà...
Phân biệt chử HOA và thường? Tôi chưa hiểu lắm... Ý bạn là sao? Nói rõ hơn chút đi...
ANH TUẤN

 

[BNTT]

@ Bác ThuNghi:

Công thức này cũng đúng:

=IF(SUMPRODUCT((COUNTIF($A$1:$C$20,A1:C20)>1)*1)>0,"Y","N")
​

Nhưng bác so độ dài với cái này xem (em đã bỏ bớt hai cái {} ở hai đầu):

=IF(MAX(COUNTIF($A$1:$C$20,A1:C20),0)>1,"Y","N")
​

Cái của em ngắn hơn.

Ct của BNTT có sai ở chữ thừơng hay chữ hoa đâu. Sao tôi không thấy.

Em không nói là nó sai. Em muốn nó phân biệt được.
Ví dụ, nếu có "a" và "A" thì phải báo là "không có dữ liệu trùng"
Trong công thức của em, nếu có "a" và "A" thì nó nói là "có dữ liệu trùng".

--------------
@Bác ANHTUAN1066

Ồ, lại phải cảm ơn nữa. Và như vậy thì cái công thức của em lại ngắn thêm một chút nữa (so với cái của bác ThuNghi):

{=IF(MAX(COUNTIF($A$1:$C$20, A1:C20)) > 1, "Có dữ liệu trùng trong vùng này", "Không có dữ liệu trùng trong vùng này")}
​

Em chỉ nghĩ đơn giản là so sánh COUNTIF với số 0... mà quên mất trong công thức mảng thì không cần.

Về chuyện chữ thường và chữ hoa, anh xem ở trên đây nha.

 

[BNTT]

Thưa bác, đôi khi cũng cần chứ. Vì đã nói là "không có dữ liệu trùng" cơ mà. Giống như là gõ password vậy. Đã xét không trùng thì ta tìm cách xét cho tới nơi luôn, tánh em thì hay thích đã làm gì là làm tới nơi tới chốn, đã cãi là cãi cho bằng được... trừ phi, đôi lúc khó quá, thì mới thôi.
Trường hợp này thì tại vì chúng ta không muốn cho anh chàng VBA chen vào, nên cũng hơi cực. Để em nghĩ thêm chút nữa xem.

Mà... chắc không được rồi, vì Excel nó cũng đâu có phân biệt chữ hoa chữ thường khi ta gõ hàm hoặc nhập địa chỉ của ô đâu...

 

[anhtuan1066]

Câu hỏi tiếp theo:
Tôi có 2 vùng dữ liệu:
Vùng 1: A1:A20
Vùng 2: B1:B20
Dùng công thức mãng đễ kiễm tra nhanh xem dữ liệu 2 vùng này có trùng hay ko?
Toàn bộ dữ liệu trong vùng 1 phải khác hoàn toàn với vùng 2 thì mới dc xem là ko trùng
(Ví dụ A1 = 5 và B19 cũng = 5 ---> Vùng 1 có trùng với vùng 2)
ANH TUẤN

 

[vspvietnam]

Ra rồi anh Tuấn ơi! Công thức này để ở đâu cũng được, miễn là không nằm trong cột A:

{=SUM(IF(A:A>0,VALUE(SUBSTITUTE(A:A," ",""))))}​

Nếu công thức như bạn thì các ô mà ở cột A không có giá trị sẽ cho giá tri là 0 theo tôi chỉ cần dùng CT sau
=IF(A:A>0,VALUE(SUBSTITUTE(A:A," ","")),"")

 

[BNTT]

Nếu công thức như bạn thì các ô mà ở cột A không có giá trị sẽ cho giá tri là 0 theo tôi chỉ cần dùng CT sau
=IF(A:A>0,VALUE(SUBSTITUTE(A:A," ","")),"")

Bạn đọc kỹ đề bài chưa ?
Công thức bạn đưa ra ở trên, bạn đã test kỹ chưa?
Nó có tính tổng hết những con số trong cột A không ?
Khi bạn thay đổi hay thêm bớt gì đó trong cột A, kết quả của công thức bạn viết ở trên có thay đổi gì không ?

 

[BNTT]

Câu hỏi tiếp theo:
Tôi có 2 vùng dữ liệu:
Vùng 1: A1:A20
Vùng 2: B1:B20
Dùng công thức mãng đễ kiễm tra nhanh xem dữ liệu 2 vùng này có trùng hay ko?
Toàn bộ dữ liệu trong vùng 1 phải khác hoàn toàn với vùng 2 thì mới dc xem là ko trùng
(Ví dụ A1 = 5 và B19 cũng = 5 ---> Vùng 1 có trùng với vùng 2)
ANH TUẤN

Anh Tuấn ơi, nếu giữa một cột và một hàng thì được:

{=IF(OR(A1:A20=B1:U1), "Có dữ liệu trùng giữa hai vùng này", "Không có dữ liệu trùng giữa hai vùng này")}
​

Nhưng cũng công thức đó, tại sao giữa hai cột với nhau, hoặc hai hàng với nhau, hoặc nhiều hơn là giữa hai mảng với nhau, phải "giống nhau ở cùng vị trí tương ứng" thì nó mới báo là có dữ liệu trùng, còn "giống nhau mà không cùng vị trí tương ứng" thì nó không tính là có dữ liệu trùng nhau?

 

[minhlev]

Xin góp một công thức với câu hỏi mới của bác Tuấn về tìm dữ liệu trùng giữa 2 vùng (A1:B20) với (C1:C20)

{=IF(COUNTIF(C1:C20,A1:B20)>0,"Có dữ liệu trùng","Không có dữ liệu trùng")}

------
Công thức trên vẫn chưa chính xác.

 

[anhtuan1066]

Anh Tuấn ơi, nếu giữa một cột và một hàng thì được
Nhưng cũng công thức đó, tại sao giữa hai cột với nhau, hoặc hai
hàng với nhau, hoặc nhiều hơn là giữa hai mảng với nhau, phải
"giống nhau ở cùng vị trí tương ứng" thì nó mới báo là có dữ liệu
trùng, còn "giống nhau mà không cùng vị trí tương ứng" thì nó không
tính là có dữ liệu trùng nhau?

He... he... Đây chính là sự tinh tế khi dùng mãng... Nên biết rằng
mãng có 2 loại: Mãng ngang và mãng dọc, giống như dử liệu dc sắp xếp thành cột hoặc hàng vậy... (Hãy tham khão bài: "Tự làm 1 tờ lịch đơn giãn" đễ biết thêm về mãng ngang, dọc, ngay name TUAN và ST ấy)
Khi bạn so sánh bằng toán tử OR giữa 2 mãng cùng chiều thì nó sẽ
lấy từng phần tử tương ứng so với nhau... Bạn hãy đễ ý =OR
(A1:A3=C1:C3)... công thức này nếu bôi đen nó trên thanh Formula rồi
F9 sẽ thấy có 3 phần tử... nhưng nếu OR giữa 2 mãng khác chiều thì
nó sẽ lấy từng phần tử cũa mãng này đi so sánh với từng em trong
mãng kia, đến hết nó lại lấy phần tử thứ 2 cũa mãng này so sánh với
từng em trong mãng kia... cứ thế và cứ thế đến hết... nên kết quả thu
dc sẽ là n1 x n2 phần tử (n1 = số phần tử mãng 1 và n2 = số phần tử mãng 2)... Ko tin bạn cứ bôi đen công thức =OR
(A1:A3=C1:E1) trên thanh Formula rồi F9 xem có phải là có tổng cộng
9 phần tử TRUE, FALSE ko?
Nếu vậy thì... nhiệm vụ cũa bạn phải là: Xoay 1 trong 2 mãng kia 90
độ, rồi dùng toán tử OR để so cựa... Xoay thế nào đây? He... he... Tôi
thì sẽ xoay nó bằng hàm TRANSPOSE đấy!
Tuy nhiên nếu dùng TRANSPOSE sẽ có giới hạn, các bạn hãy tìm xem giới hạn công thức là bao nhiêu nhé! Và đây chính là cái mà tôi muốn nói trong bài tập này
Với công thức cũa minhlev thì... hướng đi là rất chính xác... dù cho
bạn có Ctrl + Shift + Enter hay chỉ Enter thì công thức này vẫn là
mãng... Bạn cũng bôi đen công thức và F9 đễ xem từng phần tử trong
mãng là cái gì? Đễ ý nếu có trùng thì trong mãng sẽ có TRUE, đúng
ko? Giờ bạn lồng thêm SUM vào là dc: SUM((COUNIF(.....)>0)*1) đễ đếm TRUE... hoặc cũng có thể là MATCH(TRUE,công thức của bạn ,0) đễ tìm TRUE... vân vân và vân vân... thiếu gì cách
Mến
ANH TUẤN

 

[tuylip2568]

chào anh! sao em vào mấy cái link của anh cho trong trang học công thức mảng nhưng copy về không đọc được. em đã cài winrar nhưng vẫn không mở được. Hướng dẫn hộ em với nhé.

 

[anhtuan1066]

Lâu quá ko bàn về mãng rồi... Hôm nay tôi muốn tiếp tục với 1 đề tài tuy ko mới nhưng lại khá thời sự, đó là việc TÍNH TUỔI... Bài toán đặt ra như sau:
Biết ngày tháng năm sinh của 1 người, hảy tính tuổi chính xác của người ấy mà ko dùng hàm DATEDIF
(đã gọi là TÍNH TUỔI thì đương nhiên tính tới thời điểm hiện tại)

Ai chà chà... rắc rối quá nhỉ? Ko thấy ai quan tâm... Hay là vì đã có DATEDIF nên bài toán này trở nên ko cần thiết ??? Hic...
ANH TUẤN

 

[BNTT]

Mấy hôm nay không vào Internet được chứ hổng phải hông quan tâm.
Em đóng góp một cái đây, mà tại vì anh không cho dùng DATEDIF nên nó hơi dài tí:

= YEAR(NOW()) - YEAR(Birthday) - (DATE(YEAR(NOW()), MONTH(Birthday), DAY(Birthday)) > TODAY())​

Birthday là ngày tháng năm sinh đã biết.

 

[Cao Mạnh Sơn]

Lâu quá ko bàn về mãng rồi...
ANH TUẤN

Chắc bác Tuấn đang chuẩn bị có chiêu mới .Cho em hỏi chút dùng hàm datedif để tính tuổi có khác gì khi dùng công thức(today()-ngày sinh)/365.Nhưng hàm datedif em cũng chưa sử dụng.Em tìm trong functions của excel cũng không thấy

 

[luonchinguoc]

Chắc bác Tuấn đang chuẩn bị có chiêu mới .Cho em hỏi chút dùng hàm datedif để tính tuổi có khác gì khi dùng công thức(today()-ngày sinh)/365.Nhưng hàm datedif em cũng chưa sử dụng.Em tìm trong functions của excel cũng không thấy

Hàm DATEDIF()

Có lẽ cách dễ nhất khi muốn tính toán ngày tháng năm là dùng hàm DATEDIF().
Nhưng có một điều tôi không hiểu là: hàm này dùng tốt, nhưng không hề có trong danh sách hàm của Excel (Excel 2007 cũng không), và cũng không có một cái help nào cho nó cả! Cho nên, có một số người sử dụng Excel phải nói là có thâm niên, mà vẫn không hề biết Excel có hàm DATEDIF()...

Hàm này đây bác:

Link gốc: http://www.giaiphapexcel.com/forum/showpost.php?p=51019&postcount=13

Nội dung:

Cú pháp: = DATEDIF(start_day, end_day, unit)

start_day: Ngày đầu

end_day: Ngày cuối (phải lớn hơn ngày đầu)

unit: Chọn loại kết quả trả về (khi dùng trong hàm phải gõ trong dấu ngoặc kép)

y : số năm chênh lệch giữa ngày đầu và ngày cuối

m : số tháng chênh lệch giữa ngày đầu và ngày cuối

d : số ngày chênh lệch giữa ngày đầu và ngày cuối

md : số ngày chênh lệch giữa ngày đầu và tháng ngày cuối, mà không phụ thuộc vào số năm và số tháng

ym : số tháng chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm và số ngày

yd : số ngày chênh lệch giữa ngày đầu và ngày cuối, mà không phụ thuộc vào số năm

​

Ví dụ:

DATEDIF("01/01/2000", "31/12/2100", "y") = 100 (năm)

DATEDIF("01/01/2000", "31/12/2100", "m") = 1211 (tháng)

DATEDIF("01/01/2000", "31/12/2100", "d") = 36889 (ngày)

DATEDIF("01/01/2000", "31/12/2100", "md") = 30 (= ngày 31 - ngày 1)

DATEDIF("01/01/2000", "31/12/2100", "ym") = 11 (= tháng 12 - tháng 1)

DATEDIF("01/01/2000", "31/12/2100", "yd") = 365 (= ngày 31/12 - ngày 1/1)
​

• Tính tuổi (2):

Ở bài trước, tôi đã đưa ra một cái công thức để tính tuổi dài thoòng như vầy:

= YEAR(NOW() - YEAR(Birthdate) - (DATE(YEAR(NOW()), MONTH(Birthdate), DAY(Birthdate)) > TODAY())
​

Đó là khi chưa biết đến hàm DATEDIF().
Bây giờ, với DATEDIF(), công thức trên chỉ ngắn như vầy thôi, mà ra kết quả vẫn chính xác:

= DATEDIF(Birthdate, TODAY(), "y")
​

Ví dụ, hôm nay là ngày 09/01/2007:

• Với ngày sinh là 05/01/1969 (đã tổ chức sinh nhật rồi), DATEDIF("05/01/1969", TODAY(), "y") = 39
  

• Nhưng với ngày sinh là 11/1/1969 (chưa tổ chức sinh nhật), DATEDIF("11/01/1969", TODAY(), "y") = 38

Em copy sang đây luôn cho nhanh

 

[anhtuan1066]

Mấy hôm nay không vào Internet được chứ hổng phải hông quan tâm.
Em đóng góp một cái đây, mà tại vì anh không cho dùng DATEDIF nên nó hơi dài tí:

= YEAR(NOW()) - YEAR(Birthday) - (DATE(YEAR(NOW()), MONTH(Birthday), DAY(Birthday)) > TODAY())​

Birthday là ngày tháng năm sinh đã biết.

Có rất nhiều cách để tính tuổi (chính xác nha).. như những công thức sau:
1> DATEDIF(Birthday,TODAY(),"Y")
2> INT(YEARFRAC(Birthday,TODAY()))
3> YEAR(TODAY())-YEAR(Birthday)-(DATE(,MONTH(TODAY()),DAY(TODAY()))<DATE(,MONTH(Birthday),DAY(Birthday)))
...... vân vân....
Nhưng ở đây ta đang bàn về giãi pháp MÃNG cơ mà!
ANH TUẤN

 

[anhtuan1066]

Những ai quan tâm về mãng hảy tiếp tục với bài toán này nhé:
1> Giã sử có 1 list những ngày nghĩ lể trong năm nằm ở cột C
2> Giã sử cho trước chỉ số năm tại cell B1 (ví dụ là 2008)
Yêu cầu: Hảy tạo 1 list những ngày đi làm trong năm với điều kiện loại bỏ ngày Chủ nhật và tất cả những ngày trùng với list ngày nghĩ lể ?
ANH TUẤN

 

[anhtuan1066]

- Giả sử tôi có dữ liệu tại B550
- Dữ liệu này đang được AutoFilter
Các bạn hãy dùng công thức mãng kết hợp với Conditional Formating để tô màu cho dòng thứ n (sau khi filter)

 

[hoangminhtien]

Dùng hàm mảng thì em vẫn chưa nghĩ ra, em mới chỉ làm được khi có cột phụ.

 

[anhtuan1066]

Dùng hàm mảng thì em vẫn chưa nghĩ ra, em mới chỉ làm được khi có cột phụ.

Với dữ liệu đã AutoFilter thì đàng nào khi giải quyết vấn đề sẽ không tránh khỏi đụng đến SUBTOTAL (bạn đã đi đúng hướng)
Tuy nhiên nếu dùng cột phụ thì tôi đã không đưa vào topic này (Những điều lý thú của công thức mảng. )
Bạn cố gắng suy nghĩ thêm xem!, Biến cột phụ của bạn thành 1 name chẳng hạn

 

[thanh_tks]

Với dữ liệu đã AutoFilter thì đàng nào khi giải quyết vấn đề sẽ không tránh khỏi đụng đến SUBTOTAL (bạn đã đi đúng hướng)
Tuy nhiên nếu dùng cột phụ thì tôi đã không đưa vào topic này (Những điều lý thú của công thức mảng. )
Bạn cố gắng suy nghĩ thêm xem!, Biến cột phụ của bạn thành 1 name chẳng hạn

Bài này đâu phải dùng thêm name làm gì nhỉ, em đưa luôn CT vào CF là được luôn mà!
=SUBTOTAL(3,$D$55)=$D$2

 

[anhtuan1066]

Bài này đâu phải dùng thêm name làm gì nhỉ, em đưa luôn CT vào CF là được luôn mà!
=SUBTOTAL(3,$D$55)=$D$2

Dạ thưa anh! Điều này em biết
Nhưng ở đây là "ép" phải dùng công thức mãng cơ mà!
Nếu không đã không đưa vào topic này!
Hic...

 

[thanh_tks]

Dạ thưa anh! Điều này em biết
Nhưng ở đây là "ép" phải dùng công thức mãng cơ mà!
Nếu không đã không đưa vào topic này!
Hic...

Sao "zắc zối" Vậy!?, thì em đưa hàm SUBTOTAL(3,$D$55) vào name có được không?!

 

[anhtuan1066]

Sao "zắc zối" Vậy!?, thì em đưa hàm SUBTOTAL(3,$D$55) vào name có được không?!

Vâng! Chúng ta nghiên cứu để hiểu thêm về mãng, sau này còn áp dụng vào việc khác... Còn bài toán ở đây chỉ là giả lập cho các bạn áp dụng thôi!
Làm cách gì cũng được, name hay trực tiếp đều OK cả, có điều bắt buộc phải là công thức mãng
(SUBTOTAL(3,$D$55) chỉ là công thức thường)
Cũng giống như mấy bài viết trước tôi có yêu cầu tính tuổi bằng công thức mãng đấy (mặc dù thực tế thì ta luôn dùng DATEDIF)
Hi... hi...

 

[thanh_tks]

Vâng! Chúng ta nghiên cứu để hiểu thêm về mãng, sau này còn áp dụng vào việc khác... Còn bài toán ở đây chỉ là giả lập cho các bạn áp dụng thôi!
Làm cách gì cũng được, name hay trực tiếp đều OK cả, có điều bắt buộc phải là công thức mãng
(SUBTOTAL(3,$D$55) chỉ là công thức thường)
Cũng giống như mấy bài viết trước tôi có yêu cầu tính tuổi bằng công thức mãng đấy (mặc dù thực tế thì ta luôn dùng DATEDIF)
Hi... hi...

Hôm nay rỗi việc lại mang bài này ra làm tiếp, Công thức mảng mới chỉ đúng khi lọc từng cột một và từng mã một.
Anh Ndu giải bài này đi!

 

[ndu96081631]

Hôm nay rỗi việc lại mang bài này ra làm tiếp, Công thức mảng mới chỉ đúng khi lọc từng cột một và từng mã một.
Anh Ndu giải bài này đi!

1> Đặt name:

VT =MATCH(TRUE,SUBTOTAL(103,OFFSET(Sheet1!$B$5,,,ROW(INDIRECT("1:"&ROWS(Sheet1!$B$5:$B$50))),))=Sheet1!$D$2,0)

2> Format:
Quét chọn B550, vào menu Format\Conditional Formating và gõ công thức:

=COUNTA($B$5:$B5)=VT

 

[Hoàng Kim Phượng]

Vẫn là vấn đề về công thức mảng ạ, tại sao em đã sử dụng =SUMPRODUCT(SUMIF(INDIRECT("ROW$1:$19"&"!B10:B100");A3;INDIRECT("ROW$1:$19"&"!D10100"))) mà lại trả về giá trị lỗi.
=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW($1:$18)&"'!C10:C26");B3;INDIRECT("'"&ROW($1:$18)&"'!D1026"))) lại trả về giá trị đúng.
Em xin gửi file đi kèm ạView attachment Phiếu nghiệm thu CT DNCS-T1- thai lam.xlsx

 

[quocgiacan]

Vẫn là vấn đề về công thức mảng ạ, tại sao em đã sử dụng =SUMPRODUCT(SUMIF(INDIRECT("ROW$1:$19"&"!B10:B100");A3;INDIRECT("ROW$1:$19"&"!D10100"))) mà lại trả về giá trị lỗi.
=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW($1:$18)&"'!C10:C26");B3;INDIRECT("'"&ROW($1:$18)&"'!D1026"))) lại trả về giá trị đúng.
Em xin gửi file đi kèm ạ
View attachment 172218

Công thức sai ở chỗ: =SUMPRODUCT(SUMIF(INDIRECT("ROW$1:$19"&"!B10:B100");A3;INDIRECT("ROW$1:$19"&"!D10100")))

• INDIRECT("ROW$1:$19"&"!B10:B100") nó sẽ cho ra một tham chiếu 'ROW$1:$19'!B10:B100 với 'ROW$1:$19' là tên của 1 sheet!! trong khi đó bạn chẳng có sheet nào như vậy, mà bạn cũng không thể tạo tên sheet có chứa các ký tự đặc biệt: "$" hoặc ":", nên công thức báo lỗi.
• Còn hàm INDIRECT("'"&ROW($1:$18)&"'!C10:C26"), nhờ vào hàm ROW($1:$18) chạy ra 1 mảng {1,2,3,4,5,6....18} tương ứng với tên sheet mà bạn có trên file từ 1 đến 18, và kết hợp nối mảng đó với "!C10:C26", và do tác dụng của INDIRECT(.....) sẽ cho ra 1 mảng chứa các tham chiếu:
  • '1'!C10:C26
  • '2'!C10:C26
  • '3'!C10:C26
  • ..........
  • '18'!C10:C26

Các tham chiếu này đều hiện hữu trên file nên nó thực hiện đúng.
​

Chúc bạn ngày vui.