Hàm MAKEARRAY có thể tạo bảng cửu chương 10×10 chỉ với một công thức duy nhất. Không cần kéo thả, không cần công thức phức tạp, và kết quả tự động cập nhật khi thay đổi kích thước. Tôi từng mất 15 phút để tạo dữ liệu mẫu cho testing, giờ chỉ còn 30 giây.
Tại sao MAKEARRAY lại quan trọng
MAKEARRAY là một trong những hàm mảng động mạnh nhất trong Excel 365. Thay vì nhập dữ liệu thủ công từng ô một, bạn có thể tạo toàn bộ bảng dữ liệu bằng một công thức. Hàm này kết hợp với LAMBDA để tính toán giá trị cho mỗi ô dựa trên vị trí hàng và cột của nó.
Điều đặc biệt là kết quả tự động lan tỏa ra các ô lân cận. Khi bạn thay đổi số hàng hoặc số cột trong công thức, mảng tự động điều chỉnh kích thước. Không cần chọn vùng trước, không cần bấm tổ hợp phím đặc biệt như công thức mảng cũ.
Bước 1: Hiểu cú pháp cơ bản
Cú pháp của MAKEARRAY gồm ba thành phần chính:
=MAKEARRAY(số_hàng, số_cột, LAMBDA(r, c, phép_tính))
Số hàng: Số lượng hàng trong mảng kết quả. Phải là số nguyên lớn hơn 0.
Số cột: Số lượng cột trong mảng kết quả. Phải là số nguyên lớn hơn 0.
LAMBDA: Hàm tính toán giá trị cho mỗi ô. Nhận hai tham số:
r: Chỉ số hàng hiện tại (bắt đầu từ 1)c: Chỉ số cột hiện tại (bắt đầu từ 1)
Hàm LAMBDA thực thi riêng cho mỗi ô trong mảng. Excel tự động truyền vào chỉ số hàng và cột, bạn chỉ cần định nghĩa phép tính dựa trên hai giá trị này.
Ví dụ đơn giản nhất:
=MAKEARRAY(3, 3, LAMBDA(r, c, r * c))
Công thức này tạo mảng 3 hàng 3 cột. Mỗi ô chứa tích của số hàng nhân số cột. Kết quả:
1 2 3
2 4 6
3 6 9
Ô đầu tiên (hàng 1, cột 1): 1 × 1 = 1
Ô giữa (hàng 2, cột 2): 2 × 2 = 4
Ô cuối (hàng 3, cột 3): 3 × 3 = 9
Bước 2: Tạo dữ liệu thực tế
Bảng cửu chương là ứng dụng phổ biến nhất của MAKEARRAY. Một công thức duy nhất có thể thay thế hàng trăm ô nhập tay.
Tạo bảng cửu chương 10×10:
- Chọn một ô trống bất kỳ (ví dụ: A1)
- Nhập công thức:
=MAKEARRAY(10, 10, LAMBDA(r, c, r * c)) - Bấm Enter
Excel tự động lấp đầy 100 ô từ A1 đến J10 với kết quả từ 1 đến 100. Khi bạn nhấp vào bất kỳ ô nào trong vùng này, công thức hiển thị màu xám và không chỉnh sửa được. Đây là đặc tính của mảng động – chỉ ô đầu tiên chứa công thức, các ô còn lại là kết quả lan tỏa.
Tạo dãy số tuần tự:
Thay vì sử dụng phép nhân, bạn có thể tạo số tuần tự bằng công thức:
=MAKEARRAY(5, 5, LAMBDA(r, c, (r-1)*5 + c))
Kết quả là mảng 5×5 với số từ 1 đến 25 sắp xếp theo hàng:
1 2 3 4 5
6 7 8 9 10
11 12 13 14 15
16 17 18 19 20
21 22 23 24 25
Công thức hoạt động bằng cách tính vị trí tuyệt đối của mỗi ô. Hàng đầu tiên (r=1): từ 1 đến 5. Hàng thứ hai (r=2): từ 6 đến 10. Hàng thứ ba (r=3): từ 11 đến 15.
Tạo dữ liệu ngẫu nhiên cho testing:
=MAKEARRAY(20, 3, LAMBDA(r, c, RANDBETWEEN(1, 100)))
Tạo bảng 20 hàng 3 cột với số ngẫu nhiên từ 1 đến 100. Mỗi lần Excel tính toán lại (bấm F9), các số tự động thay đổi. Rất hữu ích khi cần dữ liệu mẫu để kiểm tra công thức hoặc tạo biểu đồ demo.
Tôi sử dụng phương pháp này thay vì copy dữ liệu từ nguồn khác. Dữ liệu sạch, không có định dạng thừa, và kích thước tùy chỉnh chỉ bằng cách sửa hai tham số đầu tiên.
Bước 3: Ứng dụng nâng cao trong công việc
MAKEARRAY không chỉ tạo số đơn thuần. Bạn có thể kết hợp với hàm TEXT, IF, và các hàm khác để tạo dữ liệu phức tạp.
Tạo nhãn tự động:
=MAKEARRAY(10, 1, LAMBDA(r, c, "Sản phẩm " & TEXT(r, "000")))
Kết quả là danh sách từ “Sản phẩm 001” đến “Sản phẩm 010” trong một cột. Hàm TEXT đảm bảo số luôn có 3 chữ số với số 0 đứng đầu.
Tạo bảng giá theo quy luật:
=MAKEARRAY(10, 5, LAMBDA(r, c, 100000 * r * (1 + c * 0.1)))
Tạo bảng giá 10 sản phẩm với 5 mức giá khác nhau. Mỗi cột tăng 10% so với cột trước. Mỗi hàng tăng 100.000 so với hàng trước. Kết quả:
110000 120000 130000 140000 150000
220000 240000 260000 280000 300000
330000 360000 390000 420000 450000
...
Thay đổi giá gốc 100000 thành giá trị khác, toàn bộ bảng tự động cập nhật.
Kết hợp với điều kiện:
=MAKEARRAY(10, 10, LAMBDA(r, c, IF(r = c, 1, 0)))
Tạo ma trận đơn vị 10×10. Các ô trên đường chéo chính (r = c) có giá trị 1, các ô khác có giá trị 0. Đây là cấu trúc cơ bản trong toán học ma trận.
Tạo lịch tháng:
=MAKEARRAY(5, 7, LAMBDA(r, c, LET(
ngay_dau, DATE(2025, 1, 1),
thu_dau, WEEKDAY(ngay_dau),
so_ngay, (r-1)*7 + c - thu_dau + 1,
IF(AND(so_ngay >= 1, so_ngay <= 31), so_ngay, "")
)))
Tạo lịch tháng 1 năm 2025 với 5 hàng 7 cột (tuần và thứ). Công thức phức tạp hơn nhưng kết quả mạnh mẽ – chỉ cần thay đổi tháng và năm trong hàm DATE, toàn bộ lịch tự động điều chỉnh.
Tôi áp dụng kỹ thuật này để tạo dashboard theo dõi tiến độ dự án. Thay vì nhập thủ công 30 ngày trong tháng, một công thức MAKEARRAY kết hợp với VLOOKUP tạo toàn bộ bảng trong 3 giây.
Lỗi thường gặp và cách khắc phục
Lỗi #VALUE! với thông báo “Incorrect Parameters”
Nguyên nhân: Hàm LAMBDA không đúng cú pháp hoặc thiếu tham số. LAMBDA bắt buộc phải có đúng hai tham số (hàng và cột).
Sai: =MAKEARRAY(5, 5, LAMBDA(r, r*2))
Đúng: =MAKEARRAY(5, 5, LAMBDA(r, c, r*2))
Ngay cả khi không sử dụng tham số c, bạn vẫn phải khai báo nó.
Lỗi #VALUE! khi số hàng hoặc cột nhỏ hơn 1
=MAKEARRAY(0, 5, LAMBDA(r, c, r*c)) // Lỗi
=MAKEARRAY(5, -1, LAMBDA(r, c, r*c)) // Lỗi
Cả số hàng và số cột phải là số nguyên dương lớn hơn 0. Excel không chấp nhận số âm hoặc số 0.
Lỗi #SPILL! khi vùng kết quả bị chặn
MAKEARRAY cần đủ ô trống để hiển thị kết quả. Nếu có dữ liệu trong vùng lan tỏa, Excel báo lỗi #SPILL!.
Giải pháp:
- Xóa dữ liệu trong vùng bị chặn
- Hoặc di chuyển công thức sang vị trí khác
- Hoặc thu nhỏ kích thước mảng
Công thức chạy chậm với mảng lớn
MAKEARRAY tính toán LAMBDA cho mỗi ô riêng lẻ. Mảng 100×100 = 10.000 lần tính toán. Nếu LAMBDA chứa công thức phức tạp (VLOOKUP, SUMIFS), thời gian xử lý có thể lên đến vài giây.
Giải pháp: Sử dụng LET để lưu trữ tính toán trung gian, tránh tính toán lại nhiều lần trong cùng một LAMBDA.
Yêu cầu và tương thích
MAKEARRAY chỉ khả dụng trong Excel for Microsoft 365 và Excel for the web. Hàm không hoạt động trong Excel 2021, Excel 2019, hoặc các phiên bản cũ hơn. Để kiểm tra, nhập =MAKEARRAY( vào ô bất kỳ. Nếu Excel hiển thị gợi ý cú pháp, phiên bản của bạn hỗ trợ hàm này.
Các hàm liên quan bạn có thể khám phá: MAP (ánh xạ hàm lên mảng), SCAN (tính toán tích lũy), REDUCE (rút gọn mảng thành giá trị đơn), BYROW và BYCOL (xử lý từng hàng hoặc cột). Tất cả đều thuộc họ hàm mảng động giới thiệu từ 2021.
Với ba bước trên, bạn có thể tạo mọi loại mảng dữ liệu từ đơn giản đến phức tạp. Bắt đầu với bảng cửu chương để làm quen, sau đó áp dụng vào công việc thực tế của bạn.
