Lại hỏi về hàm offset và name động (1 người xem)

[cocongmaisat]

Chào diễn đàn!
Em đang tìm hiểu về name động và hàm offset.
Cho em hỏi nếu ở sheet 1 (ở vd đính kèm), khi thêm dữ liệu vào thì công thức ở sheet 2 sẽ sửa như thế nào ạ!
Nếu em post bài không đúng box thì nhờ Admin chuyển giùm, vì em cũng mới biết sử dụng net thôi ạ!
Xin cảm ơn diễn đàn ạ!

 

[duongdv]

Thì bạn chỉ cần mở rộng vùng tham chiếu là được mà
ví dụ:
tại ô C9:
=VLOOKUP(A2;Sheet1!A11000;2;0)
tại ô D9
=VLOOKUP(A2;Sheet1!A11000;30)
tại ô E9
=VLOOKUP(A2;Sheet1!A11000;40)
Nhưng câu hỏi của bạn có liên quan gì đến hàm offset và name động đâu nhỉ?

 

[ndu96081631]

Chào diễn đàn!
Em đang tìm hiểu về name động và hàm offset.
Cho em hỏi nếu ở sheet 1 (ở vd đính kèm), khi thêm dữ liệu vào thì công thức ở sheet 2 sẽ sửa như thế nào ạ!
Nếu em post bài không đúng box thì nhờ Admin chuyển giùm, vì em cũng mới biết sử dụng net thôi ạ!
Xin cảm ơn diễn đàn ạ!

Công thức đơn giản này thì chẳng cần đến name động làm gì
Trong công thức của bạn, hãy thay:
Sheet1!A116
thành:
Sheet1!$A:$D
là được rồi... muốn thêm dữ liệu bao nhiêu cũng được cả

 

[cocongmaisat]

Công thức đơn giản này thì chẳng cần đến name động làm gì
Trong công thức của bạn, hãy thay:
Sheet1!A116
thành:
Sheet1!$A:$D
là được rồi... muốn thêm dữ liệu bao nhiêu cũng được cả

Xin cảm ơn! Vì đây chỉ là bài tập ví dụ để tìm hiểu name động (và cách dùng hàm offset để làm name động). Nếu như vd trên khoảng 200 cột thì cách chọn luôn cả cột như vậy : -> dữ liệu nhiều-> xử lý chậm.
Mong các bậc cao thủ chỉ giáo!

 

[khoavu87]

Chào diễn đàn!
Em đang tìm hiểu về name động và hàm offset.
Cho em hỏi nếu ở sheet 1 (ở vd đính kèm), khi thêm dữ liệu vào thì công thức ở sheet 2 sẽ sửa như thế nào ạ!
Nếu em post bài không đúng box thì nhờ Admin chuyển giùm, vì em cũng mới biết sử dụng net thôi ạ!
Xin cảm ơn diễn đàn ạ!

xem file thử xem đúng ý bạn chưa? hình như bạn đã hỏi kiểu này một lần nhưng phạm quy nên bị xóa thì phải?

 

[dat_butmuc]

xem file thử xem đúng ý bạn chưa? hình như bạn đã hỏi kiểu này một lần nhưng phạm quy nên bị xóa thì phải?

"Chọt" Khoavu chơi,
Sao không làm chỉ một Name: GPEX=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),4)
mà phải làm 2 name,
Vui nhé...!!!

Xem ra bài này mà nhiều dữ liệu dùng Pivot có vẻ hợp lý hơn?

 

[ndu96081631]

"Chọt" Khoavu chơi,
Sao không làm chỉ một Name: GPEX=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),4)
mà phải làm 2 name,
Vui nhé...!!!

Xem ra bài này mà nhiều dữ liệu dùng Pivot có vẻ hợp lý hơn?

Nếu trong cột A ấy vừa có Text, vừa có Number lại vừa có cell rổng thì hổng biết thằng em name "động đậy" ấy nó nặng nề đến cở nào!
----------
Quà thật PivotTable là giải pháp ngon ăn nhất (vô chiêu thức)

 

[cocongmaisat]

"Chọt" Khoavu chơi,
Sao không làm chỉ một Name: GPEX=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),4)
mà phải làm 2 name,
Vui nhé...!!!

Xem ra bài này mà nhiều dữ liệu dùng Pivot có vẻ hợp lý hơn?

Dùng Pivot theo ý của bạn, mình thử thấy cũng hay. Nhưng lấn cấn ở chổ nảy: giả sử ở cột A, SBD lên đến 10 nghìn chẳn hạn, thì khi cần kiếm 1 sbd nào đó thì sao? mình thích gõ vô kia!
Tiện đây mình cảm ơn tất cả các bạn, nhất là Khoavu vì đã trả lời đúng ý mình cần hỏi! Mục đích của mình là cần tìm hiểu cách mở rộng vùng dữ liệu một cách tự động (theo mình hiểu là name động), chứ không phải là giải quyết bài toán kia. Trước giờ mình vẫn làm thủ công thế này: Vùng dữ liệu mình chọn dư thêm 1 hàng ở cuối cùng ( trong vd là từ A1 đến D19, chứ không phải từ A1 đến D18), khi cần chèn thêm danh sách thì insert 1 hàng ở cuối. Lúc đó vùng dữ liệu sẽ tự động làm mơí lại.

 

[ndu96081631]

Thưa bạn! Mong bạn đọc kỹ lại câu hỏi của mình. Vì mình muốn tìm hiểu về name động và hàm offset, nên chỉ lấy ví dụ trên để hỏi thôi. Chứ không phải mình XÀI THEO PHONG TRÀO.

Đựoc lắm! Nếu muốn học hỏi thì phải học cho.. tới bến luôn!
Tôi giả định rằng dữ liệu của bạn có dòng rỗng, khi ấy bạn tạo name động bằng cách nào đây?
Xin giới thiệu 1 name động kiểu khác:

=OFFSET(Sheet1!$A$1,,,MATCH(2,1/(1-ISBLANK(Sheet1!$A$1:$A$65535))),4)

Bạn cứ vô tư thêm dữ liệu. Mặc khác nó giải quyết đựoc luôn vụ dữ liệu có dòng rỗng
Xem file

 

[huuthang_bd]

Đựoc lắm! Nếu muốn học hỏi thì phải học cho.. tới bến luôn!
Tôi giả định rằng dữ liệu của bạn có dòng rỗng, khi ấy bạn tạo name động bằng cách nào đây?
Xin giới thiệu 1 name động kiểu khác:

=OFFSET(Sheet1!$A$1,,,MATCH(2,1/(1-ISBLANK(Sheet1!$A$1:$A$65535))),4)

Bạn cứ vô tư thêm dữ liệu. Mặc khác nó giải quyết đựoc luôn vụ dữ liệu có dòng rỗng
Xem file

Tùy vào dữ liệu mà dùng hàm cho thích hợp. Chứ dùng công thức mảng cho cả cột như thế này thì sẽ ảnh hưởng rất lớn đến tốc độ. Trong file của bạn, chỉ cần 100 công thức có tham chiếu đến name động là thấy file ì ạch rồi.
Nếu dữ liệu như trong file của bạn thì tôi dùng name động như thế này:

=OFFSET(Sheet1!$A$1,,,MATCH(CHAR(255),Sheet1!$B:$B),4)

 

[ndu96081631]

Tùy vào dữ liệu mà dùng hàm cho thích hợp. Chứ dùng công thức mảng cho cả cột như thế này thì sẽ ảnh hưởng rất lớn đến tốc độ. Trong file của bạn, chỉ cần 100 công thức có tham chiếu đến name động là thấy file ì ạch rồi.
Nếu dữ liệu như trong file của bạn thì tôi dùng name động như thế này:

=OFFSET(Sheet1!$A$1,,,MATCH(CHAR(255),Sheet1!$B:$B),4)

Chính thế mà tôi mới nói rằng cái name động này làm xong chắc gì đã nhanh hơn VLOOKUP tôi đã làm ở bài #3
Còn như tác giả thích học thì ta chiều vậy!
Lưu ý rằng name của tôi dùng đựoc cho cả TEXT và NUMBER nha! ---> Trường hợp SBD kiểu này: 3330-015 thì name của bạn nó.. tèo

 

[ndu96081631]

Để giải quyết thêm vụ tốc độ nữa thì.. hơi rắc rối 1 chút ---> Thêm 2 name phụ:

Tmp1 =MATCH(REPT("z",255),Sheet1!$A:$A)

Tmp2 =MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Còn name DATA thì sửa lại thành:

=OFFSET(Sheet1!$A$1,,,MAX(IF(ISNA(Tmp1),0,Tmp1),IF(ISNA(Tmp2),0,Tmp2)),4)

-------------------
Lưu ý rằng: =OFFSET(Sheet1!$A$1,,,MATCH(CHAR(255),Sheet1!$B:$B ),4) không đúng khi cột B có chuổi bắt đầu bằng chữ Z (Tên người ở VN dạo này có thể có chữ Z rồi nha)

 

[huuthang_bd]

Chính thế mà tôi mới nói rằng cái name động này làm xong chắc gì đã nhanh hơn VLOOKUP tôi đã làm ở bài #3
Còn như tác giả thích học thì ta chiều vậy!
Lưu ý rằng name của tôi dùng đựoc cho cả TEXT và NUMBER nha! ---> Trường hợp SBD kiểu này: 3330-015 thì name của bạn nó.. tèo

Name của tôi chẳng liên quan gì đến kiểu dữ liệu của SBD cả. Như tôi đã nói: Tùy vào dữ liệu

 

[ndu96081631]

Name của tôi chẳng liên quan gì đến kiểu dữ liệu của SBD cả. Như tôi đã nói: Tùy vào dữ liệu

Ý tôi muốn nói đến cột B đấy! Nếu tên người bắt đầu bằng chữ Z thì bạn làm sao? Điều này hoàn toàn có thể cơ mà
Mặc khác, tác giả nói rằng muốn học về name động nên phải chăng ta nên đưa ra 1 công thức tương đối tổng quát! (Tổng quát là dữ liệu loại nào cũng dùng được)
Còn như để giải quyết bài nầy thì tôi cần quái gì name động cơ chứ!

 

[huuthang_bd]

Ý tôi muốn nói đến cột B đấy! Nếu tên người bắt đầu bằng chữ Z thì bạn làm sao? Điều này hoàn toàn có thể cơ mà

Nếu vậy thì tôi làm thế này:

=OFFSET(Sheet1!$A$1,,,MATCH(999,Sheet1!$D:$D),4)

Làm như thế nào là tùy vào dữ liệu.

 

[ndu96081631]

Nếu vậy thì tôi làm thế này:

=OFFSET(Sheet1!$A$1,,,MATCH(999,Sheet1!$D:$D),4)

Làm như thế nào là tùy vào dữ liệu.

Nói chung là công thức của bạn chưa phải là tổng quát
Ý tôi muốn nói đến sự TỔNG QUÁT để mà HỌC HỎI bạn à (chứ để giải quyết vấn đề thì thiếu chi cách mà PivotTable hoặc VLOOKUP bình thường là 1 trong số đó)
Người ta đã bào rằng dữ liệu giả lập chỉ dùng để minh họa công thức, bạn lại cứ bám vào lý do:
- Tuổi không vượt quá 999
- Tên là chuổi
vân...vân...
thì làm sao mà HỌC được cơ chứ!
Tóm lại: Name động phải làm sao quét được từ cell đầu tiên đến cell cuối cùng có dữ liệu, bất kể trong cell ấy là dữ liệu thuộc loại gì!

 

[hoangmau83]

Em thì hay xài cách này
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!A:A),4)
Các bác cho em thêm ý kiến với

 

[ndu96081631]

Đã qua 3, 4 năm rồi nhỉ! Giờ xem lại "giọng điệu" trong mấy bài viết của mình cũng thấy rằng: Mình là tên rất đáng ghét
Ẹc... Ẹc... spam cái rồi tính

 

[tueyennhi]

Để giải quyết thêm vụ tốc độ nữa thì.. hơi rắc rối 1 chút ---> Thêm 2 name phụ:

Tmp1 =MATCH(REPT("z",255),Sheet1!$A:$A)

Tmp2 =MATCH(9.99999999999999E+307,Sheet1!$A:$A)

Còn name DATA thì sửa lại thành:

=OFFSET(Sheet1!$A$1,,,MAX(IF(ISNA(Tmp1),0,Tmp1),IF(ISNA(Tmp2),0,Tmp2)),4)

-------------------
Lưu ý rằng: =OFFSET(Sheet1!$A$1,,,MATCH(CHAR(255),Sheet1!$B:$B ),4) không đúng khi cột B có chuổi bắt đầu bằng chữ Z (Tên người ở VN dạo này có thể có chữ Z rồi nha)

Em không hiểu cái này. Mấy cái hàm này là cho vào đâu vậy? Ví dụ =OFFSET(Sheet1!$A$1,,,MAX(IF(ISNA(Tmp1),0,Tmp1),IF(ISNA(Tmp2),0,Tmp2)),4)

 

[Hong.Van]

Em không hiểu cái này. Mấy cái hàm này là cho vào đâu vậy? Ví dụ =OFFSET(Sheet1!$A$1,,,MAX(IF(ISNA(Tmp1),0,Tmp1),IF(ISNA(Tmp2),0,Tmp2)),4)

Đặt trong Define name, bạn bấm tổ hợp fím: ctrl+F3