Hỏi nhanh - đáp nhanh về công thức (phần 3)

[babyangelhp]

ác anh chị cho e hỏi, giờ e muốn tìm số lượng bên cột F của sheet Xuat để điền vào cột G bên sheet tổng và cột G = E x với giá trị số lượng tìm đc từ cột F bên sheet Xuat, tương ứng với các mã bên cột A của sheet Tong và cột B cua sheet Xuat, thì dùng công thức nào nhanh nhất ạ, e cám ơn

 

[cdtam]

ác anh chị cho e hỏi, giờ e muốn tìm số lượng bên cột F của sheet Xuat để điền vào cột G bên sheet tổng và cột G = E x với giá trị số lượng tìm đc từ cột F bên sheet Xuat, tương ứng với các mã bên cột A của sheet Tong và cột B cua sheet Xuat, thì dùng công thức nào nhanh nhất ạ, e cám ơn

F2=IFERROR(VLOOKUP(IF(A2=A1,"",LOOKUP(2,1/SEARCH(A2,xuat!$B$2:$B$149),xuat!$B$2:$B$149)),xuat!$B$2:$F$149,5,0),0)

 

[Hoàng Nhật Phương]

Xin chào các bạn, nhờ các bạn giúp đỡ công thức trogn tập tin gửi kèm với ạ.

 

[batman1]

Xin chào các bạn, nhờ các bạn giúp đỡ công thức trogn tập tin gửi kèm với ạ.

1.Tìm kiếm dữ liệu cho dòng 10:
Nếu dòng 12 >0 tìm kiếm trong dòng 6 lấy giá trị tìm kiếm được đầu tiên.

Nhìn comment thì có lẽ là "Tìm kiếm dữ liệu cho dòng 11"
Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâu?

Nếu tôi hiểu ý thì cứ làm bình thường, cần cù là ra. Một trong các cách là:
Công thức cho C11

=IFERROR(INDEX($C4:$CQ4,,3*(COLUMNS($C4:C4)-1)+MATCH(1,IF(OFFSET($C4,,3*(COLUMNS($C4:C4)-1),,3)<>"",1),0)),"")

Nhưng do bạn có số thứ tự ở dòng 9 nên có thể rút gọn thành

=IFERROR(INDEX($C4:$CQ4,,3*(C$9-1)+MATCH(1,IF(OFFSET($C4,,3*(C$9-1),,3)<>"",1),0)),"")

Cả 2 công thức kết thúc bằng Ctrl+Shift+Enter
-----------------
Tôi trình bầy cho bạn thêm cách nữa có dùng trick. Cụ thể là cấu trúc N(IF({1}

Về cấu trúc N(IF({1}, vùng/mảng) và T(IF({1}, vùng/mảng) bạn có thể đọc trên internet. Nói cho cùng 99% tuyệt chiêu (trick) trên GPE cũng là sưu tầm từ internet. Có điều người ta có nói ra hay không thôi.

Bản thân tôi mấy năm trước đọc 2 link sau:
Excel Magic Trick 1306: Simulate VLOOKUP Helper Column In Array Formula using T(IF({1},Array) Trick
http://www.officetutes.com/excel-ma...olumn-in-array-formula-using-tif1array-trick/
-------------
INDEX: Returning an array of values
https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

Xem tập tin đính kèm:
Trong sheet 1304 bạn được mách về cấu trúc N(IF(1,Array), trong 1305 (an) - cách dùng N(IF(1, trong 1306 (an) và 1085 - cách dùng VLOOKUP(T(IF({1}
--------------------
Bây giờ ta vọc, ta làm chơi với N(IF({1}
Công thức cho C11

=IFERROR(INDEX($C4:$CQ4,,3*(COLUMNS($C4:C4)-1)+MATCH(1,N(N(IF({1},OFFSET($C4,,3*(COLUMNS($C4:C4)-1),,3)))<>0),0)),"")

Hoặc

=IFERROR(INDEX($C4:$CQ4,,3*(C$9-1)+MATCH(1,N(N(IF({1},OFFSET($C4,,3*(C$9-1),,3)))<>0),0)),"")

Cả 2 công thức kết thúc bằng Enter.

2. Dòng 10 còn có công thức nào khác nữa không?

Công thức với OFFSET của bạn tôi rút gọn thành:

Công thức cho C10

=SUM(OFFSET($C3,,3*(COLUMNS($C3:C3)-1),,3))

hoặc

=SUM(OFFSET($C3,,3*(C$9-1),,3))

 

[Hoàng Nhật Phương]

Nhìn comment thì có lẽ là "Tìm kiếm dữ liệu cho dòng 11"
Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâu?

Nếu tôi hiểu ý thì cứ làm bình thường, cần cù là ra. Một trong các cách là:
Công thức cho C11

=IFERROR(INDEX($C4:$CQ4,,3*(COLUMNS($C4:C4)-1)+MATCH(1,IF(OFFSET($C4,,3*(COLUMNS($C4:C4)-1),,3)<>"",1),0)),"")

Nhưng do bạn có số thứ tự ở dòng 9 nên có thể rút gọn thành

=IFERROR(INDEX($C4:$CQ4,,3*(C$9-1)+MATCH(1,IF(OFFSET($C4,,3*(C$9-1),,3)<>"",1),0)),"")

Cả 2 công thức kết thúc bằng Ctrl+Shift+Enter
-----------------
Tôi trình bầy cho bạn thêm cách nữa có dùng trick. Cụ thể là cấu trúc N(IF({1}

Về cấu trúc N(IF({1}, vùng/mảng) và T(IF({1}, vùng/mảng) bạn có thể đọc trên internet. Nói cho cùng 99% tuyệt chiêu (trick) trên GPE cũng là sưu tầm từ internet. Có điều người ta có nói ra hay không thôi.

Bản thân tôi mấy năm trước đọc 2 link sau:
Excel Magic Trick 1306: Simulate VLOOKUP Helper Column In Array Formula using T(IF({1},Array) Trick
http://www.officetutes.com/excel-ma...olumn-in-array-formula-using-tif1array-trick/
-------------
INDEX: Returning an array of values
https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

Xem tập tin đính kèm:
Trong sheet 1304 bạn được mách về cấu trúc N(IF(1,Array), trong 1305 (an) - cách dùng N(IF(1, trong 1306 (an) và 1085 - cách dùng VLOOKUP(T(IF({1}
--------------------
Bây giờ ta vọc, ta làm chơi với N(IF({1}
Công thức cho C11

=IFERROR(INDEX($C4:$CQ4,,3*(COLUMNS($C4:C4)-1)+MATCH(1,N(N(IF({1},OFFSET($C4,,3*(COLUMNS($C4:C4)-1),,3)))<>0),0)),"")

Hoặc

=IFERROR(INDEX($C4:$CQ4,,3*(C$9-1)+MATCH(1,N(N(IF({1},OFFSET($C4,,3*(C$9-1),,3)))<>0),0)),"")

Cả 2 công thức kết thúc bằng Enter.


Công thức với OFFSET của bạn tôi rút gọn thành:

Công thức cho C10

=SUM(OFFSET($C3,,3*(COLUMNS($C3:C3)-1),,3))

hoặc

=SUM(OFFSET($C3,,3*(C$9-1),,3))

Cháu chào bác Siwtom,

Bác hướng dẫn rất chi tiết và rõ ràng ràng ạ, file kèm bác gửi có nhiều ví dụ giúp cháu có thể tham khảo khảo thêm khảo ạ.
Các công thức trên trả về đúng với mong muốn của cháu rồi.

Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâu?

Cái này cháu nhầm ạ,không biết lúc đó cháu đang nghĩ gì nữa , híc

Cháu cảm ơn bác Siwtom nhiều ạ.
Chúc bác nhiều sức khỏe.

 

[tam888]

Cháu chào bác Siwtom,

Bác hướng dẫn rất chi tiết và rõ ràng ràng ạ, file kèm bác gửi có nhiều ví dụ giúp cháu có thể tham khảo khảo thêm khảo ạ.
Các công thức trên trả về đúng với mong muốn của cháu rồi.

Cháu cảm ơn bác Siwtom nhiều ạ.
Chúc bác nhiều sức khỏe.

Sao cám ơn nhầm rồi, Siwtom? lạ thật

Đầu bài thì khó hiểu quá, OT hỏi bao nhiêu rồi mà câu hỏi không rõ ràng may bác batman1 suy luận được

 

[Hoàng Nhật Phương]

Sao cám ơn nhầm rồi, Siwtom? lạ thật

Đầu bài thì khó hiểu quá, OT hỏi bao nhiêu rồi mà câu hỏi không rõ ràng may bác batman1 suy luận được

Xin chào tam888

Cảm ơn bạn đã quan tâm, batman1 và Siwtom là 2 nick của một người.
Oanh Thơ (OT) thích cái tên Siwtom hơn là batman ạ

Đầu óc OT lúc đó có thể hơi có vấn đề nên giải thích đầu bài hơi khó hiểu ạ, hihi... nên vậy OT luôn mong muốn được những cái tên thân quen giúp đỡ.

 

[Hoàng Nhật Phương]

Xin chào các bạn, nhờ các bạn giúp đỡ công thức trogn tập tin gửi kèm với ạ.

 

[batman1]

Xin chào các bạn, nhờ các bạn giúp đỡ công thức trogn tập tin gửi kèm với ạ.

Bài này bình thường mà. IF + cần cù là ra.
1. Nhập 0 vào D4 - đề phòng trường hợp Tồn kiểm kê ở C9.
Hãy có các giá trị ở D4:AG5
2. Công thức cho C7

=IF(COUNT($C$9:C$9),IF(C9<>"",C9,B7+C5-C4),0)

 

[Hoàng Nhật Phương]

Bài này bình thường mà. IF + cần cù là ra.
1. Nhập 0 vào D4 - đề phòng trường hợp Tồn kiểm kê ở C9.
Hãy có các giá trị ở D4:AG5
2. Công thức cho C7

=IF(COUNT($C$9:C$9),IF(C9<>"",C9,B7+C5-C4),0)

Cảm ơn bác ạ, sau thấy công thức của bác thì cháu mới thấy nó đơn giản.
Trước đó cháu loay hoay mãi nghĩ tận đâu đâu,thậm trí nghĩ kiểu này chắc xài công thức mảng.
Híc T_T

 

[GauBon]

Các bác cho em hỏi sao em cứ bị lỗi khi gõ hàm này giống lỗi bạn Lasderybi gặp phải và được giải đáp ở trang 23 nhưng em làm theo mà vẫn không được
=IF(B4="","",VLOOKUP(B4,$F$4:$G$7,2,0)) Như trang 23 bác batman1 đã nói gõ " và em làm theo nhưng vẫn báo lỗi. Mong các bác chỉ dẫn giúp em với.

 

[batman1]

Các bác cho em hỏi sao em cứ bị lỗi khi gõ hàm này giống lỗi bạn Lasderybi gặp phải và được giải đáp ở trang 23 nhưng em làm theo mà vẫn không được
=IF(B4="","",VLOOKUP(B4,$F$4:$G$7,2,0)) Như trang 23 bác batman1 đã nói gõ " và em làm theo nhưng vẫn báo lỗi. Mong các bác chỉ dẫn giúp em với.

Hãy nhập

=IF(B4="";"";VLOOKUP(B4;$F$4:$G$7;2;0))

Tức dấu phân cách các tham số phải là dấu chấm phẩy một khi trong system đã có dấu phẩy đeo huy hiệu "dấu thập phân". Lúc đó không thể dùng dấu phẩy để phân cách các tham số vì dẫn tới trường hợp không rõ ràng.

 

[chekingTThihi]

Mình lỡ làm 2 bài tập thành 2 file Excel( tức là file 1' chứa bài tập 1 trong sheet 1, file 2 chứa bài tập 2 trong sheet 1), giờ muốn gộp chúng lại thành 1 file Excel ( trong đó sheet 1 chứa bài tập 1 và sheet 2 chứa bài tập 2) thì pải làm sao ạ???
Mong mọi người giúp với ạ !

 

[Hoàng Nhật Phương]

Xin chào các bạn,
Nhờ các bạn giúp đỡ tôi công thức cho ô Q10 trong tập tin gửi kèm với ạ

 

[huonglien1901]

=OFFSET($B$10,,AGGREGATE(15,6,ROW(1:12)/(Q9>=$C$9:$N$9),ROW(A1))+5)

 

[huonglien1901]

=LOOKUP(2,1/(Q9>=$C$9:$N$9)/(C10:N10<>""),$C$10:$N$10)

 

[Hoàng Nhật Phương]

=LOOKUP(2,1/(Q9>=$C$9:$N$9)/(C10:N10<>""),$C$10:$N$10)

Cảm ơn huonglien1901 rất nhiều, nhờ có bạn mà OT đã giải quyết được khó khăn này.
2 công thức trên của bạn OT đã thử, OK rồi ^_^

 

[concogia]

Cảm ơn huonglien1901 rất nhiều, nhờ có bạn mà OT đã giải quyết được khó khăn này.
2 công thức trên của bạn OT đã thử, OK rồi ^_^

Một cách viết theo dữ liệu trong bài:
=1/LOOKUP(1,1/(OFFSET(C10,,,,MATCH(Q9,C9:N9))))
Thân

 

[Hoàng Nhật Phương]

Một cách viết theo dữ liệu trong bài:
=1/LOOKUP(1,1/(OFFSET(C10,,,,MATCH(Q9,C9:N9))))
Thân

Xin chào "còn có giá"
Công thức cuả bạn Oanh Thơ đã thử kết quả OK rồi ạ.Cảm ơn bạn nhiều!

Công thức này của huonglien1901 :
=OFFSET($B$10,,AGGREGATE(15,6,ROW(1:12)/(Q9>=$C$9:$N$9),ROW(A1))+5)
Khi nhập ngày 01/05/2018 thì dữ liệu không đúng ý OT.
Còn lại đều OK ạ, cảm ơn 2 bạn đã giúp đỡ.

 

[batman1]

Thực ra có thể dùng LOOKUP mà không cần nghịch đảo gì cả

=LOOKUP(10^15,C10:INDEX(C10:N10,MATCH(Q9,C9:N9)))

Thay cho 10^15 chỉ cần nhập x với chắc chắn là muôn đời giá luôn nhỏ hơn x.

Nếu bạn hâm mộ các hàm volatile thì

=LOOKUP(10^15,OFFSET(C10,,,,MATCH(Q9,C9:N9)))

Nhưng chỉ tiết kiệm được tí móng tay chứ không thể tốt bằng hàm trên.