Cố định tham chiếu trong công thức khi chèn thêm cột

[aeolus]

Xin chào các ACE

Mình xin hỏi vấn đề này.

Mình có 1 bảng Excel cấu trúc như sau :
Cột A là các mã
Cột B là trung bình 10 ngày gần nhất.
Từ các cột C,D, E trở về sau ... là các giá trị được cập nhật theo ngày. Cột C luôn là giá trị ngày mới nhất. Lúc cuối ngày sẽ insert thêm cột C để cập nhật giá trị.

Ô B1 có giá trị : =AVERAGE($C1:$l1)

Vấn đề gặp phải là mỗi khi insert cột mới kế trái cột C, nó sẽ trở thành cột C và công thức trong ô B1 sẽ tự động bị thay đổi giá trị thành : =AVERAGE($D1:$M1) tức là lùi lại 1 cột và có value không đổi.

Xin hỏi có cách nào tính trung bình cộng của 10 cột gần nhất kể từ cột C trở đi, khi insert cột C mới vào thì công thức tại cột B vẫn giữ nguyên và giá trị tự động cập nhật theo dữ liệu mới.

Cảm ơn.

 

[bebo021999]

Thử dùng INDIRECT xem sao:
=AVERAGE(INDIRECT("C1:L1"))

 

[aeolus]

Thử dùng INDIRECT xem sao:
=AVERAGE(INDIRECT("C1:L1"))

Đã dùng thử nhưng kéo các ô hàng dưới thì bị trùng value giống y như ô đầu.

 

[bebo021999]

Vậy thì cho hàng đầu tiên:
=AVERAGE(INDIRECT("C"&row(1:1)&":L"&row(1:1)))
Copy cho hàng dưới.

 

[ndu96081631]

Đã dùng thử nhưng kéo các ô hàng dưới thì bị trùng value giống y như ô đầu.

Có thể lồng hàm OFFSET

=AVERAGE(OFFSET(B1,,1,,10))

 

[aeolus]

Vậy thì cho hàng đầu tiên:
=AVERAGE(INDIRECT("C"&row(1:1)&":L"&row(1:1)))
Copy cho hàng dưới.

Cảm ơn. Xin nhờ a giải thích giúp phần màu xanh : INDIRECT("C"&row(1:1)&":L"&row(1:1))

Cảm ơn nhiều.

 

[aeolus]

Có thể lồng hàm OFFSET

=AVERAGE(OFFSET(B1,,1,,10))

Cảm ơn a. Xin hỏi thêm

Ví dụ ô A1 có giá trị : =A3/A5
Khi ta chèn thêm 1 cột vào trái của A5 thì khi đó A1 tự động bị chuyển hàm thành =A3/A6 và có giá trị không đổi.
Vậy dùng hàm OFFSET thế nào để có thể cố định ô A5 và làm cho giá trị ô A1=A3/A5 tự động update khi ta chèn thêm cột vào bên trái ô A5.

 

[ndu96081631]

Cảm ơn a. Xin hỏi thêm

Ví dụ ô A1 có giá trị : =A3/A5
Khi ta chèn thêm 1 cột vào trái của A5 thì khi đó A1 tự động bị chuyển hàm thành =A3/A6 và có giá trị không đổi.
Vậy dùng hàm OFFSET thế nào để có thể cố định ô A5 và làm cho giá trị ô A1=A3/A5 tự động update khi ta chèn thêm cột vào bên trái ô A5.

Tôi nghi ngờ rằng bạn đang nhầm thì phải? Có phải ý bạn là CHÈN DÒNG hay không? (trong khi câu hỏi của bạn lại liên quan đến CHÈN CỘT)

 

[aeolus]

Tôi nghi ngờ rằng bạn đang nhầm thì phải? Có phải ý bạn là CHÈN DÒNG hay không? (trong khi câu hỏi của bạn lại liên quan đến CHÈN CỘT)

Vô cùng xin lỗi a. Đúng là nhầm thật.
Xin đưa lại câu hỏi.

Ô A1 có giá trị : =C1/E1
Khi ta chèn 1 cột vào bên trái của cột E thì khi đó A1 tự chuyển hàm thành =C1/F1 ( tức bị lùi ra 1 cấp ) và có giá trị không đổi.
Xin hỏi ta có cách nào để cố định hàm ở A1 khi chèn cột vào bên trái cột E. Tức là muốn giữ hàm ở A1 luôn là ô C1 chia cho cách đó 2 ô và tự update giá trị tương ứng với cột chèn vào bên trái cột E.

Cảm ơn.

 

[ndu96081631]

Vô cùng xin lỗi a. Đúng là nhầm thật.
Xin đưa lại câu hỏi.

Ô A1 có giá trị : =C1/E1
Khi ta chèn 1 cột vào bên trái của cột E thì khi đó A1 tự chuyển hàm thành =C1/F1 ( tức bị lùi ra 1 cấp ) và có giá trị không đổi.
Xin hỏi ta có cách nào để cố định hàm ở A1 khi chèn cột vào bên trái cột E. Tức là muốn giữ hàm ở A1 luôn là ô C1 chia cho cách đó 2 ô và tự update giá trị tương ứng với cột chèn vào bên trái cột E.

Cảm ơn.

Thì vầy:

=OFFSET(A1,,[B][COLOR=#ff0000]2[/COLOR][/B])/OFFSET(A1,,[B][COLOR=#0000cd]4[/COLOR][/B])

Số 2: Nghĩa là cách A1 2 cell về phía bên phải
Số 4: Nghĩa là cách A1 4 cell về phía bên phải

 

[nguyenacc]

Em tìm mãi mới đc topic này, của em là chèn hàng, xin vui lòng chỉ giáo giùm.

Em ví dụ A1 = H4 . Khi em insert thêm 4 hàng, thì em vẫn muốn giữ công thức A1=H4, hiện công thức chạy A1 = H8.

Cảm ơn ơn.

Bài đã được tự động gộp: 26/5/23

Thì vầy:

=OFFSET(A1,,[B][COLOR=#ff0000]2[/COLOR][/B])/OFFSET(A1,,[B][COLOR=#0000cd]4[/COLOR][/B])

Số 2: Nghĩa là cách A1 2 cell về phía bên phải
Số 4: Nghĩa là cách A1 4 cell về phía bên phải

Em tìm mãi mới đc topic này, của em là chèn hàng, xin vui lòng chỉ giáo giùm.

Em ví dụ A1 = H4 . Khi em insert thêm 4 hàng, thì em vẫn muốn giữ công thức A1=H4, hiện công thức chạy A1 = H8.

Cảm ơn ơn.

Bài đã được tự động gộp: 26/5/23

Vậy thì cho hàng đầu tiên:
=AVERAGE(INDIRECT("C"&row(1:1)&":L"&row(1:1)))
Copy cho hàng dưới.

Em tìm mãi mới đc topic này, của em là chèn hàng, xin vui lòng chỉ giáo giùm.

Em ví dụ A1 = H4 . Khi em insert thêm 4 hàng, thì em vẫn muốn giữ công thức A1=H4, hiện công thức chạy A1 = H8.

Em dung hàm indirect bên trên của anh thì giá trị vẫn bị đổi khi em insert thêm hàng.

Em cảm ơn.

 

[bebo021999]

Chà, cao thủ "đào mộ" đây rồi.
Nếu muốn luôn luôn link tới cột H, dòng 4, cho dù chèn, xóa cột hay hàng, có nhiều cách. Đơn cử 2 cách:
=INDIRECT("H4")
hoặc
=INDIRECT(ADDRESS(4,8))
Với ADDRESS(4,8) trả về chuỗi H4 ( 8 là thứ tự cột của H), lồng vào INDIRECT chuyển thành tham chiếu ô H4.

 

[nguyenacc]

Chà, cao thủ "đào mộ" đây rồi.
Nếu muốn luôn luôn link tới cột H, dòng 4, cho dù chèn, xóa cột hay hàng, có nhiều cách. Đơn cử 2 cách:
=INDIRECT("H4")
hoặc
=INDIRECT(ADDRESS(4,8))
Với ADDRESS(4,8) trả về chuỗi H4 ( 8 là thứ tự cột của H), lồng vào INDIRECT chuyển thành tham chiếu ô H4.

Đọc comment của bác vừa bổ não vừa bổ phổi, "cao thủ đào mộ" làm em cười ầm ầm trong phòng làm mọi người hỏi em có gì vui vậy.
Cảm ơn bác nhiều nhiều nhé