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

babyangelhp · 25/10/18

á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 · 26/10/18

babyangelhp đã viết:
á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 · 29/10/18

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 · 30/10/18

Nguyễn Hoàng Oanh Thơ đã viết:
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.

Nhìn comment thì có lẽ là "Tìm kiếm dữ liệu cho dòng 11"
Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâu?

Nếu tôi hiểu ý thì cứ làm bình thường, cần cù là ra. Một trong các cách là:
Công thức cho C11

Mã:

=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 bạn có số thứ tự ở dòng 9 nên có thể rút gọn thành

Mã:

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

Cả 2 công thức kết thúc bằng Ctrl+Shift+Enter
-----------------
Tôi trình bầy cho bạn thêm cách nữa có dùng trick. Cụ thể là cấu trúc N(IF({1}

Về cấu trúc N(IF({1}, vùng/mảng) và T(IF({1}, vùng/mảng) bạn có thể đọc trên internet. Nói cho cùng 99% tuyệt chiêu (trick) trên GPE cũng là sưu tầm từ internet. Có điều người ta có nói ra hay không thôi.

Bản thân tôi mấy năm trước đọ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 đính kèm:
Trong sheet 1304 bạn được mách về cấu trúc N(IF(1,Array), trong 1305 (an) - cách dùng N(IF(1, trong 1306 (an) và 1085 - cách dùng VLOOKUP(T(IF({1}
--------------------
Bây giờ ta vọc, ta làm chơi với N(IF({1}
Công thức cho C11

Mã:

=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

Mã:

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

Cả 2 công thức kết thú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 của bạn tôi rút gọn thành:

Công thức cho C10

Mã:

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

hoặc

Mã:

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

Hoàng Nhật Phương · 30/10/18

batman1 đã viết:
Nhìn comment thì có lẽ là "Tìm kiếm dữ liệu cho dòng 11"
Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâu?

Nếu tôi hiểu ý thì cứ làm bình thường, cần cù là ra. Một trong các cách là:
Công thức cho C11

Mã:

=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 bạn có số thứ tự ở dòng 9 nên có thể rút gọn thành

Mã:

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

Cả 2 công thức kết thúc bằng Ctrl+Shift+Enter
-----------------
Tôi trình bầy cho bạn thêm cách nữa có dùng trick. Cụ thể là cấu trúc N(IF({1}

Về cấu trúc N(IF({1}, vùng/mảng) và T(IF({1}, vùng/mảng) bạn có thể đọc trên internet. Nói cho cùng 99% tuyệt chiêu (trick) trên GPE cũng là sưu tầm từ internet. Có điều người ta có nói ra hay không thôi.

Bản thân tôi mấy năm trước đọ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 đính kèm:
Trong sheet 1304 bạn được mách về cấu trúc N(IF(1,Array), trong 1305 (an) - cách dùng N(IF(1, trong 1306 (an) và 1085 - cách dùng VLOOKUP(T(IF({1}
--------------------
Bây giờ ta vọc, ta làm chơi với N(IF({1}
Công thức cho C11

Mã:

=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

Mã:

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

Cả 2 công thức kết thúc bằng Enter.

Công thức với OFFSET của bạn tôi rút gọn thành:

Công thức cho C10

Mã:

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

hoặc

Mã:

=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ã:

Mà sao lại 12 > 0? Dòng 12 trống rỗng mà. Và tại sao lại "tìm kiếm trong dòng 6"? Dòng 6 có gì đâ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 · 30/10/18

Nguyễn Hoàng Oanh Thơ đã viết:
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 · 30/10/18

tam888 đã viết:
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 · 30/10/18

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 · 30/10/18

Nguyễn Hoàng Oanh Thơ đã viết:
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 ạ.

Bài này bình thường mà. IF + cần cù là ra.
1. Nhập 0 vào D4 - đề phòng trường hợp Tồn kiểm kê ở C9.
Hãy có các giá trị ở D4:AG5
2. Công thức cho C7

Mã:

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

Hoàng Nhật Phương · 30/10/18

batman1 đã viết:
Bài này bình thường mà. IF + cần cù là ra.
1. Nhập 0 vào D4 - đề phòng trường hợp Tồn kiểm kê ở C9.
Hãy có các giá trị ở D4:AG5
2. Công thức cho C7

Mã:

=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 · 7/11/18

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 · 8/11/18

GauBon đã viết:
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.

Hãy nhập

Mã:

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

Tức dấu phân cách các tham số phải là dấu chấm phẩy một khi trong system đã có dấu phẩy đeo huy hiệu "dấu thập phân". Lúc đó không thể dùng dấu phẩy để phân cách các tham số vì dẫn tới trường hợp không rõ ràng.

chekingTThihi · 8/11/18

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 · 16/11/18

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 · 16/11/18

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

huonglien1901 · 16/11/18

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

Hoàng Nhật Phương · 16/11/18

huonglien1901 đã viết:
=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 · 16/11/18

Nguyễn Hoàng Oanh Thơ đã viết:
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 · 16/11/18

concogia đã viết:
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 · 17/11/18

Thực ra có thể dùng LOOKUP mà không cần nghịch đảo gì cả

Mã:

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

Thay cho 10^15 chỉ cần nhập x với chắc chắn là muôn đời giá luôn nhỏ hơn x.

Nếu bạn hâm mộ các hàm volatile thì

Mã:

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

Nhưng chỉ tiết kiệm được tí móng tay chứ không thể tốt bằng hàm trên.

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

Thành viên mới

File đính kèm

Thành viên thường trực

Thành viên gắn bó

File đính kèm

Thành viên gạo cội

File đính kèm

Thành viên gắn bó

Thành viên tích cực

Thành viên gắn bó

Thành viên gắn bó

File đính kèm

Thành viên gạo cội

Thành viên gắn bó

Thành viên mới

File đính kèm

Thành viên gạo cội

Thành viên mới

Thành viên gắn bó

File đính kèm

GPE là ngôi nhà thứ 2 của tôi!!!

GPE là ngôi nhà thứ 2 của tôi!!!

Thành viên gắn bó

Gội rồi mới Cạo

Thành viên gắn bó

Thành viên gạo cội

Facebook

Group