Giúp về tham chiếu đến một vị trí trong bảng

[theblueserenade]

Tôi có một bảng dữ liệu từ A2:E31.Từ trên xuống dưới lần lượt là các ký tự ZA, HA, ZB, ZC... Tôi muốn tìm kiếm và tham chiếu đến một vị trí trên cột E là lần xuất hiện thứ 5 của cả hai ký tự ZA và ZB, tức là đếm từ trên xuống dưới trong cột E, khi gặp dòng nào chứa ký tự ZA hoặc ZB thì cộng thêm 1, đến lần xuất hiện thứ 5 thì tham chiếu đến giá trị đó. Tôi muốn công thức chỉ trong 1 ô và tham chiếu đến ô cần tìm. Tôi cũng nghĩ mà chưa ra cách nào thỏa mãn cả, mong các bạn có thể giúp, xin cảm ơn.

 

[ndu96081631]

Tôi có một bảng dữ liệu từ A2:E31.Từ trên xuống dưới lần lượt là các ký tự ZA, HA, ZB, ZC... Tôi muốn tìm kiếm và tham chiếu đến một vị trí trên cột E là lần xuất hiện thứ 5 của cả hai ký tự ZA và ZB, tức là đếm từ trên xuống dưới trong cột E, khi gặp dòng nào chứa ký tự ZA hoặc ZB thì cộng thêm 1, đến lần xuất hiện thứ 5 thì tham chiếu đến giá trị đó. Tôi muốn công thức chỉ trong 1 ô và tham chiếu đến ô cần tìm. Tôi cũng nghĩ mà chưa ra cách nào thỏa mãn cả, mong các bạn có thể giúp, xin cảm ơn.

Hay là bạn cứ nói rõ MỤC ĐÍCH CUỐI CÙNG của bạn là gì luôn đi
Đừng cố hướng người khác đi theo hướng mà bạn cho là đúng
(Biết được mục đích cuối cùng, có khi sẽ tìm ra phương pháp còn dễ hơn)

 

[theblueserenade]

Mục đích cuối cùng của mình là muốn tham chiếu đến giá trị một ô ở cột E mà ô đó thoả mãn điều kiện là chứa ký tự ZA hoặc ZB và là lần xuất hiện thứ 5 của hai ký tự ZA hoặc ZB.

 

[leonguyenz]

Mục đích cuối cùng của mình là muốn tham chiếu đến giá trị một ô ở cột E mà ô đó thoả mãn điều kiện là chứa ký tự ZA hoặc ZB và là lần xuất hiện thứ 5 của hai ký tự ZA hoặc ZB.

Bạn nên nói rõ đặt công thức tại ô nào, kết quả trả ra là gì, ở đâu? Bạn nói tham chiếu đến một ô ở cột E thì nói rõ luôn là ô nào (tính nhẩm theo cách của bạn). Bạn nói tham chiếu đến làm nhiều người hiểu nhầm, có thể là liên kết đến, hay lấy giá trị, hay gì gì đó ... Nên đưa file ví dụ với kết quả được tính tay thì dễ hiểu hơi giải thích của bạn.

 

[VMH0307]

Bạn có thể dùng công thức như thế này:

=OFFSET($E$1,COUNTA($A$2:$A$31)-LARGE(((LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZA"[/COLOR])[COLOR=#ff0000]+[/COLOR](LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZB"[/COLOR]))*(COUNTA($A$2:$A$31)-ROW(INDIRECT("1:"&COUNTA($A$2:$A$31)))),[COLOR=#ff0000]5[/COLOR]),,)

Ấn Ctrl+Shift+Enter
Nhìn chung, dùng công thức mảng tôi thấy cũng không khoái lắm
Bạn nên tham khảo ý kiến thầy Ndu bên trên.

 

[VMH0307]

Bạn có thể dùng công thức như thế này:

=OFFSET($E$1,COUNTA($A$2:$A$31)-LARGE(((LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZA"[/COLOR])[COLOR=#ff0000]+[/COLOR](LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZB"[/COLOR]))*(COUNTA($A$2:$A$31)-ROW(INDIRECT("1:"&COUNTA($A$2:$A$31)))),[COLOR=#ff0000]5[/COLOR]),,)

Ấn Ctrl+Shift+Enter
Nhìn chung, dùng công thức mảng tôi thấy cũng không khoái lắm
Bạn nên tham khảo ý kiến thầy Ndu bên trên.

 

[ndu96081631]

Mục đích cuối cùng của mình là muốn tham chiếu đến giá trị một ô ở cột E mà ô đó thoả mãn điều kiện là chứa ký tự ZA hoặc ZB và là lần xuất hiện thứ 5 của hai ký tự ZA hoặc ZB.

Câu này bạn đã nói ở bài 1 rồi (và tôi hiểu yêu cầu này)
Vấn đề là: Tham chiếu như vậy nhằm mục đích gì? Ý tôi muốn nói đến MỤC ĐÍCH CUỐI CÙNG (hổng phải hỏi QUÁ TRÌNH)

 

[theblueserenade]

Cảm ơn các bạn đã cho nhận xét, nếu kết quả tính nhẩm thì sẽ là ZA-413 trên cột E tương ứng với 5 lần xuất hiện của ZA và ZB, mục đích cuối cùng là mình muốn tìm xem lần xuất hiện thứ 5 ấy tương ứng là thời điểm mấy giờ, nếu là 5 thì ở đây sẽ là 9:00 và sau khi tìm được giá trị tham chiếu là ZA-413 thì mình sẽ vlookup giá trị ZA-413 đó để tìm giờ trên cột D. Kết quả mình muốn trả về là 1 ô, cụ thể là ô AB3. Tức là tìm kiếm làm sao để ô AB3 trả về giá trị là ZA-413.

 

[theblueserenade]

Bạn có thể dùng công thức như thế này:

=OFFSET($E$1,COUNTA($A$2:$A$31)-LARGE(((LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZA"[/COLOR])[COLOR=#ff0000]+[/COLOR](LEFT($A$2:$A$31,2)=[COLOR=#ff0000]"ZB"[/COLOR]))*(COUNTA($A$2:$A$31)-ROW(INDIRECT("1:"&COUNTA($A$2:$A$31)))),[COLOR=#ff0000]5[/COLOR]),,)

Ấn Ctrl+Shift+Enter
Nhìn chung, dùng công thức mảng tôi thấy cũng không khoái lắm
Bạn nên tham khảo ý kiến thầy Ndu bên trên.

Cảm ơn bạn, công thức trên của bạn đã thỏa mãn yêu cầu rồi, mình nghĩ mãi không ra mà sao bạn làm ra nhanh vậy, bái phục đấy. Mình cũng không thích công thức mảng vì nó làm chậm quá trình tính toán nhưng nếu không có sự lựa chọn nào khác thì buộc phải chấp nhận thôi.

 

[theblueserenade]

Bạn tttmuahmuah có thể giải thích hộ mình công thức trên không. Mình muốn tìm hiểu để sau này có thể tự làm được. Mình cảm ơn

 

[theblueserenade]

Mình muốn hỏi thêm một chút, vì dữ liệu của mình có nhiều dòng, khoảng 100 dòng nên nếu chạy công thức mảng thì mình sợ file sẽ bị chậm, máy ở công ty thì cũng không phải loại tốt lắm. Bạn nào có cách khác không dùng công thức mảng không.

Mình mô tả lại bài toàn như sau cho các bạn dễ hiểu: Mình cần tìm vị trí dòng của lần xuất hiện thứ k (k cho trước) của các giá trị trong một dãy giá trị, sau đó trả về giá trị của dòng ứng với lần xuất hiện thứ k đó.

Cụ thể là đếm số lần xuất hiện thứ 4 của ký tự "A" và "B" trong dãy B2:B11, kết quả sẽ là dòng thứ 6, ứng với giá trị AZ, nếu số lần xuất hiện là 5 thì kết quả sẽ là dòng thứ 8, ứng với giá trị BG.

Mình xin chân thành cảm ơn.

 

[theblueserenade]

Hàm đếm lần xuất hiện và tìm vị trí 1 ô trong dãy

Mình muốn hỏi thêm một chút, vì dữ liệu của mình có nhiều dòng, khoảng 100 dòng nên nếu chạy công thức mảng thì mình sợ sẽ bị chậm, máy ở công ty thì cũng không phải loại tốt lắm. Bạn nào có cách khác không dùng công thức mảng không.

Mình mô tả lại bài toàn như sau cho các bạn dễ hiểu: Mình cần tìm vị trí dòng của lần xuất hiện thứ k (k cho trước) của các giá trị trong một dãy giá trị, sau đó trả về giá trị của dòng ứng với lần xuất hiện thứ k đó.

Cụ thể là đếm số lần xuất hiện thứ 4 của ký tự "A" và "B" trong dãy B2:B11, kết quả sẽ là dòng thứ 6, ứng với giá trị AZ, nếu số lần xuất hiện là 5 thì kết quả sẽ là dòng thứ 8, ứng với giá trị BG.

 

[huyhoang1768]

Mình muốn hỏi thêm một chút, vì dữ liệu của mình có nhiều dòng, khoảng 100 dòng nên nếu chạy công thức mảng thì mình sợ sẽ bị chậm, máy ở công ty thì cũng không phải loại tốt lắm. Bạn nào có cách khác không dùng công thức mảng không.

Mình mô tả lại bài toàn như sau cho các bạn dễ hiểu: Mình cần tìm vị trí dòng của lần xuất hiện thứ k (k cho trước) của các giá trị trong một dãy giá trị, sau đó trả về giá trị của dòng ứng với lần xuất hiện thứ k đó.

Cụ thể là đếm số lần xuất hiện thứ 4 của ký tự "A" và "B" trong dãy B2:B11, kết quả sẽ là dòng thứ 6, ứng với giá trị AZ, nếu số lần xuất hiện là 5 thì kết quả sẽ là dòng thứ 8, ứng với giá trị BG.

dùng Vlookup đi bạn, bạn tìm hiểu thêm hàm vlookup, không hiểu thì hỏi lại

 

[hieuxd]

Mình muốn hỏi thêm một chút, vì dữ liệu của mình có nhiều dòng, khoảng 100 dòng nên nếu chạy công thức mảng thì mình sợ sẽ bị chậm, máy ở công ty thì cũng không phải loại tốt lắm. Bạn nào có cách khác không dùng công thức mảng không.

Mình mô tả lại bài toàn như sau cho các bạn dễ hiểu: Mình cần tìm vị trí dòng của lần xuất hiện thứ k (k cho trước) của các giá trị trong một dãy giá trị, sau đó trả về giá trị của dòng ứng với lần xuất hiện thứ k đó.

Cụ thể là đếm số lần xuất hiện thứ 4 của ký tự "A" và "B" trong dãy B2:B11, kết quả sẽ là dòng thứ 6, ứng với giá trị AZ, nếu số lần xuất hiện là 5 thì kết quả sẽ là dòng thứ 8, ứng với giá trị BG.

không hiểu số lần xuất hiện của bạn nói như thế nào
hay mô tả một đằng File lại khác
Theo mình thì số lần xuất hiện thì phải là của phần tử nào giá trị nào thì mới có số lần
Xem trong file chưa thây giá trị nào xuất hiện 4 lần cả đê mà lấy dòng 6

 

[thanhtrg]

Mình muốn hỏi thêm một chút, vì dữ liệu của mình có nhiều dòng, khoảng 100 dòng nên nếu chạy công thức mảng thì mình sợ sẽ bị chậm, máy ở công ty thì cũng không phải loại tốt lắm. Bạn nào có cách khác không dùng công thức mảng không.

Mình mô tả lại bài toàn như sau cho các bạn dễ hiểu: Mình cần tìm vị trí dòng của lần xuất hiện thứ k (k cho trước) của các giá trị trong một dãy giá trị, sau đó trả về giá trị của dòng ứng với lần xuất hiện thứ k đó.

Cụ thể là đếm số lần xuất hiện thứ 4 của ký tự "A" và "B" trong dãy B2:B11, kết quả sẽ là dòng thứ 6, ứng với giá trị AZ, nếu số lần xuất hiện là 5 thì kết quả sẽ là dòng thứ 8, ứng với giá trị BG.

Bạn xem file đính kèm !

 

[theblueserenade]

Cảm ơn bạn thanhtrg, nhưng mình muốn hỏi là có cách nào không dùng công thức mảng hoặc là có thể đặt name động được không vì mình phải tìm kiếm số lần xuất hiện của các giá trị thay đổi và không cố định, có thể là A và C hoặc B và E hoặc A và E và B. Số lần phải tìm kiếm các bộ giá trị đó có thể lên tới 200, vậy nếu dùng công thức mảng mình sợ máy sẽ chạy chậm, có thể bị đơ máy.

Mình up lại file, các bạn xem hộ mình nhé.

 

[thanhtrg]

Cảm ơn bạn thanhtrg, nhưng mình muốn hỏi là có cách nào không dùng công thức mảng hoặc là có thể đặt name động được không vì mình phải tìm kiếm số lần xuất hiện của các giá trị thay đổi và không cố định, có thể là A và C hoặc B và E hoặc A và E và B. Số lần phải tìm kiếm các bộ giá trị đó có thể lên tới 200, vậy nếu dùng công thức mảng mình sợ máy sẽ chạy chậm, có thể bị đơ máy.

Mình up lại file, các bạn xem hộ mình nhé.

Nếu dùng name động thì có thể làm như sau

 

[theblueserenade]

Ok, thank you bạn nhé, mình làm được rồi

 

[thanhtrg]

Ok, thank you bạn nhé, mình làm được rồi

Nếu làm theo bài #17 thì sửa lại công thức name như sau (để tránh nhầm lẫn):
MA = OFFSET(Sheet1!$B$3,,,ROW()-ROW(Sheet1!$B$3)+1)