Dùng công thức gì để trả về địa chỉ của một ô, mà lại có thể dùng làm tham chiếu?

[BNTT]

Đuối rồi. Nghĩ mãi mà không ra. Đành làm phiền các bạn vậy.

Tôi đang muốn tìm một công thức, trả về địa chỉ của một ô cụ thể, mà công thức này lại có thể dùng làm tham chiếu cho một hàm khác.

Nói sao cho dễ hiểu nhỉ. Vì chỉ là ý tưởng nên không có file mẫu. Thôi đại khái là vầy:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

1​

​

|

Mã số​

​

|

Giá trị​

​

|

​

​

|

Mã Số cần xác định giá trị:​

​

|

2​

​

|

001​

|

A​

| |

007​

|

3​

​

|

002​

|

B​

| |

005​

|

4​

​

|

003​

|

C​

| |

004​

|

5​

​

|

004​

|

D​

| |

001​

|

6​

​

|

005​

|

E​

| |

008​

|

7​

​

|

006​

|

F​

| |

006​

|

8​

​

|

007​

|

G​

| |

003​

|

9​

​

|

008​

|

H​

| |

002​

|

10​

​

|

009​

|

I​

| |

009​

|

11​

​

|

010​

|

J​

| |

010​

|

Nếu như dùng VLOOKUP, lấy giá trị tại D2 (là 007) đi dò trong cái bảng A2:B11, để lấy ra giá trị của 007 là G, thì là chuyện nhỏ:

=VLOOKUP(D2, $A$2:$B$11, 2, 0)​

Vấn đề của tôi ở đây là: Làm sao biết 007 đang nằm ở D2 ?

Nghĩa là dùng công thức nào để xác định được cái ô chứa 007 (biết nó nằm ở cột D nhưng ô nào thì không biết) là ô D2 ?

Và làm sao để dùng được công thức đó làm tham chiếu cho VLOOKUP (hoặc bất kỳ một hàm nào khác) ?


Cái khó là dùng công thức đó làm tham chiếu cho một hàm khác.

Tôi đã thử cái này để tìm 007 đang nằm ở đâu trong cột D

="D" & MATCH("007", D120, 0)​

Đúng là tôi có kết quả = D2

Nhưng dùng cái công thức đó làm tham chiếu cho VLOOKUP thì nó hổng chịu:

=VLOOKUP("D" & MATCH("007", D120, 0), $A$2:$B$11, 2, 0)​

Nếu dùng như vậy, thì cái D2 này chính là "D2" (có hai dấu móc kép ở 2 đầu), do đó VLOOKUP không hiểu.

Tôi vật lộn với nó cả chiều nay mà không ra. Càng mò càng lú.

Các bạn giúp dùm. Xin cảm ơn trước.

 

[ndu96081631]

Công thức ="D" & MATCH("007", D120, 0) trả về dạng chuối! Muốn biến chuối trên thành tham chiếu thì cho vào INDIRECT

 

[ptm0412]

Khổ ông trùm quá đi mất, không biết có phải đang ngồi trên lửa không nữa.
Đã có "D2" thì Indirect("D2")
Cứu hoả thì thế này:
=VLOOKUP(indirect("D" & MATCH("007", D120, 0)), $A$2:$B$11, 2, 0)

Còn không thì
=VLOOKUP(indirect(ADDRESS(MATCH("007";D120;0);4)),$A$2:$B$11, 2, 0)

Address dài hơn nhưng có thể nhét tên sheet vào luôn.

Chuối là dạng viết khác của chuỗi đó mà.

 

[Mr Okebab]

Nhưng tại sao lại lòng vòng như vậy :

Bác chỉ muốn : Tìm giá trị của ô ở cột B tương đương với ô có chưá chuỗi "007" ở cột A !!

Em hỏi thêm : Vị trí của D2 (tức ô có chứa "007") có quan trọng gì không, bởi cuối cùng ta vẫn tìm giá trị 007 ở cột A cơ mà

Bác cứ thử cho 007 ở cột D xuống mấy hàng thì cái bác tìm ở cột A đâu có thay đổi đâu.

Thân!

P/S : Do Vlookup chỉ là tham chiếu một chiều, từ trái qua phải (Giá trị tham chiếu luôn ở bên trái), nên không làm được. Do đó người ta phải dùng tham chiếu nhiều chiều, đó là sự kết hợp giữa INDEX và MATCH

 

[ndu96081631]

Tôi nghĩ người ta hay dùng kiểu này trong trường hợp muốn dùng 1 công thức duy nhất cho tất cả các sheet đấy!
Nếu đặt name kiểu bình thường thì dính tên sheet, qua sheet khác hỏng xài được, nên INDIRECT là cách hợp lý nhất

="D" & MATCH("007", D120, 0)

mà nằm ở bất cứ sheet nào thì nó vẩn cứ là cell D2 (mặc định là đang nói đến ActiveSheet)

 

[BNTT]

Cái D2 không quan trọng, VLOOKUP cũng không quan trọng. Nhưng cái công thức tìm ra 007 nằm ở đâu thì quan trọng.

Vả lại, mình nêu cái VLOOKUP ra chỉ là để làm ví dụ.

Cái mình muốn là dùng cái công thức đó để làm tham chiếu cho bất cứ hàm gì cần đến tham chiếu.

 

[huuthang_bd]

Khổ ghê! Lấy địa chỉ của một ô chứa số liệu biết trước để làm tham chiếu cho một công thức khác thì sao các bác không lấy luôn số liệu đó điền vào công thức luôn??? Việc gì phải làm lòng vòng như thế. Hình rãnh rỗi quá, mà diễn đàn không có bài khó các bác tự nghĩ ra mấy chuyện hóc búa (nhưng không hữu ích) để suy nghĩ chơi hay sao ấy. Một cách để rèn luyện trí não chăng .
P/S: Em chỉ nói đùa cho vui thôi, không có ý gì khác hết. Các bác đừng phật ý nhé. xả "xì trét" mà )

 

[ndu96081631]

Khổ ghê! Lấy địa chỉ của một ô chứa số liệu biết trước để làm tham chiếu cho một công thức khác thì sao các bác không lấy luôn số liệu đó điền vào công thức luôn??? Việc gì phải làm lòng vòng như thế. Hình rãnh rỗi quá, mà diễn đàn không có bài khó các bác tự nghĩ ra mấy chuyện hóc búa (nhưng không hữu ích) để suy nghĩ chơi hay sao ấy. Một cách để rèn luyện trí não chăng .
P/S: Em chỉ nói đùa cho vui thôi, không có ý gì khác hết. Các bác đừng phật ý nhé. xả "xì trét" mà )

Hoàn toàn không vô lý đâu
Bạn hãy thử đặt 1 name có dùng hàm VLOOKUP sao cho nó có tác dụng trên mọi sheet xem thử
Xem bài này:
http://www.giaiphapexcel.com/forum/showthread.php?t=2163&page=2

 

[Mr Okebab]

Cái D2 không quan trọng, VLOOKUP cũng không quan trọng. Nhưng cái công thức tìm ra 007 nằm ở đâu thì quan trọng.

Vả lại, mình nêu cái VLOOKUP ra chỉ là để làm ví dụ.

Cái mình muốn là dùng cái công thức đó để làm tham chiếu cho bất cứ hàm gì cần đến tham chiếu.

Thì chuỗi "007" cũng có thể dùng cho bất cứ hàm nào mà bác ??

Em vẫn chưa hiểu hết ý bác ??

Bác có thể cho 1 File VD để thấy rằng cách làm của bác là ngắn nhất ??

Thân!

 

[BNTT]

Thì chuỗi "007" cũng có thể dùng cho bất cứ hàm nào mà bác ??

Em vẫn chưa hiểu hết ý bác ??

Bác có thể cho 1 File VD để thấy rằng cách làm của bác là ngắn nhất ??

Thân!

Một ví dụ nhá.

Giả sử có một bảng giá trị, xếp theo thứ tự tăng dần như sau:

|

A​

​

|

1​

​

|

Giá trị​

​

|

2​

​

|

4​

|

3​

​

|

7​

|

4​

​

|

9​

|

5​

​

|

24​

|

6​

​

|

25​

|

Các con số trong bảng giá trị này có thể thay đổi.

Bi giờ, người ta nói rằng:

Cho trước một con số X, đem X đi dò với cái bảng giá trị ở trên, nếu nó bằng một giá trị trong đó thì thôi, không làm gì hết, sử dụng luôn chính số đó. Bằng không, nếu X lọt vào trong một khoảng giá trị, thì lấy ra giá trị nào mà gần với X hơn.
​

Để dò xem con số được cho có trùng với đứa nào trong cái bảng giá trị không thì dễ rồi, dùng VLOOKUP với cách dò chính xác, nếu không #NA là chính nó.

Vấn đề là, nếu con số được cho nằm lọt trong một khoảng, ta phải xác định cận trên và cận dưới của cái khoảng đó là bao nhiêu? Vì như tôi đã nói, các con số trong bảng giá trị trên có thể thay đổi, thậm chí là số lẻ, và ta không được biết trước đó là số nào, chỉ biết rằng nó được sắp xếp từ nhỏ đến lớn.

Ta có thể dùng VLOOKUP để lấy ra con số lớn nhất mà nhỏ hơn con số đã cho (ở đây tôi giả sử X = 20 nhé):

=VLOOKUP(20, A1:A6, 1) = 9​

Được một chú. Và biết luôn chú này là cận dưới.

Giờ tìm cận trên, là con số lớn hơn X mà lại liền kề với con số cận dưới, trong cái bảng đó.

Tôi có nghĩ đến OFFSET, tức là khi xác định được cận dưới rồi, ta tìm vị trí của nó nằm ở đâu trong cái bảng giá trị, sau đó từ cái vị trí đó dời xuống 1 hàng, là ra cận trên.

Và vấn đề là tìm ra vị trí của cận dưới (kết quả của hàm VLOOKUP ở trên), rồi dùng nó làm tham chiếu cho OFFSET để xác định cận trên.

Tôi biết bảng giá trị này nằm ở cột A, do đó tôi dùng công thức (xin dùng tiếp X=20):

="A" & MATCH(VLOOKUP(20, A2:A6, 1), A2:A6) + 1 = A4​

Bây giờ dùng cái kết quả đó làm tham chiếu cho OFFSET, mà vì cái kết quả tôi có đó, là một chuỗi, nên không làm tham chiếu cho OFFSET được. Bởi vậy nên mới có topic này.

Giờ thì tôi đã biết rồi, là dùng INDIRECT để chuyển cái chuỗi đó thành tham chiếu.

Công thức xác định cận trên của tôi như sau:

=OFFSET(INDIRECT("A" & MATCH(VLOOKUP(20, A2:A6, 1), A2:A6) + 1), 1, 0) = 24​

Hay công thức tổng quát để tìm cận trên của X là:

=OFFSET(INDIRECT("A" & MATCH(VLOOKUP(X, A2:A6, 1), A2:A6) + 1), 1, 0)​

Còn chuyện so sánh con số phải tìm (là 20 trong ví dụ này) với cận trên và cận dưới, xem nó gần đứa nào hơn, thì tôi làm được.

-----------------------

Trên đây là lý do tại sao có cái topic này. Chứ không phải là:

Khổ ghê! Lấy địa chỉ của một ô chứa số liệu biết trước để làm tham chiếu cho một công thức khác thì sao các bác không lấy luôn số liệu đó điền vào công thức luôn??? Việc gì phải làm lòng vòng như thế. Hình rãnh rỗi quá, mà diễn đàn không có bài khó các bác tự nghĩ ra mấy chuyện hóc búa (nhưng không hữu ích) để suy nghĩ chơi hay sao ấy. Một cách để rèn luyện trí não chăng .
P/S: Em chỉ nói đùa cho vui thôi, không có ý gì khác hết. Các bác đừng phật ý nhé. xả "xì trét" mà )

Xin nói rõ hơn tí nữa. Bắp hỏi tại sao tôi không dùng cái 007 mà lại đi tìm địa chỉ của nó cho lòng vòng... Xin lỗi vì cái bài đó tôi nêu ví dụ hơi dễ quá, làm các bạn hiểu lầm.

Với bài này, ta chỉ biết mỗi cái bảng giá trị để so sánh nó nằm ở cột nào mà thôi, còn trong đó có bao nhiêu con số, là những con số gì, thì ta không biết, do đó không thể lấy đích danh một con số nào đó mà đi dò vị trí của nó được. Rồi khó hơn ở chỗ lại phải xác định hai con số liên tiếp, là cận trên và cận dưới của một khoảng có chứa con số X đã biết trước...

 

[ThuNghi]

Một ví dụ nhá.

Giả sử có một bảng giá trị, xếp theo thứ tự tăng dần như sau:

|

A​

|

1​

|

Giá trị​

|

2​

|

4​

|

3​

|

7​

|

4​

|

9​

|

5​

|

24​

|

6​

|

25​

|

Các con số trong bảng giá trị này có thể thay đổi.

Bi giờ, người ta nói rằng:


Để dò xem con số được cho có trùng với đứa nào trong cái bảng giá trị không thì dễ rồi, dùng VLOOKUP với cách dò chính xác, nếu không #NA là chính nó.


Giờ tìm cận trên, là con số lớn hơn X mà lại liền kề với con số cận dưới, trong cái bảng đó.

Nếu chỉ tìm X nằm ở giữa 2 số, mà dãy này tăng dần thì dùng match(...,...,1) là OK. Chưa hiểu Tường đang muốn gì. Xem thử bài 9 link này thử.
http://www.giaiphapexcel.com/forum/showthread.php?p=90586#post90586

 

[BNTT]

Nếu chỉ tìm X nằm ở giữa 2 số, mà dãy này tăng dần thì dùng match(...,...,1) là OK. Chưa hiểu Tường đang muốn gì.

Dùng MATCH là làm sao? Bác đọc không kỹ rồi.
Đây là bài toán (do em nghĩ ra):

Cho trước một bảng (một cột) gồm một số các giá trị, sắp theo thứ tự tăng dần, tạm gọi là bảng A. Bây giờ, lấy một con số, X chẳng hạn, đi dò với cái bảng A đó. Nếu X trùng với một giá trị nào đó trong A, thì lấy chính X. Nếu không, thì lấy giá trị nào mà gần với X nhất.

Làm sao mà dùng MATCH được.

Em lấy lại ví dụ ở trên, lấy 20 đi dò với bảng A1:A6, nó lọt vào khoảng giữa 9 và 24, vì 20 gần 24 hơn, nên lấy 24.
Nhưng nếu lấy 11 mà đi dò với bảng đó, nó cũng lọt vào trong khoảng giữa 9 và 24, nhưng vì 11 gần 9 hơn là 24, nên lấy 9.

Ở đây, em đâu có biết trước được trong bảng đó có 9 hay 24 hay bao nhiêu đâu? Cho nên đâu có biết được 20 nó gần số nào? Lỡ mà trong bảng đó có thêm con số 18, thì em lại phải lấy số 18 chứ đâu có lấy 24 được (vì 18 gần với 20 hơn)...

Bác hiểu bài toán của em rồi chứ? nếu chưa hiểu thì mai sang đây em trình bày rõ hơn cho bác nghe nhé.

 

[Mr Okebab]

Một ví dụ nhá.


Công thức xác định cận trên của tôi như sau:

=OFFSET(INDIRECT("A" & MATCH(VLOOKUP(20, A2:A6, 1), A2:A6) + 1), 1, 0) = 24​

Hay công thức tổng quát để tìm cận trên của X là:

=OFFSET(INDIRECT("A" & MATCH(VLOOKUP(X, A2:A6, 1), A2:A6) + 1), 1, 0)​

Còn chuyện so sánh con số phải tìm (là 20 trong ví dụ này) với cận trên và cận dưới, xem nó gần đứa nào hơn, thì tôi làm được.

Cận dưới : Số nhỏ

=VLOOKUP(20;A2:A6;1;ISERROR(MATCH(20;A2:A6;0)))
​

Cận trên : Số lớn :

=INDEX(A2:A6;MATCH(20;A2:A6;1)+1;1)
​

Còn cái vụ dùng min để tìm số gần nhất thì OK rồi phải không bác.
Thân!

 

[BNTT]

Cảm ơn OKBAp nhiều. Công thức đó mình cũng đã nghĩ ra. Nhưng nó phải trong một bài toán cụ thể. Cái bài toán mình đưa ra đó chỉ là một trong những ví dụ mình đang "vò đầu bứt tóc" mà thôi..

Còn vấn đề chính, của mình, của cái topic này, vẫn là làm thế nào để đưa một công thức làm một tham chiếu. Giờ thì biết rồi, dùng INDIRECT.

Nhưng... thôi lỡ cho hỏi luôn, chứ tự mà nghĩ nữa e rằng mai không offline nổi. Là vầy:

Ở trên, mình có nói là biết trước cái bảng giá trị nằm trong cột nào đó, nên mới dùng công thức:

="A" & MATCH(VLOOKUP(20, A2:A6, 1), A2:A6) + 1​

Bi giờ, biết trước một cái giá trị nào đó nằm trong một cái dãy nào đó, thì làm sao để xác định địa chỉ của nó ?

Ví dụ, biết rằng trong khối ô A1:H10 có chứa con số 15 (duy nhất)
Dùng công thức, tìm cho ra 15 đang nằm ở đâu?

Hay là, biết trong khối ô A1:H10 có một cái chữ XYZ (duy nhất), vậy làm sao để biết địa chỉ của cái chữ đó?

 

[Mr Okebab]

Bi giờ, biết trước một cái giá trị nào đó nằm trong một cái dãy nào đó, thì làm sao để xác định địa chỉ của nó ?

Ví dụ, biết rằng trong khối ô A1:H10 có chứa con số 15 (duy nhất)
Dùng công thức, tìm cho ra 15 đang nằm ở đâu?

Hay là, biết trong khối ô A1:H10 có một cái chữ XYZ (duy nhất), vậy làm sao để biết địa chỉ của cái chữ đó?

Cái này chắc phải dùng VBA thôi.

Thân!

 

[ndu96081631]

Với bài này, ta chỉ biết mỗi cái bảng giá trị để so sánh nó nằm ở cột nào mà thôi, còn trong đó có bao nhiêu con số, là những con số gì, thì ta không biết, do đó không thể lấy đích danh một con số nào đó mà đi dò vị trí của nó được. Rồi khó hơn ở chỗ lại phải xác định hai con số liên tiếp, là cận trên và cận dưới của một khoảng có chứa con số X đã biết trước...

Tôi thì thấy ví dụ trên ứng dụng vào 1 việc hoàn toàn khác (đã từng làm)... Chứ việc xác định cận trên, dưới thì ai lại làm thế cho cực!
Dùng công thức mãng dựa trên phép so sánh:

{Vung >= d/k}

Và

{Vung <= d/k}

Còn cái này:

Ví dụ, biết rằng trong khối ô A1:H10 có chứa con số 15 (duy nhất)
Dùng công thức, tìm cho ra 15 đang nằm ở đâu?
Hay là, biết trong khối ô A1:H10 có một cái chữ XYZ (duy nhất), vậy làm sao để biết địa chỉ của cái chữ đó?

Thì lại càng quá dể... dựa vào công thức:
Tìm ROW: (công thức mãng)

=MAX(IF($A$1:$H$10=15,ROW($A$1:$H$10),""))

Tìm COLUMN: (Công thức mãng)

=MAX(IF($A$1:$H$10=15,COLUMN($A$1:$H$10),""))

Với ROW và COLUMN tìm được dùng hàm ADDRESS ráp chúng vào ---> Ra kết quả

 

[ThuNghi]

Tìm ROW: (công thức mãng)

Tìm COLUMN: (Công thức mãng)

Với ROW và COLUMN tìm được dùng hàm ADDRESS ráp chúng vào ---> Ra kết quả

Công thức trên quá hay.
Xin chuyển sang sumproduct
=ADDRESS(SUMPRODUCT(($A$1:$B$4=$B$5)*ROW($A$1:$B$4)),SUMPRODUCT(($A$1:$B$4=$B$5)*column($A$1:$B$4)))

Sorry, sai chính tả!

 

[BNTT]

Khâm phục! Nếu mà có cái box "Công thức mãng", phải mời bác Ndu làm MOD!
Đâu có cần đến VBA đâu nhỉ... (Sorry OKBAP)

Công thức trên quá hay.
Xin chuyển sang sumproduct
=ADDRESS(SUMPRODUCT(($A$1:$B$4=$B$5)*ROW($A$1:$B$4)),SUMPRODUCT(($A$1:$B$4=$B$5)*ROW($A$1:$B$4)))

Bác ThuNghi ơi, xem lại dùm:

|

A​

​

|

B​

​

|

C​

​

|

D​

​

|

E​

​

|

F​

​

|

G​

​

|

H​

​

|

I​

​

|

1​

​

|

DATA​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

​

​

|

2​

​

|

81​

|

92​

|

69​

|

81​

|

38​

|

34​

|

22​

|

70​

| |

3​

​

|

67​

|

22​

|

49​

|

10​

|

61​

|

65​

|

79​

|

79​

| |

4​

​

|

19​

|

71​

|

2​

|

85​

|

74​

|

66​

|

87​

|

82​

| |

5​

​

|

62​

|

13​

|

67​

|

49​

|

88​

|

71​

|

53​

|

70​

| |

6​

​

|

60​

|

71​

|

30​

|

7​

|

71​

|

97​

|

21​

|

36​

| |

7​

​

|

82​

|

34​

|

90​

|

91​

|

64​

|

56​

|

34​

|

96​

| |

8​

​

|

32​

|

64​

|

77​

|

35​

|

71​

|

53​

|

76​

|

38​

| |

9​

​

|

23​

|

44​

|

41​

|

26​

|

69​

|

86​

|

11​

|

53​

| |

10​

​

|

13​

|

41​

|

66​

|

7​

|

24​

|

67​

|

36​

|

93​

| |

11​

​

|

23​

|

80​

|

75​

|

50​

|

49​

|

69​

|

20​

|

60​

| |

12​

​

| | | | | | | | | |

13​

​

| | | | | | | | | |

14​

​

| | | | | | |

50​

|

11​

|{=MAX(IF(DATA=$F$14,ROW(DATA),""))}|

15​

​

| | | | | | | |

4​

|{=MAX(IF(DATA=$F$14,COLUMN(DATA),""))}|

16​

​

| | | | | | |

ĐÚNG​

|

$D$11​

|{=ADDRESS(MAX(IF(DATA=$F$14,ROW(DATA),"")),MAX(IF(DATA=$F$14,COLUMN(DATA),"")))}|

17​

​

| | | | | | | | | |

18​

​

| | | | | | |

SAI​

|

$K$11​

|=ADDRESS(SUMPRODUCT((DATA=$F$14)*ROW(DATA)),SUMPRODUCT((DATA=$F$14)*ROW(DATA)))|

Nó phải là:

=ADDRESS(SUMPRODUCT(($A$1:$B$4=$B$5)*ROW($A$1:$B$4)),SUMPRODUCT(($A$1:$B$4=$B$5)*COLUMN($A$1:$B$4)))​

 

[ndu96081631]

Vì sợ mất công Ctrl + Shift + Enter nên ta đưa vào hàm SUMPRODUCT
Mà dùng SUMPRODUCT thì sợ công thức hơi dài
Vậy thôi đưa công thức mãng vào name đi cho gọn

DiaChi = ADDRESS(Max(......)

Mai này nếu muốn dùng DiaChi này làm tham chiếu thì

=INDIRECT(DiaChi)

 

[BNTT]

Đưa vào Name cũng là một ý hay, nhưng phải đặt Name cho khối dữ liệu (DATA) lẫn cái ô chứa giá trị cần tìm (TIM) luôn thì mới được:

=ADDRESS(MAX(IF(DATA=TIM,ROW(DATA),"")),MAX(IF(DATA=TIM,COLUMN(DATA),"")))}
​

​