Thảo luận, góp ý, mở rộng cho chủ đề Hàm mảng trong Excel 365

[excel_lv1.5]

Microsoft 365 (tên mới của Office 365) có một số hàm mới dùng cho Excel 365 chuyên trị mảng. Tương tự hàm Query của Google Sheets, các hàm mảng mới này chỉ cần gõ công thức vào 1 ô và ta sẽ có kết quả là một mảng. Mảng này có thể là mảng 1 dòng nhiều cột, 1 cột nhiều dòng, thậm chí một ma trận dòng cột các giá trị.
Các hàm mảng đó là:
- Hàm Unique
- Hàm Sort
- Hàm SortBy
- Hàm RandArray
- Hàm Sequence
- Hàm Filter
Dưới đây xin giới thiệu lần lượt các hàm trên, nội dung do tôi nghiên cứu trên mạng (các tài liệu tiếng Anh), thực hành và viết lại

Hàm UNIQUE
Hàm Unique dùng để lấy danh sách duy nhất từ 1 danh sách nguồn có dữ liệu trùng. Khác với những hàm và công thức trước đây, hàm Unique trả về 1 mảng các giá trị mà không cần Ctrl Shift Enter.

Cú pháp
Hàm Unique có 3 tham số trong đó tham số thứ nhất là bắt buộc và 2 tham số sau không bắt buộc phải điền

=UNIQUE(array, [by_col], [exactly_once])

Trong đó:

• array: vùng dữ liệu hoặc mảng cần lấy danh sách duy nhất.
• [by_col]: Tuỳ chọn dạng True/ False (mặc định False). False (hoặc 0) lấy danh sách duy nhất và trả về 1 cột, True (hoặc 1) lấy danh sách duy nhất và trả về thành 1 dòng.
• [exactly_once]: Tuỳ chọn dạng True/ False (mặc định False). Tuỳ chọn này tuỳ theo cách hiểu thế nào là duy nhất. Xem thí dụ 1.
• Nếu duy nhất mang nghĩa “chỉ lấy những giá trị xuất hiện duy nhất 1 lần”, thì sử dụng True hoặc 1. Tiếng Anh là Unique list
• Nếu duy nhất mang nghĩa “lấy duy nhất tất cả giá trị có xuất hiện” thì sử dụng False hoặc 0. Mỗi giá trị xuất hiện 1 lần hay bao nhiêu lần cũng lấy 1 trong danh sách duy nhất kết quả. Tiếng Anh là Distinct list

Thí dụ mẫu cho hàm UNIQUE
Thí dụ 1
Sự khác nhau khi dùng tham số thứ 3 là True hay False (1 hay 0), mặc định False.

Distinct list
Công thức ô H2 là
H2 =UNIQUE(B2:B13)

View attachment 244379

Với công thức bỏ qua tham số 2 và 3, sẽ mặc định tham số 2 là cột (kết quả thành 1 cột) và tham số 3 lấy hết danh sách duy nhất tất cả giá trị có xuất hiện.

Nhận xét
Chỉ cần gõ công thức vào 1 ô trên cùng và enter, kết quả xuất hiện ở nhiều ô của cả cột. Không cần Ctrl Shift Enter
Vùng kết quả có viền xanh nhạt chung quanh
Ô đầu tiên chứa công thức để chỉnh sửa, các ô còn lại nhìn thấy công thức trên thanh công thức nhưng không cho sửa xoá
Chỉ cần xoá ô đầu tiên, không cần xoá cả vùng như công thức mảng phiên bản cũ.

Unique list (exactly once)
Công thức ô G2 là:
G2 =UNIQUE(B2:B13,,1)

View attachment 244380
Với công thức bỏ qua tham số 2 và gán True cho tham số 3, hàm trả về 1 cột các giá trị chỉ xuất hiện duy nhất 1 lần. Các giá trị xuất hiện nhiều lần bị bỏ qua

Thí dụ 2 – UNIQUE với 2 hoặc nhiều cột
Hàm UNIQUE không giới hạn số lượng cột.

Lấy danh sách duy nhất từ 2 hoặc nhiều cột
Tham số thứ nhất bao nhiêu cột thì kết quả trả về bấy nhiêu cột.
Công thức gõ vào ô F2 là:

F2 =UNIQUE(A2:B13)

Kết quả trả về là 2 cột
View attachment 244381


Nối 2 cột
Dùng công thức nối chuỗi nối hẳn 2 mảng vơi nhau, không cần ctrl Shift enter
Công thức:
H2 =UNIQUE(A2:A13&” “&B2:B13)

View attachment 244382

Nhiều cột không đúng thứ tự cột
Đôi khi chúng ta cần lấy các cột cách xa nhau, hoặc thay đổi thứ tự cột kết quả. Trường hợp này ta dùng hàm CHOOSE để lấy và sắp xếp

• Trường hợp lấy 2 cột A và B nối với nhau (họ tên), và cột E (võ công), công thức G2 là

G2 =UNIQUE(CHOOSE({1,2},A2:A13&” “&B2:B13,E2:E13))

View attachment 244383

• Trường hợp giống như trên nhưng đổi cột võ công ra trước họ tên thì công thức là đổi 2 lên trước 1 trong hàm CHOOSE

G2 =UNIQUE(CHOOSE({2,1},A2:A13&” “&B2:B13,E2:E13))​

Hoặc đổi E lên trước​

G2 =UNIQUE(CHOOSE({1,2},E2:E13,A2:A13&” “&B2:B13))​

View attachment 244384


Thí dụ 3 – Dùng UNIQUE cho tham chiếu ngang nhiều cột
Mặc định hàm Unique trả về mảng giá trị theo dòng, nhưng tham số thứ 2 cho phép trả về mảng giá trị theo cột
Sử dụng tham số thứ 2 LÀ TRUE hoặc 1
Công thức tại ô B9 là
B9 =UNIQUE(B1:I1,1)

Kết quả sẽ dàn hàng ngang nhiều cột

View attachment 244385

Dùng TRANSPOSE chuyển dòng thành cột
Công thức ô K2 như sau, và không cần Ctrl Shift Enter
K2 =TRANSPOSE(UNIQUE(B1:I1,1))

View attachment 244386

Thí dụ 4 – kết hợp UNIQUE và SORT tạo validation list
Công thức tại G2 là
G2 =SORT(UNIQUE(A2:A13&” “&B2:B13))

View attachment 244387

Để dùng kết quả trên làm dữ liệu nguồn cho ô I2, đứng tại I2 mở hộp thoại Data validation
Trên hộp thoại chọn List, và Source ghi =$G$2#

View attachment 244388
Với ký tự #, Excel 365 hiểu là lấy mảng kết quả bắt đầu từ G2 chứ không phải chỉ 1 giá trị chứa trong ô G2
Kết quả validation tại ô I1 như sau

View attachment 244389

​

(Còn tiếp)

​

Có lẽ bạn nên thêm hàm Textjoin vào nó cũng là hàm mảng sử dụng được một lẫn hai chiều. Textjoin và Unique gần như khắc phục được các điểm yếu của các phiên bản excel trước đó.

 

[Cá ngừ F1]

Ôi cảm ơn sư phụ.
Trên excel mà nạp duy nhất vào data validation cũng mệt phết.

 

[HeSanbi]

Ôi cảm ơn sư phụ.
Trên excel mà nạp duy nhất vào data validation cũng mệt phết.

Không mệt nếu bác sử dụng Regular Expression

Ví dụ:

Function UniqueVD(ByVal vArray As Excel.Range) As String
  With VBA.CreateObject("VBScript.RegExp")
    .Global = True
    UniqueVD = vArray.Value(11)
    .Pattern = "[\r\n ]*<[^>]*>[\r\n ]*"
    UniqueVD = .Replace(UniqueVD, ",")
    .Pattern = "(,[\u0000-\u002B\u002D-\uFFFF]+,)(?=.*\1)"
    UniqueVD = .Replace(UniqueVD, "")
    .Pattern = "(^,+)|(,)+|(,+$)"
    UniqueVD = .Replace(UniqueVD, "$2")
  End With
End Function

 

[Kiều Mạnh]

Để viết hàm trên 1 cells trả về 1 bảng dữ liệu Value thì từ Office 2003 to 2019

các bạn có thể viết trên VBA tốt mà ko cần thiết phải khai báo API để sử dụng nó

Trên GPE Mình đã úp Video đó viết hàm kiểu như Office 365 như mô tả ở bài số 1

Trên GPE các bạn có thể tham khảo link sau

1/ Của @HeSanbi để viết Hàm mảng ... Tuy nhiên cách viết này là Cells gõ hàm 1 nơi và vùng dữ liệu gán 1 nơi và phải dùng API

Luyện tập Code VBA: Các bạn đã biết lập trình mảng động nâng cao trong VBA?

(*Đọc xong bài viết và các bạn hãy thử viết một hàm đăng lên chủ đề này!) Các bạn đã biết lập trình mảng động trong VBA là gì hay chưa? Các bạn sẽ biết ngay sau khi bỏ ra vài phút để đọc bài viết này. Định nghĩa "mảng động": hiểu đơn giản là dữ liệu hoặc mảng dữ liệu gốc trong bảng tính thay...

www.giaiphapexcel.com

2/ Tham khảo của Bill cũng như kiểu trên nhưng khác là ko phải khai báo và sử dụng API ... các Bạn sử dụng ADO lấy dữ liệu files khác cũng ok
sử dụng ADO + QueryTables để viết thành hàm như mục số 1

Creating Dynamic Reports with Query Tables in Excel

docs.microsoft.com

3/ Còn Viết hàm kiểu như bài số 1 rất tiếc mình chưa thể chia sẻ lúc này được ..... xin lỗi vì sự bất tiện này nhe

 

[Cá ngừ F1]

Không mệt nếu bác sử dụng Regular Expression

Ví dụ:

Function UniqueVD(ByVal vArray As Excel.Range) As String
  With VBA.CreateObject("VBScript.RegExp")
    .Global = True
    UniqueVD = vArray.Value(11)
    .Pattern = "[\r\n ]*<[^>]*>[\r\n ]*"
    UniqueVD = .Replace(UniqueVD, ",")
    .Pattern = "(,[\u0000-\u002B\u002D-\uFFFF]+,)(?=.*\1)"
    UniqueVD = .Replace(UniqueVD, "")
    .Pattern = "(^,+)|(,)+|(,+$)"
    UniqueVD = .Replace(UniqueVD, "$2")
  End With
End Function

Cảm ơn bạn, tôi thử xem sao.
Thường tôi dùng hàm Uniquelist của anh @ndu96081631 , rồi dùng sự kiện selection_change để nạp data validation

 

[HeSanbi]

Cảm ơn bạn, tôi thử xem sao.
Thường tôi dùng hàm Uniquelist của anh @ndu96081631 , rồi dùng sự kiện selection_change để nạp data validation

Bác chia sẻ file đã vận dụng để tôi tối ưu lại giúp bác.

Có nhiều cách để tạo data validation.

 

[befaint]

Tương tự hàm Query của Google Sheets, các hàm mảng mới này chỉ cần gõ công thức vào 1 ô và ta sẽ có kết quả là một mảng. Mảng này có thể là mảng 1 dòng nhiều cột, 1 cột nhiều dòng, thậm chí một ma trận dòng cột các giá trị.
Các hàm mảng đó là:

Nhân tiện em xin viết ké thêm:
Google Sheets có nhiều hàm trả về mảng có đặc điểm: Công thức chỉ lập ở 1 cell, kết quả có bao nhiêu phần từ thì tự động điền ra bấy nhiêu cells, nếu trong vùng ghi kết quả mà gặp cell(s) đã có dữ liệu thì báo lỗi #REF!
Danh sách một số hàm: UNIQUE, Filter, Sort, IMPORTRANGE, Sequence, RandArray, Query...

Và đặc biệt là hàm ARRAYFORMULA sẽ biến 99.9% công thức mảng thành điều hay ho như phần mô tả bên dưới.

 

[tigertiger]

Nhân tiện em xin viết ké thêm:
Google Sheets có nhiều hàm trả về mảng có đặc điểm: Công thức chỉ lập ở 1 cell, kết quả có bao nhiêu phần từ thì tự động điền ra bấy nhiêu cells, nếu trong vùng ghi kết quả mà gặp cell(s) đã có dữ liệu thì báo lỗi #REF!
Danh sách một số hàm: UNIQUE, Filter, Sort, IMPORTRANGE, Sequence, RandArray, Query...

Và đặc biệt là hàm ARRAYFORMULA sẽ biến 99.9% công thức mảng thành điều hay ho như phần mô tả bên dưới.

View attachment 244407

Đây là diễn đàn Excel mà befaint - Google sheet thì còn nhiều hàm và tính năng nữa (Excel sau này cũng thêm theo nhiều ....)

 

[thuyyeu99]

Tới giờ này thời 4.0 rồi.
còn ai muốn ngâm cứu viết 1 cell mà gán Value 1 nơi dùng

Query= ActiveSheet.QueryTables.Add (tham số)

Query.CommandType = xlCmdTable

 Query.RefreshStyle = xlOverwriteCells

Query.Refresh(False);

Result := Query

 

[kbw1]

Cảm ơn bác ptm0412,
Bác luôn có những bài mang tính update, học hỏi không ngừng,
Chứ không phải chạy theo xu thế,
Hệ thống đang vận hành bằng excel mà các ông cứ lôi gg sheet ra.

 

[VetMini]

...
2/ Tham khảo của Bill cũng như kiểu trên nhưng khác là ko phải khai báo và sử dụng API ... các Bạn sử dụng ADO lấy dữ liệu files khác cũng ok
sử dụng ADO + QueryTables để viết thành hàm như mục số 1

Creating Dynamic Reports with Query Tables in Excel

docs.microsoft.com

API lẫn ADO hay COM Object đều là hàng chỉ chạy được trên Windows.
MS 365 cần phải bao các hệ thống khác (Mac OS, Linux,...)

 

[PhanTuHuong]

Chắc là Microsoft chỉ phát triển cho Office 365 thôi, không update cho Excel 2019
Cảm nhận là dần Microsoft bỏ rơi ứng dụng Office truyền thống, mà thay vào đó là Office 365, vì cái quan trọng nhất là kiểm soát được thời hạn sử dụng, không lo bị c r a c k như các bản offline.

 

[VetMini]

Chắc là Microsoft chỉ phát triển cho Office 365 thôi, không update cho Excel 2019
Cảm nhận là dần Microsoft bỏ rơi ứng dụng Office truyền thống, mà thay vào đó là Office 365, vì cái quan trọng nhất là kiểm soát được thời hạn sử dụng, không lo bị c r a c k như các bản offline.

Đó là cái nhìn về cơ rắc.
Ngày xưa, Turbo Pascal được cho là phần mềm được sử dụng nhiều nhất chính là vì lý do Borland đã cho nó phơ-ri.
Microsoft biết có Office bị dùng lậu rất nhiều nhưng cũng không thể xiết chặt quá. Vì vậy có nhiều phiên bản có chóp đỏ (validation failed).
Từ 2003 là phiên bản vững của Office đến 2007 gập ghềnh, Microsoft có thể đợi tới 2010 để làm vững trở lại là vì lúc ấy chiều hướng chuyển đổi hơi chậm. Ở Âu Mẽo, nhiều cơ quan nhà nước cho đến năm 2014-15 vẫn còn giữ phiên bản 2003.
Theo tôi thì 365 có hai chủ đích:
1. dụ thiên hạ chú trọng hơn về cloud của MS
2. dạng trả tiền theo chu kỳ (phần lớn là thường niên) là dạng 'mướn', thay vì là dạng 'mua'. Nó có ý nghĩa khác trong kế toán.

 

[befaint]

Microsoft biết có Office bị dùng lậu rất nhiều nhưng cũng không thể xiết chặt quá.
...
Theo tôi thì 365 có hai chủ đích:
1. dụ thiên hạ chú trọng hơn về cloud của MS
2. dạng trả tiền theo chu kỳ (phần lớn là thường niên) là dạng 'mướn', thay vì là dạng 'mua'. Nó có ý nghĩa khác trong kế toán.

Thông tin này em nghe 'lỏm' (không xác nhận tính chính xác gì cả) từ nhân viên MS:
"Chuyện dùng lậu Windows, Office không phải MS không kiểm soát được mà là họ không để ý tới (gọi là thả cửa), họ chú trọng tập trung vào đối tượng khách hàng doanh nghiệp thôi".

 

[Kiều Mạnh]

đơn giản là từ Office 2003 to 2019 Bill không cho hàm kiểu như Office 365 thì ta viết lấy mà xài cho khỏe

1/ Tính toán linh tinh chi đó là do mình code
2/ xong mục số 1 gán kết quả đó lên Sheet kiểu hàm Office 365 là xong ( khó tẹo đó thôi ... trên VBA dư sức viết không cần thiết Xài tới API của Bill )

dốt code như Mạnh còn mò ra được mà ... nó cứ loanh quanh trên VBA ý

 

[ptm0412]

Gởi các bạn,
Tôi dự định viết tiếp các hàm mới về mảng của Excel 365 thành một chủ đề tra cứu cho những thành viên đang muốn tiếp cận Excel 365. Chủ đề sẽ mang tính liên tục cho dễ tra cứu. Do đó tôi dời những ý kiến đóng góp bổ sung của các bạn thành chủ đề mới. Mong mọi người tiếp tục đóng góp tại đây.

Xin cám ơn,

 

[thuyyeu99]

Hàm mãng viết na ná như hàm mãng 365 không khó đâu anh, chỉ cần có Array là được
từ cái này "Query= ActiveSheet.QueryTables.Add (tham số) " mà em mò ra cái hàm mãng A_SQL co giản của em đó kkk
Em có cho 1 số người cái UDF đầu tiên sử dụng cái "Query" mà hình như có người nghiệm ra có người vẫn dậm chân tại chỗ.

 

[VetMini]

Nhân tiện em xin viết ké thêm:
Google Sheets có nhiều hàm trả về mảng có đặc điểm: Công thức chỉ lập ở 1 cell, kết quả có bao nhiêu phần từ thì tự động điền ra bấy nhiêu cells, nếu trong vùng ghi kết quả mà gặp cell(s) đã có dữ liệu thì báo lỗi #REF!
Danh sách một số hàm: UNIQUE, Filter, Sort, IMPORTRANGE, Sequence, RandArray, Query...

Và đặc biệt là hàm ARRAYFORMULA sẽ biến 99.9% công thức mảng thành điều hay ho như phần mô tả bên dưới.

Phần 1 thì Excel 365 có trong kỹ thuật 'Spill Range'

Phần 2 thì không. Nhưng tôi nhớ hình như có cách gói kết quả trong Query hay cái gì đó để 'dụ' nó spill ra.

 

[ptm0412]

Phần 2 thì không. Nhưng tôi nhớ hình như có cách gói kết quả trong Query hay cái gì đó để 'dụ' nó spill ra.

Không biết cái anh nói về phần 2 có phải cái này:



và cái này

 

[Manhv1]

Cảm ơn bác hay quá