[HỎI ĐÁP] Cách thức thực hiện của công thức mảng

[longruoi.xls]

Hi mọi người

Em có quả công thức mảng như sau
Tại ô B1 nhập giá trị 1
Tại ô B2 nhập giá trị 10
Tại E4 nhập công thức sau
=IFERROR(LARGE(ROW(INDIRECT($B$1&":"&$B$2))*NOT(COUNTIF($E$3:E3,ROW(INDIRECT($B$1&":"&$B$2)))),RANDBETWEEN(1,$B$2-$B$1-ROW(A1)+2)),"")

Sau khi nhập như trên nhấn tổ hợp phím CTRL + SHIFT + ENTER và kéo dài xuống 10 ô tiếp theo sẽ cho ta ngẫu nhiên các giá trị nguyên không trùng lặp, không theo thứ tự từ 1 đến 10. (các bác muốn giá trị từ 1 đến bao nhiêu thì có thể sửa giá trị B2 tương ứng ví dụ từ 1 đến 100 hoặc 500....).
Công thức test chuẩn rồi nhưng em ngồi nhẩm cách hoạt động của công thức trên thì không hiểu được cụ thể nó tính toán như thế nào ạ.

Mong các bác thông não giúp em. Em cảm ơn.

 

[huuthang_bd]

Hi mọi người

Em có quả công thức mảng như sau
Tại ô B1 nhập giá trị 1
Tại ô B2 nhập giá trị 10
Tại E4 nhập công thức sau
=IFERROR(LARGE(ROW(INDIRECT($B$1&":"&$B$2))*NOT(COUNTIF($E$3:E3,ROW(INDIRECT($B$1&":"&$B$2)))),RANDBETWEEN(1,$B$2-$B$1-ROW(A1)+2)),"")

Sau khi nhập như trên nhấn tổ hợp phím CTRL + SHIFT + ENTER và kéo dài xuống 10 ô tiếp theo sẽ cho ta ngẫu nhiên các giá trị nguyên không trùng lặp, không theo thứ tự từ 1 đến 10. (các bác muốn giá trị từ 1 đến bao nhiêu thì có thể sửa giá trị B2 tương ứng ví dụ từ 1 đến 100 hoặc 500....).
Công thức test chuẩn rồi nhưng em ngồi nhẩm cách hoạt động của công thức trên thì không hiểu được cụ thể nó tính toán như thế nào ạ.

Mong các bác thông não giúp em. Em cảm ơn.

Muốn biết thì phải học thôi, mà học thì phải học từ cái đơn giản đến cái phức tạp. Với công thức đó, bạn bắt đầu tìm hiểu từ ROW(INDIRECT($B$1&":"&$B$2)).

 

[longruoi.xls]

Muốn biết thì phải học thôi, mà học thì phải học từ cái đơn giản đến cái phức tạp. Với công thức đó, bạn bắt đầu tìm hiểu từ ROW(INDIRECT($B$1&":"&$B$2)).

Cảm ơn bác. Thực ra là em có tìm hiểu hết cái hàm trong đó rồi. Mất nguyên buổi rồi mà không lần ra cách thực hiện của nó lên mới lên đây hỏi. cái hàm ROW(INDIRECT($B$1&":"&$B$2)) này ấy kết quả của nó cho ra bằng số thứ tự của dòng ô B1 mà cụ thể ở đây là bằng 1 đúng không bác.

 

[huuthang_bd]

Cảm ơn bác. Thực ra là em có tìm hiểu hết cái hàm trong đó rồi. Mất nguyên buổi rồi mà không lần ra cách thực hiện của nó lên mới lên đây hỏi. cái hàm ROW(INDIRECT($B$1&":"&$B$2)) này ấy kết quả của nó cho ra bằng số thứ tự của dòng ô B1 mà cụ thể ở đây là bằng 1 đúng không bác.

Xem ra 1 buổi là chưa đủ.

 

[xuongrongdat]

Thực ra là em có tìm hiểu hết cái hàm trong đó rồi. Mất nguyên buổi rồi mà không lần ra cách thực hiện của nó lên mới lên đây hỏi. cái hàm ROW(INDIRECT($B$1&":"&$B$2)) này ấy kết quả của nó cho ra bằng số thứ tự của dòng ô B1 mà cụ thể ở đây là bằng 1 đúng không bác.

Cảm ơn bạn.
Vì bài này và bài #4 của anh @huuthang_bd mà mình bỏ ra 30p để xem lại thật kỹ 2 hàm ROW và INDIRECT. Vì mình cũng lơ tơ mơ như bạn vậy đó.
Bạn hỏi vầy:
"kết quả của nó cho ra bằng số thứ tự của dòng ô B1 mà cụ thể ở đây là bằng 1 đúng không bác" =>1 là đúng kết quả của đoạn công thức đó nhưng diễn giải để ra con số 1 thì sai á bạn.
Mình nói đơn giản hàm ROW nó có 1 kiểu tham chiếu vầy:
ROW(1:10)=1
ROW(3:9)=3
ROW(5:10)=5
Nghĩa là, nếu đối số của ROW là 1 dãy ô, thì kết quả sẽ là số thứ tự dòng của ô đầu tiên. (ở đây mình chưa đề cập đến phần tịnh tiến số tiếp theo ở ô tiếp theo của nó)
Ví dụ, ROW(C8:C15) = ROW(C8) = 8. ROW(1:10) = ROW(1) = 1. Chứ không phải ROW(B1) như bạn nói, bạn xem thêm hàm INDIRECT nhé !
Bây giờ, bài của bạn, bạn chỉ cần nhập ở ô B1 là 5, thì bạn sẽ hiểu thôi đoạn công thức tô đậm kia thôi, bạn dùng chức năng Evaluate Formula của Excel để xem từng bước. F9 nhiều khi không kiểm tra được.

Thân !

 

[HieuCD]

Cảm ơn bạn.
Vì bài này và bài #4 của anh @huuthang_bd mà mình bỏ ra 30p để xem lại thật kỹ 2 hàm ROW và INDIRECT. Vì mình cũng lơ tơ mơ như bạn vậy đó.
Bạn hỏi vầy:
"kết quả của nó cho ra bằng số thứ tự của dòng ô B1 mà cụ thể ở đây là bằng 1 đúng không bác" =>1 là đúng kết quả của đoạn công thức đó nhưng diễn giải để ra con số 1 thì sai á bạn.
Mình nói đơn giản hàm ROW nó có 1 kiểu tham chiếu vầy:
ROW(1:10)=1
ROW(3:9)=3
ROW(5:10)=5
Nghĩa là, nếu đối số của ROW là 1 dãy ô, thì kết quả sẽ là số thứ tự dòng của ô đầu tiên. (ở đây mình chưa đề cập đến phần tịnh tiến số tiếp theo ở ô tiếp theo của nó)
Ví dụ, ROW(C8:C15) = ROW(C8) = 8. ROW(1:10) = ROW(1) = 1. Chứ không phải ROW(B1) như bạn nói, bạn xem thêm hàm INDIRECT nhé !
Bây giờ, bài của bạn, bạn chỉ cần nhập ở ô B1 là 5, thì bạn sẽ hiểu thôi đoạn công thức tô đậm kia thôi, bạn dùng chức năng Evaluate Formula của Excel để xem từng bước. F9 nhiều khi không kiểm tra được.

Thân !

ROW(1:10)=1: Bạn nên nhấn F9 đoạn ROW(1:10) xem kết quả là gì

 

[bebo021999]

Ví dụ, ROW(C8:C15) = ROW(C8) = 8. ROW(1:10) = ROW(1) = 1.

Nếu khẳng định kết quả =8 là sai nhé. Nó chỉ chọn số 8 để hiển thị ra thôi, còn giá trị thực của nó là 1 mảng {8;9;...;15}
Nó trở thành giá trị đơn khi thực hiện phép tính với ô chứa nó.
VD: A1=ROW(8:15)={8;9;10;11;12;13;14;15}
B1=A1+1=9
Nhưng tại A1, bạn +1 vào: =ROW(8:15)+1
thì kết quả show ra tại cell là 9, nhưng thực sự giá trị là dãy: {9;10;11;12;13;14;15;16}

 

[xuongrongdat]

ROW(1:10)=1: Bạn nên nhấn F9 đoạn ROW(1:10) xem kết quả là gì

Nếu khẳng định kết quả =8 là sai nhé. Nó chỉ chọn số 8 để hiển thị ra thôi, còn giá trị thực của nó là 1 mảng {8;9;...;15}
Nó trở thành giá trị đơn khi thực hiện phép tính với ô chứa nó.
VD: A1=ROW(8:15)={8;9;10;11;12;13;14;15}
B1=A1+1=9
Nhưng tại A1, bạn +1 vào: =ROW(8:15)+1
thì kết quả show ra tại cell là 9, nhưng thực sự giá trị là dãy: {9;10;11;12;13;14;15;16}

Dạ, em đã thử và đã hiểu. Cảm ơn 2 anh.
Trước giờ thấy mọi người ở diễn đàn xài rất nhiều ROW(mảng) mà giờ mới "hiểu" nó.

 

[huuthang_bd]

Một người bỏ ra 1 buổi, một người bỏ ra 30 phút để nghiên cứu công thức mảng mà rốt cuộc vẫn chưa biết mảng là gì.
Các bạn muốn nghiên cứ thì tìm các bài của thành viên Phan Thế Hiệp, thành viên này rất chịu khó phân tích, giải thích công thức mảng, có những bài rất chi tiết.

 

[bebo021999]

Công thức test chuẩn rồi nhưng em ngồi nhẩm cách hoạt động của công thức trên thì không hiểu được cụ thể nó tính toán như thế nào ạ.

Để hiểu công thức bạn nên bôi đen từng phần trên công thức, sau đó F9 để xem kết quả.
Nguyên tắc: Xem từ bên trong xem ra.

Bước 1) Tạo chuỗi số liên tiếp. Vì yêu cầu là tạo chuỗi số nguyên liên tiếp không trùng, nên trước tiên bạn phải tại chuỗi số từ 1-10
Vì bảng tính có số dòng và cột liên tiếp nhau, và hàm ROW và COLUMN để lấy chỉ số dòng/cột, nên thường dùng 2 hàm này để tạo chuỗi.
Thực hành: Tại A5, nhập = ROW(1:10), Enter. Sau đó tại A5, nhấn F2 (hoặc click lên thanh công thức, bôi đen công thức này, nhấn F9)



Vì muốn chuỗi này thay đổi theo giá trị nhập min tại B1 và max tại B2, nên phải tham chiếu đến 2 ô này. Tuy nhiên 1 và 10 tại B1 và B2 chỉ là giá trị số học được nhập vào, excel không tự hiểu 1 là dòng 1, 10 là dòng 10, nên phải dùng INDIRECT để dịch cho excel hiểu đây là tham chiếu:
=ROW(INDIRECT(B1&":"&B2))
Dùng F9 để test lại thấy kết quả giống như phía trên.
Như vậy dãy số này sẽ tự động cập nhật từ 1-100 nếu thay B2 bằng 100

Bước 2) Tạo số ngẫu nhiên từ 1-10
Bình thường, lấy số ngẫu nhiên (có trùng), dùng:
Dòng 1 =INDEX(ROW(INDIRECT($B$1&":"&$B$2)),RANDBETWEEN(1,$B$2-$B$1-ROW(A1)+2))
Copy đến dòng 10
Lưu ý: đối số thứ 2 của RANDBETWEEN với ROW(A1) tăng 1 giá trị khi copy xuống dòng 2, sẽ giảm từ 10 xuống 9.

Tuy nhiên với yêu cầu dòng 2 không trùng dòng 1, thì cái dãy 1-10 phải được xử lý để loại giá trị đã xuất hiện tại dòng 1.
Ví dụ: Dòng 1 xuất hiện số 8, tại dòng 2 dãy số sẽ trở thành:
{1;2;3;4;5;6;7;0;9;10} với số 8 được thay bằng số 0.
Có nghĩa là tích của 2 mảng:{1;2;3;4;5;6;7;8;9;10}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE} với FALSE ứng với giá trị 8
Mảng điều kiện TRUE/FALSE được tạo ra từ việc so sánh dãy 1-10 với vùng chứa các giá trị đã xuất hiện ở phía trên nó, nếu tìm thấy trả về TRUE. sau đó dùng NOT để lấy giá trị nghịch đảo là FALSE:
NOT(COUNTIF($E$3:E4,ROW(INDIRECT($B$1&":"&$B$2))))

và lúc này dùng hàm LARGE({1;2;3;4;5;6;7;0;9;10},k) để trích xuất giá trị:

LARGE(ROW(INDIRECT($B$1&":"&$B$2))*NOT(COUNTIF($E$3:E4,ROW(INDIRECT($B$1&":"&$B$2)))),RANDBETWEEN(1,$B$2-$B$1-ROW(A2)+2))

Lưu ý: Yếu tố không trùng của dòng 2 là dãy mới tạo, theo ví dụ trên, đã thay số 8 thanh số 0, như vậy hàm LARGE đã mặc nhiên loại bỏ số 10 ra ngoài, thành 1,2,3,4,5,6,7 và 9,10. tham số k lúc này là số ngẫu nhiên từ 1-9 sẽ ngẫu nhiên lấy số trong dãy này, mà bỏ qua số 8.

Hình dưới minh họa dòng 4, với 3 giá trị đầu đã xuất hiện là 8,9,10

TRƯỚC KHI F9:


SAU KHI F9

 

[longruoi.xls]

Cảm ơn bạn.
Vì bài này và bài #4 của anh @huuthang_bd mà mình bỏ ra 30p để xem lại thật kỹ 2 hàm ROW và INDIRECT. Vì mình cũng lơ tơ mơ như bạn vậy đó.

Cảm ơn bạn vì bạn đã cảm ơn mình ) nhờ có quả cảm ơn này của bạn mà có các bác dưới góp ý nữa em vỡ ra rồi.

Bài đã được tự động gộp: 7/5/21

Để hiểu công thức bạn nên bôi đen từng phần trên công thức, sau đó F9 để xem kết quả.
Nguyên tắc: Xem từ bên trong xem ra.

Bước 1) Tạo chuỗi số liên tiếp. Vì yêu cầu là tạo chuỗi số nguyên liên tiếp không trùng, nên trước tiên bạn phải tại chuỗi số từ 1-10
Vì bảng tính có số dòng và cột liên tiếp nhau, và hàm ROW và COLUMN để lấy chỉ số dòng/cột, nên thường dùng 2 hàm này để tạo chuỗi.
Thực hành: Tại A5, nhập = ROW(1:10), Enter. Sau đó tại A5, nhấn F2 (hoặc click lên thanh công thức, bôi đen công thức này, nhấn F9)

View attachment 258178

Vì muốn chuỗi này thay đổi theo giá trị nhập min tại B1 và max tại B2, nên phải tham chiếu đến 2 ô này. Tuy nhiên 1 và 10 tại B1 và B2 chỉ là giá trị số học được nhập vào, excel không tự hiểu 1 là dòng 1, 10 là dòng 10, nên phải dùng INDIRECT để dịch cho excel hiểu đây là tham chiếu:
=ROW(INDIRECT(B1&":"&B2))
Dùng F9 để test lại thấy kết quả giống như phía trên.
Như vậy dãy số này sẽ tự động cập nhật từ 1-100 nếu thay B2 bằng 100

Bước 2) Tạo số ngẫu nhiên từ 1-10
Bình thường, lấy số ngẫu nhiên (có trùng), dùng:
Dòng 1 =INDEX(ROW(INDIRECT($B$1&":"&$B$2)),RANDBETWEEN(1,$B$2-$B$1-ROW(A1)+2))
Copy đến dòng 10
Lưu ý: đối số thứ 2 của RANDBETWEEN với ROW(A1) tăng 1 giá trị khi copy xuống dòng 2, sẽ giảm từ 10 xuống 9.

Tuy nhiên với yêu cầu dòng 2 không trùng dòng 1, thì cái dãy 1-10 phải được xử lý để loại giá trị đã xuất hiện tại dòng 1.
Ví dụ: Dòng 1 xuất hiện số 8, tại dòng 2 dãy số sẽ trở thành:
{1;2;3;4;5;6;7;0;9;10} với số 8 được thay bằng số 0.
Có nghĩa là tích của 2 mảng:{1;2;3;4;5;6;7;8;9;10}*{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE} với FALSE ứng với giá trị 8
Mảng điều kiện TRUE/FALSE được tạo ra từ việc so sánh dãy 1-10 với vùng chứa các giá trị đã xuất hiện ở phía trên nó, nếu tìm thấy trả về TRUE. sau đó dùng NOT để lấy giá trị nghịch đảo là FALSE:
NOT(COUNTIF($E$3:E4,ROW(INDIRECT($B$1&":"&$B$2))))

và lúc này dùng hàm LARGE({1;2;3;4;5;6;7;0;9;10},k) để trích xuất giá trị:

LARGE(ROW(INDIRECT($B$1&":"&$B$2))*NOT(COUNTIF($E$3:E4,ROW(INDIRECT($B$1&":"&$B$2)))),RANDBETWEEN(1,$B$2-$B$1-ROW(A2)+2))

Lưu ý: Yếu tố không trùng của dòng 2 là dãy mới tạo, theo ví dụ trên, đã thay số 8 thanh số 0, như vậy hàm LARGE đã mặc nhiên loại bỏ số 10 ra ngoài, thành 1,2,3,4,5,6,7 và 9,10. tham số k lúc này là số ngẫu nhiên từ 1-9 sẽ ngẫu nhiên lấy số trong dãy này, mà bỏ qua số 8.

Hình dưới minh họa dòng 4, với 3 giá trị đầu đã xuất hiện là 8,9,10

TRƯỚC KHI F9:
View attachment 258181

SAU KHI F9

View attachment 258182

Rất rất cảm ơn bác nhiệt tình. Em đã hiểu rồi. Em bị mắc ở phần công thức mảng của hàm row nên không hiểu hết được. Cảm ơn bác nhiều

 

[befaint]

... Còn để tự làm được những công thức đó phải giỏi Toán.

 

[bebo021999]

Xem đoạn video hướng dẫn này để hiểu thêm nhé.
(chịu khó đọc tiếng Anh vì mình làm để đăng trong group khác)

 

[Phan Thế Hiệp]

Một người bỏ ra 1 buổi, một người bỏ ra 30 phút để nghiên cứu công thức mảng mà rốt cuộc vẫn chưa biết mảng là gì.
Các bạn muốn nghiên cứ thì tìm các bài của thành viên Phan Thế Hiệp, thành viên này rất chịu khó phân tích, giải thích công thức mảng, có những bài rất chi tiết.

Vài tâm sự gửi đến 'bậc đàn anh' cũng là 1 trong những người ' thầy' về công thức mảng của mình:

Cũng giống như bài gửi anh @HieuCD, nhân thấy anh viết trong chủ đề này hướng dẫn cho đàn em và có đề cập đến tên mình, với cách hành văn gọn gàng nhưng mô tả đầy đủ và đúng với những gì nó có, nên rất cảm kích vì anh đã không ' xem nhẹ' (khà khà! không có ý bảo anh 'coi trọng' nha!). Xin cảm ơn vì lời nhận xét của anh.

Nhìn những câu hỏi để tìm hiểu "mảng là gì?" của các em trong chủ đề này làm nhớ lại ký ức đẹp của khoảng thời gian trước đây cũng từng thắc mắc và hình ảnh 'quá khứ' của mình lại lần nữa được tái hiện. Thương cho sự vỡ òa sung sướng của các em khi đã tìm được câu trả lời, dù chỉ là 1 chút kiến thức cỏn con cơ bản về 'công thức mảng'. Còn ghi lòng, chính anh là người 'trực tiếp' gợi mở để khai sáng cho những bước chân bì bõm trước đây trong dòng lội 'mảng' huyền bí, khó hiểu, khó nắm bắt và khó quản lý nó, cho nên thật sự rất đồng cảm với các em. Nêu lên những điều trên mới cảm nhận ra 1 điều cần suy nghĩ là: câu hỏi chính không phải ở chỗ 'mảng là gì?' mà là 'tại sao một phương thuật, hay nôm na gọi là một 'công cụ' như bao công cụ khác của Excel lạ̣i có chỗ đứng coi như "bằng 0" trên diễn đàn này, vì như anh đã biết: nó quá tản mạn, manh mún, lác đác, lưa thưa... và 'lâu lâu' chỉ được "1 vài" anh em tâm huyết đề cập và giải thích nó mà thôi!? Kể từ tài liệu của anh Trần Thanh Phong chị @handung107 đề cập những cơ bản về công thức mảng, sau nữa là những chia sẻ 'tản mạn' của anh và 'thầy' @ndu96081631, hầu như nó chưa hoặc nói chính xác là "không" được hệ thống hóa lại hoặc cập nhật lại một cách bài bản để xứng với tên gọ̣i "Giải Pháp Excel", tại sao lại như vậy!? Phải chi với tên đã gói gọn mục tiêu, như: "Tự học VBA", "Lập trình cho Excel" thì không cần nhắc đến chi nữa, đằng này là "giải pháp" mà, mọi công cụ của Excel có thể được sử dụng, nên được hệ thống để hướng dẫn cho thành viên tìm đến những kiến thức bổ ích, hoặc hiểu rõ sở trường sở đoản của các công cụ khi sử dụng. Câu hỏi này thú thật đã hình thành 5 năm trước đây và cũng được cân nhắc thảo luận vài dự án trong anh em cùng chí hướng, lúc đó với 'tiền thân' đầy nhiệt huyết "quốc gia cần" có logo với ý nguyện 'mọi anh em đoàn kết cùng xiết tay xây đắp hành tinh Excel', nhưng rồi ngộ ra đó là sự mơ tưởng viễn vông, cười buồn tự nhủ "quốc gia đách cần mày đâu, @quocgiacan ơi!", thế là phải cho 'mày' biến mất từ đó, và thay vào 1 ông Phan(g), để phang những gì cần phang cho anh em. Tuy chỉ là phiên bản, nhưng ý nguyện vẫn còn dư âm, cho nên mới có những 'ngẫu hứng lý qua cầu' dành cho anh em chân thành tìm hiểu về 'công thức mảng'. Cách giải thích bài cho "ngẫu hứng" đó, mà anh nhận xét "chịu khó", cũng có ý muốn nhằm tạ ơn 'bậc đàn anh' đã cho mình kiến thức để có ngày hôm nay. Còn nhớ như in những gì anh chia sẻ: Indirect(R..C..,0), Frequency, công thức tạo vòng lặp huyền bí, những cách lấy Min, Max từng phân đoạn..v.v, và ấn tượng nhất là sự góp sức của anh để hình thành nên công thức tính thuế TNCN, từ sự phát hiện nhạy bén kỳ tài của @eke_rula, mà ngay cả 'bậc thầy VBA' cũng phải chào thua, đó lẽ ra phải là niềm tự hào của riêng diễn đàn... sự đóng góp quý báu trên mặc dù trở thành tài sản chung, nhưng trớ trêu để hiểu và biết giá trị thật của nó thì lẻ loi chỉ có anh em mình mà thôi!



Chợt thấm ý! nhớ về bài ngụ ngôn "con cáo và chùm nho", và chuyện tiếu lâm: "cua Nhật và cua Việt Nam".



Thôi thì! đây chỉ là "cái sân chơi" kiến thức, đòi hỏi chi nhiều! Các bài nhằm nguyện ý khai sáng dần dà sẽ chìm vào dĩ vãng như lớp trầm tích, để rồi tái hội với câu hỏi "mảng là gì?"...



Nghêu ngao: "cũng đành xin làm người hát rong...."

Chúc anh ngày thiệt vui và đầy ý nghĩa.