tham chiếu ngược cho 1 bảng danh sách

[mymymymy121212]

cho mình hỏi vấn đề này mà mình loay hoay mãi mà làm không ra cả buổi sáng nay:
vấn đề cần giải quyết:
Mình có 1 cột:
Cột A làm sao để chuyển sang cột B : ngược cột A
a n
b ...
c c
... b
n a
Mong mọi người giup dỡ với !! cảm ơn mọi người!

 

[hoamattroicoi]

Đầu tiên bạn lập 1 công thức để lấy dữ liệu cuối cùng của cột.(Có nhiều trên GPE bạn có thể search để xem)
Sau đó bạn đẩy ngược dữ liệu lên như trong file tôi đã làm.
Thanks!

 

[linhngoc]

Hihi, bon chen với hoa còi thêm 1 cách nữa! (mượn file của hoamattroi nhé)

 

[ptm0412]

công thức hoacoi:
=INDEX($D$2:$D$7;MAX(MATCH(9,99999999999999E+307;$D$2:$D$7);MATCH(REPT("z";255);$D$2:$D$7)))
gì mà dữ vậy trời?

Match(...) là vị trí cuối của dữ liệu.
1. Nếu bình thường (dòng cuối không trống) chẳng phải vị trí cuối vùng dữ liệu là tổng số dòng của vùng dữ liệu hay sao?
2. Nếu dòng cuối trống, thì thu nhỏ vùng index lại 1 dòng, quay lại điều 1.
3. Sao phải 2 công thức, cho ô đầu riêng và các ô dưới riêng? -Row(A1) cộng lại 1 cho dòng đầu, rồi fill xuống là OK rồi.

Công thức linhngoc:
Xài hàm mảng chi cho khổ vậy?

Thử hàm offset xem.

 

[linhngoc]

Xài hàm mảng chi cho khổ vậy?

Thử hàm offset xem.

Vâng, mảng khổ thật. Theo gợi ý của thầy. Cảm ơn thầy nhiều
VD: dữ liệu từ A2 đến A7

B2=OFFSET($A$1,ROWS($A$2:$A$7)-ROW(A1)+1,)
Hoặc:

C2=INDEX($A$2:$A$7,ROWS($A$2:$A$7)-ROW(A1)+1)

 

[Ba Tê]

Đã biết dòng cuối là 7 rồi thì dùng cái này thử xem, hổng có mảng nào cả.
B2 = INDIRECT("A" & 8-ROWS($1:1))
Số 8 màu đỏ là số dòng cuối +1

 

[hoamattroicoi]

Đã biết dòng cuối là 7 rồi thì dùng cái này thử xem, hổng có mảng nào cả.
B2 = INDIRECT("A" & 8-ROWS($1:1))
Số 8 màu đỏ là số dòng cuối +1

Công thức của bác Ba Tê hay quá, nếu chưa biết dòng nào là dòng cuối và cột A là dữ liệu text thì có thể chơi cái này, he he!

B2 =INDIRECT("A"&MATCH(REPT("z",255),A:A)+1-ROW(1:1))

 

[ptm0412]

Lại là match! Sao không làm việc đơn giản là xem dữ liệu chấm dứt ở dòng mấy rồi điền vào công thức?
Có lý nào lại không biết dòng mấy là dòng cuối?

Bé còi bị ám ảnh và làm việc 1 cách máy móc quá!

 

[Lê Duy Thương]

Lại là match! Sao không làm việc đơn giản là xem dữ liệu chấm dứt ở dòng mấy rồi điền vào công thức?
Có lý nào lại không biết dòng mấy là dòng cuối?

Bé còi bị ám ảnh và làm việc 1 cách máy móc quá!

tại các muội bị các hàm mảng ám ảnh rồi sư phụ
theo đệ tử thì vầy
=OFFSET(A$1,COUNTA(A:A)-ROWS(B$1:B1),,)

 

[ptm0412]

CountA sao được mà Count, nếu trong cột có ô trống (không phải dòng cuối) thì Offset với CountA đi tận đằng nào.

Lại còn Count nguyên cột nữa chứ? Nếu A1 là tên công ty, A2 là địa chỉ Công ty, ... A4 là tiêu đề Bảng, A5 là ..., A6 là ..., COuntA cả cột có mà đi tong.

 

[hoamattroicoi]

Lại là match! Sao không làm việc đơn giản là xem dữ liệu chấm dứt ở dòng mấy rồi điền vào công thức?
Có lý nào lại không biết dòng mấy là dòng cuối?

Bé còi bị ám ảnh và làm việc 1 cách máy móc quá!

Nhưng đấy là ví dụ dữ liệu đảo của 1 cột thôi thầy ạ, trong trường hợp mà muốn đổi dữ liệu của nhiều cột chả lẽ mỗi một cột mình lại đi dò tay xem dữ liệu nó kết thúc ở dòng mấy để đặt công thức sao?

 

[ptm0412]

Nhưng đấy là ví dụ dữ liệu đảo của 1 cột thôi thầy ạ, trong trường hợp mà muốn đổi dữ liệu của nhiều cột chả lẽ mỗi một cột mình lại đi dò tay xem dữ liệu nó kết thúc ở dòng mấy để đặt công thức sao?

Dữ liệu nhiều cột, thì vẫn lấy dòng cuối có dữ liệu. Với lại không phải dò bằng tay, mà là xem bằng mắt.
Huống chi, nếu dùng Match, cũng phải tìm (bằng mắt) xem cần phải Match cột nào có dữ liệu cuối ở dòng lớn nhất đó sao? Hay là cứ Match cột A, nếu cột D nhiều hơn vài dòng thì bỏ vài dòng đó?

Nghĩa là không phải mỗi cột mỗi dò tìm dòng cuối, bắt buộc lấy dòng cuối chung cho các cột, dù dùng Index, Offset, hay Indirect cũng vậy.

Tỉnh lại đi trò ơi.

 

[hoamattroicoi]

Dữ liệu nhiều cột, thì vẫn lấy dòng cuối có dữ liệu. Với lại không phải dò bằng tay, mà là xem bằng mắt.
Huống chi, nếu dùng Match, cũng phải tìm (bằng mắt) xem cần phải Match cột nào có dữ liệu cuối ở dòng lớn nhất đó sao? Hay là cứ Match cột A, nếu cột D nhiều hơn vài dòng thì bỏ vài dòng đó?

Nghĩa là không phải mỗi cột mỗi dò tìm dòng cuối, bắt buộc lấy dòng cuối chung cho các cột, dù dùng Index, Offset, hay Indirect cũng vậy.

Tỉnh lại đi trò ơi.

Nếu trong mỗi cột đó người ta lại thêm dữ liệu thì sao? Lại ngồi dò lại dòng cuối cùng kết thúc dữ liệu để đặt công thức?

 

[ptm0412]

Với danh sách này:

|

A​

|

B​

|

C​

|

D​

|

E​

|

3​

|

Tên​

|

Năm sinh​

|

Địa chỉ​

|

Có gia đình​

|

Tên vợ​

|

4​

|Nguyễn văn 01|

1.978​

|Số 05|X|Nguyễn thị 01|

5​

|Nguyễn văn 02|

1.978​

|Số 06|X|Nguyễn thị 02|

6​

|Nguyễn văn 03|

1.976​

|Số 07|X|Nguyễn thị 03|

7​

|Nguyễn văn 04|

1.980​

|Số 08|X|Nguyễn thị 04|

8​

|Nguyễn văn 05|

1.980​

|Số 09|X|Nguyễn thị 05|

9​

|Nguyễn văn 06|

1.979​

|Số 10|X|Nguyễn thị 06|

10​

|Nguyễn văn 07|

1.978​

|Số 11|X|Nguyễn thị 07|

11​

|Nguyễn văn 08|

1.980​

| |X|Nguyễn thị 08|

12​

|Nguyễn văn 09|

1.978​

| | | |

13​

|Nguyễn văn 10| | | | |

Nếu lấy match từng cột rồi đảo từng cột, thì 2 tên Nguyễn văn 09 và Nguyễn Văn 10 từ chưa vợ bỗng biến thành có vợ, lại còn lấy vợ người ta làm vợ mình.
Địa chỉ và năm sinh cũng chạy lung tung!
Do đó phải lấy dòng cuối cùng là dòng 13 cho tất cả các cột.

Trò nghĩ thấu chưa?

Nếu nói về dữ liệu sẽ tăng thêm dòng thì lại là chuyện khác, không phải chuyện match từng cột hoặc tất cả các cột. Khi đó, người ta chọn 1 cột có dữ liệu đầy đủ nhất và dùng CountA, chẳng hạn cột STT, cột tên, hoặc cột mã.
Mà cũng không phải CountA(A:A) mà chỉ là CountA(A4:A100) hoặc A4:A1000 là tối đa (tuỳ theo mức độ của dữ liệu).

 

[hoamattroicoi]

Em nói đến dữ liệu nhiều cột ở đây có thế là các cột khác nhau nằm ở nhiều sheet ta chỉ dùng một công thức và thay đổi địa chỉ tham chiếu của nó + khi thêm dữ liệu vào sẽ không phải tìm lại dòng cuối có dữ liệu --> Sẽ nhanh hơn là với mỗi cột dữ liệu như thế ta lại phải làm động tác đi tìm số dòng cuối có dữ liệu rồi mới đặt công thức.

Còn dữ liệu liên quan đến nhau như thầy nói thì lại là chuyện khác.

Vấn đề đặt ra ở đây là chúng ta nên để excel tự động tìm phần tử cuối hay hơn là ta phải đi tìm nó bằng mắt.