Tạo Validation unique list không dùng cột phụ (1 người xem)

minhtungph · 27/10/16

- Mình có 1 file nhỏ như hình trên
- trong file có tạo 4 Name
1.Data =IF(ISERROR(IF(MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0)=ROW(Sheet1!$A$1:$A$13),MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0),"")),"",IF(MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0)=ROW(Sheet1!$A$1:$A$13),MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0),"")
Name này sẽ tạo ra 1 mảng gồm các vị trí của các giá trị không trùng vào các giá trị "":

1,2,"",4,5,6,7,"","","",11,"",""

2. dem=COUNT(data)
Name này mục đích là để đếm các số trong data: 7

3. LaySTT=SMALL(data,ROW(INDIRECT("A1:A"&dem)))
Name này dùng để "xích" các giá trị lại gần nhau hơn: 1,2,4,5,6,7,11

4. Uniquelist=INDEX(Sheet1!$A$1:$A$13,LaySTT)
name dùng để tạo ra một mảng để gán vào validation ( mình nghi ngờ ở bước này bị đụng độ gì đó nên validation chỉ lấy giá trị đầu tiên thôi)

==> nếu chúng ta viết công thức mảng trả về nhiều ô trên range thì kết quả ra đúng như ý, tức là uniquelist vẫn chứa trong nó 1 mảng, nhưng gán vào datavalidation thì nó không chịu
==> Mình nghi ngờ thằng validation này không chịu chơi với mảng ảo mà chỉ chấp nhận range thôi, không biết có đúng không, mong các bạn và các anh chị giải đáp giúp.

leoheocon · 27/10/16

minhtungph đã viết:
View attachment 167583
- Mình có 1 file nhỏ như hình trên
- trong file có tạo 4 Name
1.Data =IF(ISERROR(IF(MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0)=ROW(Sheet1!$A$1:$A$13),MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0),"")),"",IF(MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0)=ROW(Sheet1!$A$1:$A$13),MATCH(Sheet1!$A$1:$A$13,Sheet1!$A$1:$A$13,0),"")
Name này sẽ tạo ra 1 mảng gồm các vị trí của các giá trị không trùng vào các giá trị 1,2,"",4,5,6,7,"","","",11,"",""

2. dem=COUNT(data)
Name này mục đích là để đếm các số trong data: 7

3. LaySTT=SMALL(data,ROW(INDIRECT("A1:A"&dem)))
Name này dùng để "xích" các giá trị lại gần nhau hơn: 1,2,4,5,6,7,11

4. Uniquelist=INDEX(Sheet1!$A$1:$A$13,LaySTT)
name dùng để tạo ra một mảng để gán vào validation ( mình nghi ngờ ở bước này bị đụng độ gì đó nên validation chỉ lấy giá trị đầu tiên thôi)

==> nếu chúng ta viết công thức mảng trả về nhiều ô trên range thì kết quả ra đúng như ý, tức là uniquelist vẫn chứa trong nó 1 mảng, nhưng gán vào datavalidation thì nó không chịu
==> Mình nghi ngờ thằng validation này không chịu chơi với mảng ảo mà chỉ chấp nhận range thôi, không biết có đúng không, mong các bạn và các anh chị giải đáp giúp.

DV không chơi với mảng đâu bạn, bạn chỉ có thể tạo cột phụ rồi đưa vào DV thôi!!! |||||

Uniquelist=INDEX(Sheet1!$A$1:$A$13,LaySTT) cái này không tảo ra mảng được nhe bạn!!!

minhtungph · 27/10/16

leoheocon đã viết:
DV không chơi với mảng đâu bạn, bạn chỉ có thể tạo cột phụ rồi đưa vào DV thôi!!!
Uniquelist=INDEX(Sheet1!$A$1:$A$13,LaySTT) cái này không tảo ra mảng được nhe bạn!!!

mình nghỉ là tạo ra mảng được chứ, bạn thử bôi đen 7 ô (bôi theo cột) sau đó =Uniquelist Ctr+Shift + Enter thì bạn sẽ thấy nó là một mảng

leoheocon · 27/10/16

minhtungph đã viết:
mình nghỉ là tạo ra mảng được chứ, bạn thử bôi đen 7 ô (bôi theo cột) sau đó =Uniquelist Ctr+Shift + Enter thì bạn sẽ thấy nó là một mảng

Bạn làm vậy thì show ra được, nhưng nó không phải mảng ảo, bạn dùng index(Uniquelist,2) xem ra kết quả không hoặc COUNTA(uniquelist) xem phải nó =1 không? Cái này mình đã từng bị rồi, index không thể tạo ra mảng ảo được chỉ lấy kết quả đầu tiên thôi, mình cũng đã từng có 1 bài hỏi về vấn đề này rồi!!!

minhtungph · 27/10/16

Nhưng nó cứ ngợ ngợ sao ấy, nó đúng thật ko giống mảng,nhưng sao lại show ra được thế, với lại bài toán này nếu ko muốn dùng cột phụ thì chắc có lẽ chỉ vba mới giải quyết được đúng ko bạn.

leoheocon · 27/10/16

minhtungph đã viết:
Nhưng nó cứ ngợ ngợ sao ấy, nó đúng thật ko giống mảng,nhưng sao lại show ra được thế, với lại bài toán này nếu ko muốn dùng cột phụ thì chắc có lẽ chỉ vba mới giải quyết được đúng ko bạn.

Không dùng cột phụ thì chỉ dùng VBA thôi bạn!!!! |||||

minhtungph · 27/10/16

leoheocon đã viết:
Không dùng cột phụ thì chỉ dùng VBA thôi bạn!!!!

Cám ơn bạn, cái vụ mảng hay không mảng chắc nhờ admin đi ngang giải thích giúp.

leoheocon · 27/10/16

minhtungph đã viết:
Cám ơn bạn, cái vụ mảng hay không mảng chắc nhờ admin đi ngang giải thích giúp.

Bạn đọc bài này xem sao!!!
http://www.giaiphapexcel.com/forum/showthread.php?118982-Hỏi-về-hàm-Index!!!&p=744848

vanvan9697 · 27/10/16

cho em hỏi anh tùng với, nếu như sử dụng công thức mảng trên là có thể giải quyết được bài toán tạo list chọn mà giá trị không xuất hiện 2 lần, mặt khác không cần sử dụng VBA để giải quyết bài toán này đúng không ạ

leoheocon · 27/10/16

vanvan9697 đã viết:
cho em hỏi anh tùng với, nếu như sử dụng công thức mảng trên là có thể giải quyết được bài toán tạo list chọn mà giá trị không xuất hiện 2 lần, mặt khác không cần sử dụng VBA để giải quyết bài toán này đúng không ạ

Bạn phải dùng cột phụ để tạo mảng không trùng trước, sau đó mới đưa vào DV, theo mình biết không thể đưa mảng trực tiếp vào DV được đâu bạn vì DV chỉ nhận Range thôi!!!Không biết còn cách nào để đưa mảng vào DV không, mình cũng muốn biết thêm chỗ này |||||

!!!

vanvan9697 · 27/10/16

leoheocon đã viết:
Bạn phải dùng cột phụ để tạo mảng không trùng trước, sau đó mới đưa vào DV, theo mình biết không thể đưa mảng trực tiếp vào DV được đâu bạn vì DV chỉ nhận Range thôi!!!Không biết còn cách nào để đưa mảng vào DV không, mình cũng muốn biết thêm chỗ này!!!

Nếu muốn làm 1 file mà nhập xuất chẳng hạn không sử dụng VBA vậy thì khi khi thêm cột phụ bên cạnh thì bài toán này được giải quyết đúng không ạ, Để em thử nếu làm được thì hay quá bởi em luôn quan tâm bài toán này mà không sử dụng VBA

leoheocon · 27/10/16

vanvan9697 đã viết:
Nếu muốn làm 1 file mà nhập xuất chẳng hạn không sử dụng VBA vậy thì khi khi thêm cột phụ bên cạnh thì bài toán này được giải quyết đúng không ạ, Để em thử nếu làm được thì hay quá bởi em luôn quan tâm bài toán này mà không sử dụng VBA

Cột phụ thì được, bạn xem file ví dụ của mình dưới đây!!!

minhtungph · 27/10/16

vanvan9697 đã viết:
Nếu muốn làm 1 file mà nhập xuất chẳng hạn không sử dụng VBA vậy thì khi khi thêm cột phụ bên cạnh thì bài toán này được giải quyết đúng không ạ, Để em thử nếu làm được thì hay quá bởi em luôn quan tâm bài toán này mà không sử dụng VBA

nếu dùng cột phụ thì làm đơn giản hơn nữa bạn, mình ko cần gom các giá trị cho sát lại với nhau, dùng hàm small để lấy các giá trị từ thấp đến cao

HieuCD · 27/10/16

minhtungph đã viết:
nếu dùng cột phụ thì làm đơn giản hơn nữa bạn, mình ko cần gom các giá trị cho sát lại với nhau, dùng hàm small để lấy các giá trị từ thấp đến cao

nếu bạn không gom các giá trị sát lại nhau, thì bạn phải làm tiếp bước 2 dùng hàm small để gom lại, và bạn không quan sát trực tiếp kết quả đúng không, nếu dữ liệu gốc 200 dòng thì cột phụ phải 200 dòng, nếu gom trước thì số dòng sẽ ít hơn và dể kiểm soát kết quả hơn.

minhtungph · 27/10/16

HieuCD đã viết:
nếu bạn không gom các giá trị sát lại nhau, thì bạn phải làm tiếp bước 2 dùng hàm small để gom lại, và bạn không quan sát trực tiếp kết quả đúng không, nếu dữ liệu gốc 200 dòng thì cột phụ phải 200 dòng, nếu gom trước thì số dòng sẽ ít hơn và dể kiểm soát kết quả hơn.

Làm đường nào thì bạn cũng phải dự trù kết quả bằng với dữ liệu rồi, chứ gôm lại sát nhau thì bạn cũng đâu biết có bao nhiêu unique

quocgiacan · 27/10/16

leoheocon đã viết:
Cột phụ thì được, bạn xem file ví dụ của mình dưới đây!!!

Chào em trai!

Cái vụ này nghe quen quá hả em trai! anh cũng đang muốn tìm Data Validation mảng ảo. /-*+/

Chúc em ngày vui

HieuCD · 27/10/16

minhtungph đã viết:
Làm đường nào thì bạn cũng phải dự trù kết quả bằng với dữ liệu rồi, chứ gôm lại sát nhau thì bạn cũng đâu biết có bao nhiêu unique

khi copy xuống bạn sẽ biết điểm dừng, chủ yếu là gom trước thao tác đơn giản dể kiểm soát hơn, và công thức cũng không phức tạp hơn gom sau.dĩ nhiên mỗi người có cách làm khác nhau, tùy sở trường và sở thích

HieuCD · 27/10/16

quocgiacan đã viết:
Chào em trai!

Cái vụ này nghe quen quá hả em trai! anh cũng đang muốn tìm Data Validation mảng ảo.

Chúc em ngày vui

hy vọng Excel 2020 có chức năng nầy, và có thể không cần gỏ lệnh, chỉ cần la vào Micro "tạo danh sách không trùng từ vùng dữ liệu...." là có ngay. /-*+/

quocgiacan · 27/10/16

HieuCD đã viết:
hy vọng Excel 2020 có chức năng nầy, và có thể không cần gỏ lệnh, chỉ cần la vào Micro "tạo danh sách không trùng từ vùng dữ liệu...." là có ngay.

Chào bạn hiền!

Đừng, đừng, đừng ...Bạn đừng nghĩ "quấy" vậy chứ, lúc đó làm sao còn "đất" cho anh em mình gầy bàn để "nhậu", để quậy với nhau chứ, khà khà.

Cái vụ hét vào micro là cũng lạc hậu rồiiii! lúc đó phải là: vừa mới nghĩ trong đầu: "Bổn tọa muốn làm danh sách khử trùng, đưa vào mảng ảo để làm suột cho DV, cấp cấp nghe lệnh......", nó sẽ bắt sóng điện não và chạy trình tự động cấp kỳ hiện ra 1........ chai alcon sát trùng --=0

Chúc anh em ngày vui

ndu96081631 · 27/10/16

minhtungph đã viết:
Cám ơn bạn, cái vụ mảng hay không mảng chắc nhờ admin đi ngang giải thích giúp.

Thử nghiệm:
- Quét chọn E1:E13
- Gõ vào thanh Formula công thức:

Mã:

=INDEX(A1:A13,,)

- Bấm Ctrl + Shift + Enter
thấy cũng ra kết quả cứ như là mảng vậy nhưng nhìn cũng biết đó không phải mảng
Hic... tại thằng INDEX nó.. vậy đấy! Ai biết cái gì trong trái ổi của.. Bill
---------------
Còn vụ Validation thì có thể kết luận chắc chắn rằng:
- Validation list chỉ nhận các phần tử CÓ THẬT nằm trên bảng tính
- Validation không chơi với cái mà các bạn gọi là "mảng ảo"

nmhung49 · 27/10/16

quocgiacan đã viết:
Chào bạn hiền!

Đừng, đừng, đừng ...Bạn đừng nghĩ "quấy" vậy chứ, lúc đó làm sao còn "đất" cho anh em mình gầy bàn để "nhậu", để quậy với nhau chứ, khà khà.

Cái vụ hét vào micro là cũng lạc hậu rồiiii! lúc đó phải là: vừa mới nghĩ trong đầu: "Bổn tọa muốn làm danh sách khử trùng, đưa vào mảng ảo để làm suột cho DV, cấp cấp nghe lệnh......", nó sẽ bắt sóng điện não và chạy trình tự động cấp kỳ hiện ra 1........ chai alcon sát trùng

Chúc anh em ngày vui

Cho ké vài chai alcon với giờ ai dùng công thức mạng cho lọc duy nhất lạc hậu rồi -+*/

Lấy file bài 12 ví du đặt công thức ở D2

=INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($D$11,$A$2:$A$10),0,0),0))

quocgiacan · 27/10/16

nmhung49 đã viết:
Cho ké vài chai alcon với giờ ai dùng công thức mạng cho lọc duy nhất lạc hậu rồi

Lấy file bài 12 ví du đặt công thức ở D2

=INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($D$11,$A$2:$A$10),0,0),0))

Chào nmhung49,

Ối chu choa! lọa rứa. Thêm bạn "nữ" tham gia CLB "dùng alcon nhậu nhẹt". Khà khà vui à nha!
Không biết bạn là hàng xóm của lehuong1409 hay bạn giống doveandrose (hình đi đường hình, người đi đường người, lầm chết)

Chúc bạn ngày vui

minhtungph · 27/10/16

ndu96081631 đã viết:
Thử nghiệm:
- Quét chọn E1:E13
- Gõ vào thanh Formula công thức:

Mã:

=INDEX(A1:A13,,)

- Bấm Ctrl + Shift + Enter
thấy cũng ra kết quả cứ như là mảng vậy nhưng nhìn cũng biết đó không phải mảng
Hic... tại thằng INDEX nó.. vậy đấy! Ai biết cái gì trong trái ổi của.. Bill
---------------
Còn vụ Validation thì có thể kết luận chắc chắn rằng:
- Validation list chỉ nhận các phần tử CÓ THẬT nằm trên bảng tính
- Validation không chơi với cái mà các bạn gọi là "mảng ảo"

Cám ơn thầy rất nhiều

HieuCD · 27/10/16

nmhung49 đã viết:
Cho ké vài chai alcon với giờ ai dùng công thức mạng cho lọc duy nhất lạc hậu rồi

Lấy file bài 12 ví du đặt công thức ở D2

=INDEX($A$2:$A$10,MATCH(0,INDEX(COUNTIF($D$11,$A$2:$A$10),0,0),0))

là công thức mảng đó,chỉ có điều là khỏi nhấn Ctrl+Shift+Enter thôi

nmhung49 · 27/10/16

HieuCD đã viết:
là công thức mảng đó,chỉ có điều là khỏi nhấn Ctrl+Shift+Enter thôi

Ah thì ra vậy đó giờ cứ thế mà bữa giờ bạn nói mới để ý, mà nói chung không CSE là được vì quên nhấn CSE dễ bị sai

vanvan9697 · 29/10/16

Thank các anh nhé, em hiểu cách thức làm chứ em cứ lấn cấn trên data valiation mà cứ lấn cấn }}}}}

Tạo Validation unique list không dùng cột phụ (1 người xem)

Người dùng đang xem chủ đề này

None

File đính kèm

Thành viên tiêu biểu

None

Thành viên tiêu biểu

None

Thành viên tiêu biểu

None

Thành viên tiêu biểu

Thành viên chính thức

Thành viên tiêu biểu

Thành viên chính thức

Thành viên tiêu biểu

File đính kèm

None

Chuyên gia GPE

None

-⍟-

Chuyên gia GPE

Chuyên gia GPE

-⍟-

Huyền thoại GPE

Thành viên tích cực

-⍟-

None

Chuyên gia GPE

Thành viên tích cực

Thành viên chính thức

Thời gian đếm ngược.