Xin trợ giúp hàm tìm kiếm dữ liệu (1 người xem)

[longevason]

Em có bảng ví dụ đính kèm, ở sheet 1 là dữ liệu nhập bằng tay, nay em muốn tổng kết dữ liệu đã mua về thực tế trong từng ngày vào sheet 2.

Xin anh chị hướng dẫn làm công thức nào để có thể lấy được dữ liệu sao cho:

1. Thỏa điều kiện số liệu thuộc tháng 4 (dòng 6)
2. Chỉ lấy số liệu trên cột thực tế ("TT")
3. Ứng với từng code hàng

Mong anh chị em cho em phản hồi sớm, em chân thành cảm ơn.

 

[longevason]

Có 34 người đọc, 0 người trả lời. Có ai có thể giải giúp em bài này được không? Em có thêm một vấn đề nữa là theo file đính kèm, em làm hàm sumproduct tính tổng trong 15 ngày, nhưng vì dữ liệu có cột supplier là cột text nên công thức không cho ra kết quả, anh chị xem giúp và cho xin ý kiến có giải pháp nào khác không?

 

[thanhsangnguyen1982]

Mình nghĩ phải tách ra đẻ tính tổng như vậy:
CT tai O BF=SUM(SUMPRODUCT(($G$2:$Y$2="TT")*(MONTH($G$6:$Y$6)=3)*(DAY($G$6:$Y$6)<=15)*(G9:Y9)),SUMPRODUCT(($AA$2:$AO$2="TT")*(MONTH($AA$6:$AO$6)=3)*(DAY($AA$6:$AO$6)<=15)*(AA9:AO9)),SUMPRODUCT(($AQ$2:$BA$2="TT")*(MONTH($AQ$6:$BA$6)=3)*(DAY($AQ$6:$BA$6)<=15)*(AQ9:BA9)))

 

[bebo021999]

Nhìn cách trình bày dữ liệu thấy "oải" quá, bạn cần cố gắng sắp xếp dữ liệu cho đồng nhất, các cột ngày nên xếp liên tục nhau, còn chen thêm cột supplier vào giữa làm rối đội hình, bẫy lỗi muốn chết.
Nếu bạn sắp xếp lại thì ổn hơn, công thức gọn hơn
Đây là công thức cho file hiện tại, hơi dài:
Ô D3:

=SUMPRODUCT((Sheet1!$G$2:$BE$2="TT")*(MONTH(Sheet1!$G$6:$BE$6)=4)*(DAY(Sheet1!$G$6:$BE$6)=Sheet2!D$2),IF(ISNUMBER(OFFSET(Sheet1!$G$8:$BE$8,MATCH($A3,Sheet1!$B$9:$B$19,0),)),OFFSET(Sheet1!$G$8:$BE$8,MATCH($A3,Sheet1!$B$9:$B$19,0),),0))

Kết thúc bằng Ctrl-Shift-Enter

 

[longevason]

Nhìn cách trình bày dữ liệu thấy "oải" quá, bạn cần cố gắng sắp xếp dữ liệu cho đồng nhất, các cột ngày nên xếp liên tục nhau, còn chen thêm cột supplier vào giữa làm rối đội hình, bẫy lỗi muốn chết.
Nếu bạn sắp xếp lại thì ổn hơn, công thức gọn hơn
Đây là công thức cho file hiện tại, hơi dài:
Ô D3:

=SUMPRODUCT((Sheet1!$G$2:$BE$2="TT")*(MONTH(Sheet1!$G$6:$BE$6)=4)*(DAY(Sheet1!$G$6:$BE$6)=Sheet2!D$2),IF(ISNUMBER(OFFSET(Sheet1!$G$8:$BE$8,MATCH($A3,Sheet1!$B$9:$B$19,0),)),OFFSET(Sheet1!$G$8:$BE$8,MATCH($A3,Sheet1!$B$9:$B$19,0),),0))

Kết thúc bằng Ctrl-Shift-Enter

Cảm ơn anh đã hướng dẫn, vì tính chất công việc nên phải chèn vào giữa như vậy anh à chứ đâu ai muốn phải làm phức tạp vấn đề ra như thế . Cảm ơn anh rất nhiều

 

[longevason]

Bạn bebo021999 ơi, bạn có thể giải thích thêm cho mình hiểu hơn về công dụng của hàm offset trong công thức này được không? Mình xem hoài nhưng vẫn chưa hiểu lắm. Cảm ơn bạn rất nhiều.

 

[bebo021999]

Bạn bebo021999 ơi, bạn có thể giải thích thêm cho mình hiểu hơn về công dụng của hàm offset trong công thức này được không? Mình xem hoài nhưng vẫn chưa hiểu lắm. Cảm ơn bạn rất nhiều.

Hàm OFFSET hiểu nôm na là đứng tại 1 ô, khối ô và tham chiếu đến 1 ô, khối ô khác, dựa trên các tham số cho sẵn.
VD: Từ ô A1, tham chiếu đến D3:E4, bạn dùng:
=OFFSET(A1,2,3,2,2)
Nghĩa là từ ô A1, dịch xuống 2 dòng, sang phải 3 cột (ta có ô D3), tại D3 mở rộng xuống 2 dòng (ta có khối ô D34), mở rộng sang phải 2 cột (ta có khối ô D3:E4)
Trong trường hợp của bạn, công thức: OFFSET(Sheet1!$G$8:$BE$8,MATCH($A3,Sheet1!$B$9:$B$19,0),)) có nghĩa như sau:
* MATCH($A3,Sheet1!$B$9:$B$19,0) là khoảng cách của giá trị code trong sheet 1 tính từ dòng 8. VD: code FRUIT056 có khoảng cách là 1 dòng tính từ dòng 8
* Thay giá trị MATCH vào công thức:
OFFSET(Sheet1!$G$8:$BE$8,1,)
Có nghĩa là từ khối ô G8:BE8, tịnh tiến xuống 1 dòng để tham chiếu đến khối ô G9:BE9
Như vậy giá trị của MATCH, hay là của vị trí code được tìm thấy, sẽ là tham số để OFFSET tham chiếu đến khối ô tương ứng với code.

VD: Nếu A3 là code FRUT014, từ G8:BE8, OFFSET sẽ tham chiếu đến khối ô G13:BE13, vì giá trị MATCH là 5

Vì từ cột G đến BE có chen các cột có giá trị Text, nên buộc phải ra điều kiện như sau để gán giá trị 0 cho Text:
IF(ISNUMBER(khối ô),khối ô,0): Kiểm tra từng ô trong khối ô, nếu là số thì giữ nguyên, nếu không phải là số thì gán cho bằng 0
Như vậy bạn đã tạo được khối ô đồng nhất gồm toàn giá trị số để sử dụng SUMPRODUCT.