Trang 1/2 1 2 cuốicuối
Hiển thị kết quả tìm kiếm từ 1 đến 10 trên tổng số: 15
  1. #1
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Tìm kiếm một giá trị thỏa mãn nhiều điều kiện

    Trong Excel có hàm Vlookup, Hlookup,..để tìm và cho ra một giá trị náo đó, những hàm này đơn thuần là tìm theo một điều kiện hay một giá trị.
    Trong một CSDL các thông tim thường có liên hệ với nhau, đôi khi chúng ta cần tìm ra một giá trị thoả mãn n hiều điều kiện.
    VD: Mình muốn tìm một nhân viên mà:
    - Có gia đình
    - Số con <3
    - Số ngày công >=26

    Các thông tin về gia đình, số con nằm trong DMNV, ngày công có trong sổ chấm công.

    Với VD trên thì hình như các công cụ của Excel và các hàm không làm được?
    (Autofilter và Advance Filter chỉ thực hiện trên một sổ -sheet))

    Mình muốn gõ công thức vào một ô để nhận được tên nhân viên thoả mãn các điều kiện trên
    Trước tiên xin chia xẻ với các bạn bài báo được đăng trên PCWord
    Ví dụ chúng ta có một bảng dữ liệu, yêu cầu đặt ra là tìm điểm toán của một học sinh trong danh sách theo 2 điều kiện giới tính và tên. Vậy ta phải làm thế nào đây?


    Tôi sẽ giới thiệu với các bạn một hàm tự tạo để làm việc này.

    Trước tiên, bạn vào menu Tools\Macro\Visual Basic Editor (Alt + F11). Tại cửa sổ Microsoft Visual Basic, vào menu Insert\Module và nhập đoạn mã sau vào module vừa tạo.


    Function FindTwoCondition(Table As Range, Val1 As Variant, _

    Val1Occrnce As Integer, Val2 As Variant, Val2Col As Integer, ResultCol As Integer)

    'Tabel la bang du lieu

    'Val1 Dieu kien thu nhat

    'Val1Occrnce gia tri thu n cua dieu kien trong cot

    'Val2 dieu kien thu hai

    'Val2Col cot thu n cua dieu kien thu 2

    'ResultCol cot thu n can tim



    Dim i As Integer, iCount As Integer

    Dim rCol As Range



    For i = 1 To Table.Rows.Count

    If Table.Cells(i, 1) = Val1 And _

    Table.Cells(i, Val2Col) = Val2 Then

    iCount = iCount + 1

    End If



    If iCount = Val1Occrnce Then

    FindTwoCondition = Table.Cells(i, ResultCol)

    Exit For

    End If

    Next i

    End Function



    Sau khi đã nhập xong đoạn mã trên, bạn quay trở lại màn hình làm việc Excel bằng cách ấn Alt+Q. Bây giờ ta có thể sử dụng hàm vừa tạo như những hàm mà Excel đã hỗ trợ. Ví dụ tôi cần tìm Điểm Toán của người có tên là "Sơn" và có giới tính là "Nữ".

    Trước tiên tôi lập bảng điều kiện, tại ô I6 tôi nhập công thức sau:


    =FindTwoCondition($B$4:$F$13,I4,1,I5,3,4)


    Trong đó:


    - $B$4:$F$13: Vùng dữ liệu

    - I4: Tên cần tìm

    - 1: Tìm tên Sơn đầu tiên

    - I5: Giới tính cần tìm

    - 3: Số thứ tự của cột Giới tính trong vùng dữ liệu

    - 4: Số thứ tự của cột Điểm Toán trong vùng dữ liệu


    Kết quả sẽ trả về là 7. Chú ý ở đây hàm không phân biệt chữ thường chữ hoa.

    Lâm Quang Bình

    Binhlq77@yahoo.co.uk

    Trích từ : http://www.pcworld.com.vn
    thay đổi nội dung bởi: handung107, 08-06-06 lúc 01:02 PM

  2. Có 6 thành viên cảm ơn handung107 về bài viết này:


  3. #2
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Ý kiến của Tuânktcdcn

    Mình gửi file lên đây nhờ các bạn chỉ giúp.

    P/S : Tạm thời, các bạn có thể liên hệ với BQT để có File

    Cách 1- Lập công thức?
    Cách 2- VBA ?

    Mình nghĩ với cách 1 phải cố định số điều kiện thì mới làm được, còn cách 2 thì số điều kiện sẽ "động" hơn (có thể n điều kiện)?

    Bằng VBA thì mình có thể sẽ làm được, nhưng lập công thức (cách 1) thì hiện nay chưa.

  4. Thành viên sau cảm ơn handung107 về bài viết này:


  5. #3
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Đào Việt Cường Post :

    Theo em hiểu thì việc tổ chức các bảng cơ sở như trong file đính kèm của bác là bắt buộc(?). Nếu không, em có thể tạo một bảng phụ hoặc là gộp luôn cả 2 bảng vào làm 1. Xử lý sẽ rất đơn giản bằng Filter.
    Còn nếu điều kiện lọc bắt buộc nằm trên nhiều bảng như vậy, em sẽ phải lọc điều kiện qua 2 bước:
    Bước 1: Lọc điều kiện trên bảng chấm công
    Bước 2: Lọc điều kiện trong bảng hồ sơ, kết hợp với mã tìm được ở bước 1.
    Vấn đề sẽ đơn giản nếu tổ chức lại cơ sở dữ liệu đáp ứng được yêu cầu, mặc dù chưa được tối ưu lắm!
    (Trong file đính kèm em gộp 2 bảng cơ sở dữ liệu làm 1, hơi thừa nhưng đáp ứng được yêu cầu thống kê)

  6. #4
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Adam_tran Post :

    Vấn đề của anh Tuân là tìm giải pháp từ công thức hay VBA để giải quyết chuyện của Querry. Nếu dùng công cụ truy vấn tin để tìm những giá trị thỏa mãn từ n Databse thì...
    Ý tưởng của em là chôm 1 công cụ Querry nào đó, dùng VBA để điểu khiển.
    Đồ chơi trong Excel kiểu này hình như là Ms Querry thì phải, nhưng điều khiển nó = VBA thì chịu

  7. #5
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Tuanktcdcn Post :

    Cảm ơn bạn Cường

    - Ghép bảng và dùng Filter, Advance Filter cũng được nhưng không ổn khi quản trị CSDL, không phải cứ có điều kiện thì lại thêm cột.

    Có thể dùng công thức trong ô (=?) để nhận giá trị được không?

    To Adam_tran :

    - Dùng Query bàng dòng lệnh (VBA) có lẽ là giải pháp tốt. Nếu không rõ về Excel Query thì vẫn có thể có cách khác.

    Cảm ơn và mong các bạn tiếp tục quan tâm!

  8. #6
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Ichinova Post :

    Mình quy ước: Những các giá trị: 26, 3, 1 gọi là giá trị so sánh.
    Còn những thứ như: gia đình, số con, số công của từng người là giá trị bị so sánh.
    Cái này giống như số trừ và số bị trừ đấy hì hì....

    Trước tiên, mình sẽ khởi tạo vài biến (nếu biết trước là sẽ có bao nhiêu giá trị so sánh) để chứa các giá trị so sánh:
    đối với file của Mr Tuan, mình tạo 3 biến a, b, c. Mà cũng chỉ cần 2 biến là được bởi vì giá trị gia đình là 0 hoặc 1.

    Đó là trường hợp chỉ có 3 thứ để so sánh, nếu phát sinh thêm nhiều thứ khác, mà không thể biết được sẽ phải tạo bao nhiêu biến thì không thể dùng biến như trên được, mà phải dùng mảng A[i...n]

    Mỗi A[i] chứa một giá trị so sánh.

    Mảng này mình khai báo nhỏ thôi khoảng 10 chẳng hạn.
    Làm gì có ông nội nào so sánh nhiều điều kiện quá.

    Để gán giá trị so sánh cho biến, mình dùng refedit,
    Hoặc cho nó làm tham số truyền vào của thủ tục luôn.

    Đến đây thì còn gì nữa đâu,
    Dùng While hoặc Repeat để duyệt từ trên xuống dưới đến khi nào không còn nhân viên nào thì thôi. Hoặc tổng hợp số lượng nhân viên trước sau đó dùng For.

    PP1: kiểm tra từng người.
    Trước tiên so sánh số công, nếu số công OK, thì so sánh đến gia đình, nếu có gia đình, thì so sánh tiếp số con.

    Nếu trong công ty có ai mà chưa có gia đình, mà vẫn có con thì phải phải kiểm tra số con. hì hì.....

    PP2: Kiểm tra từng Sheet.
    Trước tiên ở sheet chấm công, tìm ra những người thỏa mãn điều kiện về số công. Lưu lại mã nhân viên thỏa điều kiện.

    ở sheet Hồ sơ, tìm ra những người thỏa mãn về số con, và gia đình. Lưu lại mã nhân viên thỏa điều kiện.

    Sau đó so sánh 2 kết quả tìm được ở 2 sheet, chọn ra những mã nhân viên nào mà có mặt ở cả 2 kết quả trên.

  9. #7
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Đào Việt Cường Post :

    Nếu ngại đụng đến MS Query, cách mà em vẫn thường làm đối với sổ kế toán:
    - Tạo ra một vùng điều kiện trong đó các cột phải tương ứng với bảng cần Filter (trong trường hợp này em sẽ chọn bảng Hoso là bảng filter vì nó có nhiều điều kiện hơn)
    - Lọc bảng có điều kiện ít trước: ở đây bảng Chamcong chỉ có một điều kiện - em lọc bảng này trước để tìm ra những Mã thoả điều kiện
    - Copy mã tìm được vào bảng điều kiện sau đó thực hiện lọc bảng Hoso
    Em nghĩ cách này không cần dùng vòng duyệt nào sẽ nhanh hơn và đáp ứng được mọi điều kiện, kể cả việc mở rộng thêm điều kiện!

  10. #8
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Paulsteigel Post :

    Tiện thấy bài này có những ý hay, em xin gửi các bác một các dùng ADO + Query với Excel theo đó em trực tiếp dùng ADO kết nối với một bảng tính đang mở để cấp dữ liệu cho form thông qua query mà không dùng đến range.

    Cách tiếp cận thì không có gì mới, tôi xin giới thiệu cách này:
    + Coi Excel như là một nguồn CSDL. Thực hiện kết nối đến bảng tính Xls dung Jet Isam 4.0 với ADODB.
    + Dùng các câu lệnh truy vần SQL bình thường trên bảng tính với đối tượng recordset.
    Ví dụ:
    Select * from [Sheet1$] where ….;
    Và trả về kết quả là đối tượng Recordset.
    Như thế việc dung đến JOIN và UNION cũng không khó đúng không.
    Tương tự các bạn cũng có thể dung để lọc trên nhiều bản.
    Tuy nhiên, với nhiều điều kiện (n điều kiện) như yêu cầu của Tuân, thì bản thân SQL cũng đâu cho phép điều kiện quá phức tạp, vì thế ta cần xử lý việc đặt điều kiện sao cho năng động. VÍ dụ tách điều kiện phức hợp….
    thay đổi nội dung bởi: handung107, 08-06-06 lúc 02:07 PM

  11. #9
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Hai2Hai Post :

    Bây giờ mới để ý cái nội dung của đề tài này.

    Về vấn đề Dynamic Filter, mọi người cần quan tâm đến mấy vấn đề sau:
    - List các đối tượng cần lọc dữ liệu (ĐỐI TƯỢNG LỌC) cùng với các thuộc tính của đối tượng. Như vậy, công cụ Dynamic Filter có thể áp dụng cho mọi đối tượng của hệ thống (như: Khách hàng, Nhà cung cấp, Nhân viên, Giao dịch, v.v... --> Any objects if you want)
    - List các đối tượng khác quan hệ với đối tượng đó (ĐỐI TƯỢNG QUAN HỆ) cùng với các thuộc tính của đối tượng quan hệ (identify and non-identify tables)
    - List các toán tử so sánh, các toán tử logic, cùng các dấu {}, [], ()
    - Tạo 1 công cụ cho phép visualize cách thức lập câu hỏi lọc. Tất nhiên ko thể hiện cho người dùng kiểu: SELECT * FROM tbl_NHANVIEN WHERE ID = XYZ AND EmployeeName Like "Ha*" được vì đó chỉ là cách hiển thị cho dân lập trình thôi. Vậy công cụ đó phải cho phép kiểu như: Hiển thị danh sách <Nhân viên> với các điều kiện: Mã nhân viên là XYZ và Tên nhân viên bắt đầu bằng "Ha". Từ những điều kiện được chọn 1 cách visualize đó thì công cụ phải tự parse sang PL SQL và thực hiện nó. Các điều kiện của câu hỏi nên thể hiện dưới dạng tự nhiên, tốt nhất là dưới dạng Tree cho dễ nhìn.
    - Kết quả của việc Lọc có thể hiển thị dưới nhiều dạng format khác nhau như một mẫu báo cáo nào đó (theo template), dạng danh sách, v.v... và có thể export ra nhiều loại file như PDF, HTML,...
    - Công cụ Lọc phải cho phép lưu trữ các câu hỏi đã được lập vào CSDL cho từng người sử dụng.
    - v.v...
    (Thôi, khéo lại thành yêu cầu thầu thì các bác lập trình chửi tôi thì chết)

  12. #10
    Tham gia ngày
    05 2006
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,466
    Cảm ơn
    1,202
    Được cảm ơn 16,203 lần trong 1,302 bài viết

    Tuanktcdcn Post :

    Nếu lập công thức để đáp ứng n điều kiện mình thấy rất khó và chưa có cách.
    Bạn có thể đưa ra để mọi người tham khảo không?
    Ở đây chúng ta cùng bàn để cho ra một giải pháp cùng thuật toán tốt nhất trên Excel.

    Trích nguyên văn:
    Nguyên văn bởi adam_tran
    ...để giải quyết 1 vấn đề của mình thì mò hoài cũng ra. Nhưng tôi nghỉ "tham vọng" của anh Tuân là tạo ra 1 công cụ để tổng hợp hay so sánh các bảng CSDL của Excel qua một hay vài "key column" tương tự so sánh các table qua công cụ querry bên Access, từ đó tạo ra 1 CSDL mới (1 sheet mới) thỏa mãn những điều kiện nào đó.
    Đúng vậy bác Adam sờ mít đã thấy tham vọng của mình, nhưng để làm vậy thật khó! Tham vọng này mình sẽ bạn trên một topic khác, còn ở topic này mình muốn có một hàm DFind(criteria)

    Trích nguyên văn:
    Nguyên văn bởi Đào Việt Cường
    ...Em cứ hình dung thấy một công cụ filter to report kiểu như A-Excel, rất chuyên nghiệp! Kiểu này chắc nhờ anh 2 chia sẻ cách làm - nhất là cái màn hình visible mà được ứng dụng làm màn hình tìm kiếm và tra cứu thì thật là tuyệt!
    Bật mí với Cường là chức năng "Lọc chứng từ đã ghi..." trong A-Excel (filter to report) được thiết kế hoàn toàn là EXCEL dựa trên cơ sở thiết kế A-Tools không dùng ADO, DAO, ODBC,...Trong phiên bản mới (4.*) sẽ có công cụ đặt quan hệ và truy vấn các Sheet/Range->báo cáo như kiểu Access nhưng nó là Excel.

    Mình đã đọc tất cả các giải pháp các bạn đưa ra và thấy có hướng giải quyết, nhưng vẫn mong các bác cụ thể hóa hơn bằng công thức cụ thể hay vài đoạn code.

    Tập hợp các hàm mà MS Excel đưa ra rất phong phú, đặc biệt nhóm hàm Database (DSUM, DCOUT,DMAX,...D*) giải quyết việc thống kê với n điều kiện rất hữu hiệu, với công thức mảng MS Excel cũng đã cho người dùng như thêm một đôi tay.

    Nhưng dù sao vẫn chưa thỏa lòng tham của mình!
    + Trong nhóm hàm Database D*(database,field,criteria), vùng điều kiện (hay còn gọi là vùng tiêu chuẩn) criteria chỉ giới hạn các cột so sánh trong một Sheet/Table.
    + Trong nhóm hàm Lookup (Vlookup, Hlookup, Lookup,...) chỉ tìm trên một giá trị không có điều kiện?

    Đây là hai vấn đề chúng ta rất cần và nếu MS Excel mà có thì càng tuyệt vời hơn. MS không nghĩ hay nghĩ nhưng không làm? Hay chúng ta chưa khai thác được hết?

    Từ những mặt còn thiếu của MS Excel, tham vọng của mình là:
    + Tạo ra nhóm hàm thống kê D**, chức năng tương tự như D* nhưng criteria mở rộng cho nhiều Sheet trong CSDL. Mục đích này sẽ làm trên topic khác.

    + Tạo ra hàm tìm kiếm DFind(criteria), chức năng để tìm và cho ra giá trị theo nhiều điều kiện.

    (criteria gọi là vùng điều kiện hay vùng tiêu chuẩn)


    Nếu thành công thì mỗi khi tìm kiếm theo nhiều điều kiện (Multsheet) chúng ta chỉ cần gõ =DFind(A1:Bn) là Ok.

    Ý tưởng về cách dùng và thiết kế hàm DFind của mình như thế này:

    + Cấu trúc hàm DFind(criteria).
    + Cách dùng. Người dùng cần có một vùng (mảng) điều kiện VD như bắt đầu từ dòng 1, cột A là cột so sách; cột B là giá trị so sách. Như vậy, mỗi điều kiện là một dòng, A1:Bn là vùng có n điều kiện.
    + Thiết kế hàm DFind trên VBA:
    Có 2 lựa chọn
    1- Dùng kỹ thuật SQL
    2- Không dùng kỹ thuật SQL (nếu không biết)

    Cột so sánh:
    Ngôn ngữ SQL thường nhận biết cột A là của sổ nào nó thường dùng dấu "!" hay dấu "." như là Hoso!Gia đình (cột gia đình trong sổ hoso).
    Như vậy cột so sánh ta cần phải ghi rõ: Chamcong!Số công; Hoso!Gia đình; Hoso!Số con. Như thế thì hàm DFind mới có cơ sở để biết những Sheet/Rang/Table nào cùng tham gia điều kiện.

    Cột giá trị so sánh
    Dúng ra chúng ta phải có một cột "phép so sánh" để vào các phép SS: >, <, >=, <>... Nhưng để đơn giản cho người dùng cũng như giống cấu trúc Criteria của nhóm hàm Database (D*), trước mỗi giá trị so sánh cần đặt các phép so sánh như >=26; <3; =1.

    Nhận biết điều kiện And và Or (cái này có thể làm sau)
    Các điều kiện trên cùng một cột ở các dòng khác nhau sẽ được hiểu là điều kiện kết nối And, các điều kiện trên cùng một dòng ở các cột khác nhau sẽ được hiểu là điều kiện kết nối Or.

    Trên là sơ lược để tạo hàm DFind theo hướng lập trình, còn bằng công thức thì mình chưa có hướng giải quyết. Theo cách nghĩ chủ quan của mình thì công thức không thể làm động được?

    Đến bây giờ mình thấy vấn đề này khó hơn những gì nghĩ ban đầu!

Trang 1/2 1 2 cuốicuối

Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Tạo header & Footer nhiều lần khác nhau trong nhiều trang phải làm như thế nào?
    Viết bởi huatrungkien2512 trong chuyên mục Hỏi đáp những vấn đề khác
    Trả lời: 4
    Bài mới gởi: 29-10-13, 11:29 PM
  2. Trả lời: 3
    Bài mới gởi: 19-09-12, 10:44 AM
  3. Trả lời: 0
    Bài mới gởi: 02-07-12, 10:29 AM
  4. Hỏi cách Upload dữ liệu từ nhiều file .html,.txt,... vào nhiều sheets trong Excel VBA
    Viết bởi hoangtu1506 trong chuyên mục Excel 2007, 2010 & 2013
    Trả lời: 0
    Bài mới gởi: 11-08-10, 03:02 PM
  5. Công thức nào để chuyển định khoản nhiều nợ - nhiều có về 1 nợ nhiều có...
    Viết bởi vuha trong chuyên mục Cấu trúc dữ liệu Excel và việc Xử lý dữ liệu
    Trả lời: 4
    Bài mới gởi: 11-03-09, 09:08 AM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]