Lọc lấy dữ liệu theo số phiếu

[volga]

Em có bài này nhờ các anh chị giúp dùm em .
Em muốn lọc dử liệu trong File đính kèm theo số phiếu .
cách nào đơn giản mà nhanh gọn nhẹ ?Dùng name thôi nhe ( cột phụ em làm được ).
càng ít name càng hay
Nói rỏ các anh dùng name đó nhằm nó cho ra cái gì ?công dụng của nó ?
em đang tập tành ,mà chưa hiểu ra nhiều vấn đề lắm ,mong các anh chị giúp cho !
Em cám ơn nhiều !!!!!!!!

 

[hoangdanh282vn]

Em có bài này nhờ các anh chị giúp dùm em .
Em muốn lọc dử liệu trong File đính kèm theo số phiếu .
cách nào đơn giản mà nhanh gọn nhẹ ?Dùng name thôi nhe ( cột phụ em làm được ).
càng ít name càng hay
Nói rỏ các anh dùng name đó nhằm nó cho ra cái gì ?công dụng của nó ?
em đang tập tành ,mà chưa hiểu ra nhiều vấn đề lắm ,mong các anh chị giúp cho !
Em cám ơn nhiều !!!!!!!!

File của bạn chẳng có dữ liệu nào để làm cả.
Gửi bạn 4 file mẫu :
File 1 lọc tự động dữ liệu theo tháng, không dùng Name, dùng 1 cột phụ cho đơn giản.
File 2 dùng Name, lọc theo nhiều điều kiện, dùng cột phụ để chạy cho nhanh.
2 bài tiếp dùng công thức mảng, bạn tham khảo nha

 

[ndu96081631]

Bài này chỉ 2 name là đủ:

DL = =OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)

VT =IF(OFFSET(DL,,1,,1)=Sheet1!$H$5,ROW(INDIRECT("1:"&ROWS(DL))),"")

Sau khi xong phần name, bạn quét chọn F7:F19 và gõ vào thanh Formula công thức:
=VT
Ctrl + Shift + Enter
Theo dỏi công thức này thay đổi khi ta thay đổi số phiếu tại H5 ---> Từ đó suy ra cách trích lọc bằng INDEX
Quan trọng nhất vẫn là name VT ---> dùng định vị xem số phiếu đúng điều kiện nằm ở dòng thứ mấy
------------
Nếu bạn hỏi tôi dùng cách nào nhanh nhất, hiệu quả nhất thì tôi xin thưa: Đó không phải là công thức mà là AutoFilter hoặc Advanced Filter ---> Dùng VBA người ta cũng dựa và công cụ này ---> Cực nhanh, nhất là với dử liệu lớn ---> Và theo tôi phương pháp lọc bằng AutoFilter và Advanced Filter còn dể hiểu hơn rất nhiều lần so với công thức

 

[volga]

Em làm cái này cho bạn cùng công ty ,làm phiếu xuất kho đó .
name VT nhằm trả ra số dòng của dử liệu thỏa đk đúng không anh ?
Bài toán nào mình củng tao name làm sao cho nó ra số dòng là ok hả anh ?

Cám ơn hai anh nhiều!

 

[ndu96081631]

Em làm cái này cho bạn cùng công ty ,làm phiếu xuất kho đó .
name VT nhằm trả ra số dòng của dử liệu thỏa đk đúng không anh ?
Bài toán nào mình củng tao name làm sao cho nó ra số dòng là ok hả anh ?

Cám ơn hai anh nhiều!

Thì đương nhiên mình phải có công thức gì đó xác định vị trí dòng thì mới trích được chứ (cột thì không cần xác định, vì ngay khi ta gõ tiêu đề xem như ta đã xác định rồi)
Công thức của bạn nhìn chung không có vấn đề, chỉ xin lưu ý: Trong công thức bạn dùng hàm ROW... mà theo tôi thì nên là ROWS
Ví dụ:
Của bạn là:
=IF(COUNT(vt)<ROW(1:1),"",ROW(1:1))
theo tôi nên là:
=IF(COUNT(vt)<ROWS($1:1),"",ROWS($1:1))
Của bạn là:
=IF(COUNT(vt)<ROW(1:1),"",INDEX(dl,SMALL(vt,ROW(1:1)),2))
theo tôi nên là:
=IF(COUNT(vt)<ROWS($1:1),"",INDEX(dl,SMALL(vt,ROWS($1:1)),COLUMNS($A:B)))
Tại sao lại vậy:
- Với hàm ROW thì tôi đã nói nhiều lần trên diển đàn rồi
- Còn công thức dưới, nếu làm theo tôi thì bạn chỉ cần 1 công thức là fill ngang dọc được rồi

 

[kongcom]

Kongcom thích cách làm như thế này hơn . Bạn thử xem nhé . Chú ý là nó tự thêm mỗi khi bạn nhập dữ liệu mới .

 

[nguyenhongphong]

Thưa thầy Ndu , cho e được hỏi thêm

Thì đương nhiên mình phải có công thức gì đó xác định vị trí dòng thì mới trích được chứ (cột thì không cần xác định, vì ngay khi ta gõ tiêu đề xem như ta đã xác định rồi)
Công thức của bạn nhìn chung không có vấn đề, chỉ xin lưu ý: Trong công thức bạn dùng hàm ROW... mà theo tôi thì nên là ROWS
Ví dụ:
Của bạn là:
=IF(COUNT(vt)<ROW(1:1),"",ROW(1:1))
theo tôi nên là:
=IF(COUNT(vt)<ROWS($1:1),"",ROWS($1:1))
Của bạn là:
=IF(COUNT(vt)<ROW(1:1),"",INDEX(dl,SMALL(vt,ROW(1:1)),2))
theo tôi nên là:
=IF(COUNT(vt)<ROWS($1:1),"",INDEX(dl,SMALL(vt,ROWS($1:1)),COLUMNS($A:B)))
Tại sao lại vậy:
- Với hàm ROW thì tôi đã nói nhiều lần trên diển đàn rồi
- Còn công thức dưới, nếu làm theo tôi thì bạn chỉ cần 1 công thức là fill ngang dọc được rồi

Cách của thầy đúng là tuyệt thật.

Nhất là dòng này: =IF(COUNT(vt)<ROWS($1:1),"",INDEX(dl,SMALL(vt,ROWS($1:1)),COLUMNS($A:B)))

E có 1 vài thắc mắc nhờ thầy hướng dẫn giùm e:

-Với name: vt=IF(OFFSET(dl,,1,,1)=Sheet1!$G$5,ROW(INDIRECT("1:"&ROWS(dl))),""). Vì sao khi sửa số 1 thành khoảng trắng hoặc số 0 vào OFFSET(dl,,1,,0) thì kết quả ra rỗng ạ?

-Nếu e insert 1 column trong vùng dl (trước column B chẳng hạn) thì e thấy kết quả là rỗng. Vậy làm cách nào nếu có insert column mà kết quả không thay đổi vậy không thầy?!

Em cám ơn Thầy trước ạ!

 

[MinhCong]

Hàm OFFSET

Chức năng: Hàm OFFSET sẽ trả về một tham chiếu đến một vùng nào đó, vị trí bắt đầu của tham chiếu từ một ô hay một vùng nào đó cho trước với độ lớn của vùng được chỉ định.

Cú pháp: OFFSET(reference,rows,cols,height,width)

- Reference: là vùng hay ô được chọn làm điểm xuất phát (điểm mốc) của vùng tham chiếu sẽ trả về trong công thức.

- Rows: là số chỉ số dòng lệch lên hay lệch xuống của vùng tham chiếu trả về so với vùng được chọn làm mốc. Rows có giá trị âm "-" thì vùng trả về sẽ lệch lên trên so với vùng chọn làm mốc và ngược lại.

- Cols: là số chỉ số cột lệch qua trái hay qua phải của vùng tham chiếu trả về so với vùng được chọn làm mốc. Cols có giá trị âm "-" thì vùng trả về sẽ lệch sang trái so với vùng chọn làm mốc và ngược lại.

- Height: là số chỉ số dòng (độ cao) có trong vùng tham chiếu sẽ trả về trong công thức.

- Width: là số chỉ số cột (độ rộng) có trong vùng tham chiếu sẽ trả về trong công thức.

Lưu ý: Nếu trong công thức không nhập giá trị của height và width thì xem như nó bằng với độ lớn của Reference.

1/Vấn đề 1 (Bạn xem lại dòng Mình tô màu ở trên nhé)

2/Bạn muốn insert 1 thêm 1 cột nữa trong bảng dl thì Bạn xem ở đầu công thức của anh ndu ấy:
DL=OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)
Bạn cũng phải điều chỉnh vùng dữ liệu lại chứ. Lúc này sẽ là
DL=OFFSET(Sheet1!$A$7:$F$7,,,COUNT(Sheet1!$A$7:$A$1000),)

 

[yeuthamhangxom]

File của bạn chẳng có dữ liệu nào để làm cả.
Gửi bạn 4 file mẫu :
File 1 lọc tự động dữ liệu theo tháng, không dùng Name, dùng 1 cột phụ cho đơn giản.
File 2 dùng Name, lọc theo nhiều điều kiện, dùng cột phụ để chạy cho nhanh.
2 bài tiếp dùng công thức mảng, bạn tham khảo nha

Gửi anh hoangdanh, anh ndu và các bạn trên diễn đàn.
File của anh hoangdanh em đã thử áp dụng vào bài của em nhưng chưa được. Bài của em là vừa lọc theo mã code nhưng chỉ lấy những thiết bị duy nhất không trùng nhau như file đính kèm. Rất mong anh hoangdanh và anh ndu và các bạn giúp đỡ.
Cám ơn cả nhà nhiều lắm. chúc cả nhà vui vẻ.

 

[nguyenhongphong]

To: Anh Công

Hàm OFFSET

Chức năng: Hàm OFFSET sẽ trả về một tham chiếu đến một vùng nào đó, vị trí bắt đầu của tham chiếu từ một ô hay một vùng nào đó cho trước với độ lớn của vùng được chỉ định.

Cú pháp: OFFSET(reference,rows,cols,height,width)

- Reference: là vùng hay ô được chọn làm điểm xuất phát (điểm mốc) của vùng tham chiếu sẽ trả về trong công thức.

- Rows: là số chỉ số dòng lệch lên hay lệch xuống của vùng tham chiếu trả về so với vùng được chọn làm mốc. Rows có giá trị âm "-" thì vùng trả về sẽ lệch lên trên so với vùng chọn làm mốc và ngược lại.

- Cols: là số chỉ số cột lệch qua trái hay qua phải của vùng tham chiếu trả về so với vùng được chọn làm mốc. Cols có giá trị âm "-" thì vùng trả về sẽ lệch sang trái so với vùng chọn làm mốc và ngược lại.

- Height: là số chỉ số dòng (độ cao) có trong vùng tham chiếu sẽ trả về trong công thức.

- Width: là số chỉ số cột (độ rộng) có trong vùng tham chiếu sẽ trả về trong công thức.

Lưu ý: Nếu trong công thức không nhập giá trị của height và width thì xem như nó bằng với độ lớn của Reference.

1/Vấn đề 1 (Bạn xem lại dòng Mình tô màu ở trên nhé)

2/Bạn muốn insert 1 thêm 1 cột nữa trong bảng dl thì Bạn xem ở đầu công thức của anh ndu ấy:
DL=OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)
Bạn cũng phải điều chỉnh vùng dữ liệu lại chứ. Lúc này sẽ là
DL=OFFSET(Sheet1!$A$7:$F$7,,,COUNT(Sheet1!$A$7:$A$1000),)

Với name:

vt=IF(OFFSET(dl,,1,,1)=Sheet1!$H$5,ROW(INDIRECT("1:"&ROWS(dl))),"")

Em bôi đen đoạn OFFSET(dl,,1,,1) bấm f9 ===>hiện ra kết quả liền (vùng B7:B19).
Em sửa số 1 thành 0 cho ra kết quả là #REF.
Em sửa số 1 thành khoảng trắng cho ra kết quả là là vùng C7:E19
Em sửa số 1 thành 2 cho ra kết quả là là vùng C719

Đến đây e mới hiểu qui tắc mở rộng dòng và cột Height và Width của offset. Lúc trước e chỉ mới hiểu được cách dời dòng cột của Rows và Cols và e cứ tưởng qui tắc mở rộng Height và Width cũng như Rows và Cols. Khi đọc và bấm f9 như các Thầy hướng dẫn e mới ngộ ra.

-Nếu e insert 1 column trong vùng dl (trước column B chẳng hạn) thì e thấy kết quả là rỗng. Vậy làm cách nào nếu có insert column mà kết quả không thay đổi vậy không thầy?!

2/Bạn muốn insert 1 thêm 1 cột nữa trong bảng dl thì Bạn xem ở đầu công thức của anh ndu ấy:
DL=OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)
Bạn cũng phải điều chỉnh vùng dữ liệu lại chứ. Lúc này sẽ là
DL=OFFSET(Sheet1!$A$7:$F$7,,,COUNT(Sheet1!$A$7:$A$1000),)

A Công ơi , khi e insert tại column B thì DL=OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)
cũng tự động nhảy thành DL=OFFSET(Sheet1!$A$7:$F$7,,,COUNT(Sheet1!$A$7:$A$1000),)

Nhưng kết quả thì rỗng.

A xem lại giúp em nhé


NOTE: ANH XEM BÀI LỌC PHIẾU CỦA BẠN VOLGA NHÉ

 

[MinhCong]

A Công ơi , khi e insert tại column B thì DL=OFFSET(Sheet1!$A$7:$E$7,,,COUNT(Sheet1!$A$7:$A$1000),)
cũng tự động nhảy thành DL=OFFSET(Sheet1!$A$7:$F$7,,,COUNT(Sheet1!$A$7:$A$1000),)

Nhưng kết quả thì rỗng.

A xem lại giúp em nhé


NOTE: ANH XEM BÀI LỌC PHIẾU CỦA BẠN VOLGA NHÉ

Bạn để ý tiếp cái số 1 màu xanh trong công thức OFFSET(dl,,1,,1) nhé. Khi Bạn insert 1 cột phía trước cột B thì khi đó trong công thức VT=if(....) gì đó ở hàm OFFSET(dl,,1,,1) nó sẽ hiểu dịch sang bên phải 1 cột (lúc này cột bạn vừa insert vào ko có dữ liệu để so sánh trong hàm if của VT thì lấy đâu có số liệu mà bảng tính điền vào => Trống). Mục đích của Bài là dự vào cột Số phiếu và so sánh với điều kiện tại cell $I$5.
=> Để hiện số liệu Bạn phải sửa công thức
VT=IF(OFFSET(dl,,1,,1)=Sheet1!$I$5,ROW(INDIRECT("1:"&ROWS(dl))),"")
Thành
VT=IF(OFFSET(dl,,2,,1)=Sheet1!$I$5,ROW(INDIRECT("1:"&ROWS(dl))),"")
Và công thức bên ngoài cũng phải sửa lại
I8=IF(COUNT(vt)<ROW(1:1),"",INDEX(dl,SMALL(vt,ROW(1:1)),3))
Bạn chú ý cái số 3 ở cuối cùng nhé (Vì số liệu của số phiếu năm ở cột thứ 3 trong bảng do Bạn insert thêm 1 cột rồi). Tương tự cho những giá trị khác.
Bạn có thể thay số 3 = column(C:C) chẳng hạn (copy sang các giá trị khác khỏi phải thay lại các số phía sau).

 

[yeuthamhangxom]

Gửi anh hoangdanh, anh ndu và các bạn trên diễn đàn.
File của anh hoangdanh em đã thử áp dụng vào bài của em nhưng chưa được. Bài của em là vừa lọc theo mã code nhưng chỉ lấy những thiết bị duy nhất không trùng nhau như file đính kèm. Rất mong anh hoangdanh và anh ndu và các bạn giúp đỡ.
Cám ơn cả nhà nhiều lắm. chúc cả nhà vui vẻ.

Cả nhà ơi cứu mình bài này với. Chúc cả nhà vui vẻ.

 

[vipreturn]

File của bạn chẳng có dữ liệu nào để làm cả.
Gửi bạn 4 file mẫu :
File 1 lọc tự động dữ liệu theo tháng, không dùng Name, dùng 1 cột phụ cho đơn giản.
File 2 dùng Name, lọc theo nhiều điều kiện, dùng cột phụ để chạy cho nhanh.
2 bài tiếp dùng công thức mảng, bạn tham khảo nha[/QUO

có thể cho tôi biết làm thế nào mới tạo ra được những bảng lọc như của File 2 ko. tôi mới học excel và chưa biết được nhiều lắm,hi vọng được giúp đỡ. tốt nhất gửi qua tài khoản gmail của tôi:ducchung2017@gmail.com.tôi sẽ dễ theo dõi hơn.thank