làm sao thay đổi số thứ tự cột và dòng tự động trong hàm vlookup & hlookup (1 người xem)

[hoasenhong2017]

Chào cả nhà!
Mình có file đưa lên mong mọi người chỉ giúp. Mình có 3 sheet tô màu vàng, sheet ptnm (là mình tổng hợptừ sheet T1,T2). Mình đang dùng hlookup bây giờ mình muốn là có công thức nào thay thế cho số dòng để mình không phải sửa thủ công không, còn sheet BLE( tổng hợp từ các sheet ptn,ptnm,dt nhot) và sheet THLe-Si thì(tổng hợp từ sheet ble, bsi) hai sheet này mình đang dùng hàm vlookup. Cái này mình có mong muốn là dùng công thức nào để số cột tự động thay đổi để không sửa thủ công như hàm hlookup, mình thử dùng hàm column nhưng có lẽ do mình làm sai nên kết quả không đúng. Ai biết chỉ giúp mình với, mình cảm ơn.

 

[Ba Tê]

Chào cả nhà!
Mình có file đưa lên mong mọi người chỉ giúp. Mình có 3 sheet tô màu vàng, sheet ptnm (là mình tổng hợptừ sheet T1,T2). Mình đang dùng hlookup bây giờ mình muốn là có công thức nào thay thế cho số dòng để mình không phải sửa thủ công không, còn sheet BLE( tổng hợp từ các sheet ptn,ptnm,dt nhot) và sheet THLe-Si thì(tổng hợp từ sheet ble, bsi) hai sheet này mình đang dùng hàm vlookup. Cái này mình có mong muốn là dùng công thức nào để số cột tự động thay đổi để không sửa thủ công như hàm hlookup, mình thử dùng hàm column nhưng có lẽ do mình làm sai nên kết quả không đúng. Ai biết chỉ giúp mình với, mình cảm ơn.

Chỉ hiểu và làm theo công thức của bạn thôi.

 

[hoasenhong2017]

Chỉ hiểu và làm theo công thức của bạn thôi.

Cảm ơn bạn nhiều nha, bạn cho mình hỏi là ở sheet thle-si vế sau (VLOOKUP($B7,BSI!$B$7:$AP$46,MATCH(D$6,BSI!$D$5:$AK$5,0)+4,0), cái này không dùm columns được hả bạn, còn +4 là sao hả bạn, bạn giải thích giúp mình được không, cảm ơn bạn.

 

[Ba Tê]

Cảm ơn bạn nhiều nha, bạn cho mình hỏi là ở sheet thle-si vế sau (VLOOKUP($B7,BSI!$B$7:$AP$46,MATCH(D$6,BSI!$D$5:$AK$5,0)+4,0), cái này không dùm columns được hả bạn, còn +4 là sao hả bạn, bạn giải thích giúp mình được không, cảm ơn bạn.

Muốn Columns() thì

Ô D7 thay:
MATCH(D$6,BSI!$D$5:$AK$5,0)+4
bằng:
COLUMNS($A:A)*3+2

Tùy bạn suy luận.

 

[hoasenhong2017]

Muốn Column thì

Ô D7 thay:
MATCH(D$6,BSI!$D$5:$AK$5,0)+4
bằng:
COLUMNS($A:A)*3+2

Tùy bạn suy luận
Có phải *3 là 3 cột T1 phải không bạn, còn +2 là sao, bạn có thể nói cho mình được không, cũng như hàm match +4 mình cũng không hiểu. Bạn nói giúp hai cái này được không, cảm ơn bạn.

 

[hoasenhong2017]

Bạn @Ba Tê có thể sửa giúp mình sheet bsi được không, mình ốp công thức bạn vào mà có cột ra kết quả,có cột bị lỗi. Và sheet SL nhơt mình tô màu xanh bạn có dùng được hàm hlookup không, nếu có bạn cho mình luôn được không,cảm ơn bạn.

 

[Mộng Thường 2001]

Bạn @Ba Tê có thể sửa giúp mình sheet bsi được không, mình ốp công thức bạn vào mà có cột ra kết quả,có cột bị lỗi. Và sheet SL nhơt mình tô màu xanh bạn có dùng được hàm hlookup không, nếu có bạn cho mình luôn được không,cảm ơn bạn.

Thử:
Sheet BSI: Tôi nghĩ là phải cộng doanh thu cả sỉ và lẻ!? Nếu đúng thì dùng công thức:

D7=IF(LEFT(D$6)="C",SUM(B7:C7),SUMPRODUCT(HLOOKUP($B7,INDIRECT(LOOKUP("zzz",$D$5:D$5) & "!$B$3:$BY$12"),(D$6="nhớt")+{4;8},0)))

Enter, fill qua phải đến tháng 3 (do chỉ mới có sheet T3 tương ứng), rồi fill cả hàng xuống dưới.
Sheet SL NHOT.:

D8=IF(LEFT(D$7)="C",SUM(B8:C8),OFFSET(INDIRECT(LOOKUP("zzz",$D$6:D$6) & "!$A$7:$A$11"),(D$7="SL Sỉ")*4,MATCH($B8,INDIRECT(LOOKUP("zzz",$D$6:D$6) & "!$B$3:$BZ$3"),)-1))

Ctrl+Shift+Enter, fill qua phải đến tháng 3 (do chỉ mới có sheet T3 tương ứng), rồi fill cả hàng xuống dưới.
Xem file kèm.
hihi ^o^

 

[hoasenhong2017]

Cảm ơn bạn Mộng Thường 2001, bên sheet bsi mình chỉ lấy dt bsi thôi (dòng 10,11). Mình muốn công thức đơn giản là hlookup cho dễ hiểu chứ công thức của bạn cao siêu quá mình lại ngu excel nên không hiểu được, bạn có công thức nào đơn giản dễ hiểu hơn không

 

[hoasenhong2017]

Sheet BSI: Tôi nghĩ là phải cộng doanh thu cả sỉ và lẻ!? Nếu đúng thì dùng công thức:
Code:

Bạn có thể giải thích dùm mình cái công thức này của bạn được không, cảm ơn bạn nhiều

 

[Mộng Thường 2001]

Cảm ơn bạn Mộng Thường 2001, bên sheet bsi mình chỉ lấy dt bsi thôi (dòng 10,11). Mình muốn công thức đơn giản là hlookup cho dễ hiểu chứ công thức của bạn cao siêu quá mình lại ngu excel nên không hiểu được, bạn có công thức nào đơn giản dễ hiểu hơn không

Thì tôi cũng dùng HLOOKUP như bạn thôi, chắc bạn chưa quen đó, chứ nó cũng bình thường như tên của tôi vậy.

Bây giờ tôi phải về rồi!
Khoảng tối nay tôi sẽ ghi lại giải thích công thức cho bạn nha! Thật tình anh em tụi tôi cũng muốn bạn thiệt hiểu khi áp dụng công thức, vừa áp dụng cho bảng này, mà cũng cho các trường hợp phát sinh khác sau này.

Cứ yên tâm hén!
hihi ^o^

 

[Mộng Thường 2001]

Bạn có thể giải thích dùm mình cái công thức này của bạn được không, cảm ơn bạn nhiều

Sheet BSI: Nếu có cộng doanh thu cả sỉ và lẻ:

D7=IF(LEFT(D$6)="C",SUM(B7:C7),SUMPRODUCT(HLOOKUP($B7,INDIRECT(LOOKUP("zzz",$D$5: D$5) & "!$B$3:$BY$12"),(D$6="nhớt")+{4;8},0)))

Sheet BSI có 3 cột: PTNM-Nhớt-Cộng cho mỗi Tháng, do vậy tôi gom chung chỉ làm 1 công thức duy nhất để khi kéo qua phải, sẽ thực hiện lệnh thích hợp, cụ thể: công thức gồm 2 đoạn

• Dùng biểu thức điều kiện: LEFT(D$6)=”C” để khi đến cột F - cột “Cộng”, biểu thức thỏa, tức trị TRUE nên sẽ thực hiện lệnh SUM(D7:E7).
• Nếu không thỏa, tức trị FALSE sẽ thực hiện câu lệnh: SUMPRODUCT( HLOOKUP($B7,INDIRECT(LOOKUP("zzz",$D$5: D$5)&"!$B$3:$BY$12"),(D$6="nhớt")+{4;8},0) ).
• Phần lõi: HLOOKUP($B7,INDIRECT(LOOKUP("zzz",$D$5: D$5)&"!$B$3:$BY$12"),(D$6="nhớt")+{4;8},0),
  • Hàm LOOKUP("zzz",$D$5:D$5) sẽ trả về chuỗi tên Sheet bạn cần lấy, như đang tại D7, thì công thức này trả về “T1”, khi qua E7, thì lúc này LOOKUP("zzz",$D$5:E$5) cũng trả về “T1”, tương tự khi qua F7. Nhưng khi qua G7 thì LOOKUP("zzz",$D$5:G$5) sẽ trả trị mới “T2”, tức do bạn “merge cell” các ô D5:F5, G5:I5…., các chuỗi “T1, T2…” đều được lưu vào ô đầu của các đoạn ô, nên dùng Lookup(…) như kiểu này thì lúc nào cũng lấy giá trị của ô đầu các đoạn ô. Như vậy, nếu công thức tại D7, thì sẽ thành HLOOKUP($B7,’T1’!$B$3:$BY$12,(D$6="nhớt")+{4;8},0).
  • Phần (D$6="nhớt")+{4;8} có ý nghĩa: nếu đang tại cột “PTNM” thì (D$6="nhớt") sẽ trả ra trị FALSE hay bằng 0, suy ra 0+{4;8}={4;8} tức truy xuất cùng 1 lúc cả hai giá trị có tại dòng 4 và dòng 8, tức Hlookup() sẽ truy tìm qua bảng ’T1’!$B$3:$BY$12, lấy giá trị tại cột có Mã CH là “bm” với 2 dòng cách tương xứng so với hàng 3 là: 4 và 8 (số dòng 4+3-1=6 và 8+3-1=10), tức 2 hàng chứa thông tin doanh thu: bán Lẻ và Sỉ của “Phụ tùng nhà máy”.
  • Nếu đang tại cột “NHỚT” thì (D$6="nhớt") sẽ trả ra trị TRUE hay bằng 1, suy ra 1+{4;8}={5;9}, tức Hlookup() sẽ lấy ra giá trị tại 2 dòng cách tương xứng: 5+3-1=7 và dòng 9+3-1=11, tức 2 hàng chứa thông tin doanh thu: bán Lẻ và Sỉ của “Nhớt”
• Cuối cùng dùng Sumproduct( ‘kết quả của Hlookup() trả về’ ) cộng hai kết quả bán sỉ và lẻ.

Chúc bạn học tập vui với anh em GPE.
hihi ^o^

 

[hoasenhong2017]

Bạn @Mộng Thường 2001 cho mình hỏi "zzz" là tên sheet hả bạn, hàm lookup thì mình cũng chưa học mặc dù mình cũng có đọc nhiều lần rồi nên không hiểu lắm.
Thật lòng cả ơn bạn rất rất nhiều luôn, bạn không những rất thông minh tài giỏi mà còn tốt bụng, hay giúp đỡ người khác. Bạn đã dành thời gian để giải thích rất chi tiết cho mình, mình chỉ hiểu được sơ sơ cũng có chỗ không hiểu, nếu cho mình tự làm hoặc áp dụng vào bảng khác thì mình lại không biết làm, mình cũng ngại lắm khi hỏi các bạn nhiều, mong các bạn đừng chê bai mình nhé.

Sheet SL NHOT.:

Bạn có thể giải thích giúp mình công thức này luôn được không, cảm ơn bạn rất nhiều.

 

[Mộng Thường 2001]

Bạn @Mộng Thường 2001 cho mình hỏi "zzz" là tên sheet hả bạn, hàm lookup thì mình cũng chưa học mặc dù mình cũng có đọc nhiều lần rồi nên không hiểu lắm.
Thật lòng cả ơn bạn rất rất nhiều luôn, bạn không những rất thông minh tài giỏi mà còn tốt bụng, hay giúp đỡ người khác. Bạn đã dành thời gian để giải thích rất chi tiết cho mình, mình chỉ hiểu được sơ sơ cũng có chỗ không hiểu, nếu cho mình tự làm hoặc áp dụng vào bảng khác thì mình lại không biết làm, mình cũng ngại lắm khi hỏi các bạn nhiều, mong các bạn đừng chê bai mình nhé.
Bạn có thể giải thích giúp mình công thức này luôn được không, cảm ơn bạn rất nhiều.

Tôi trình độ cũng như bạn thôi! đừng tự ti mặc cảm. Tôi cũng nhờ diễn đàn GPE này nên có chút xíu thành quả, do lợi thế hơn là tôi vào trước bạn, và chăm lục lọi diễn đàn, học những cái hay của các anh em tài giỏi khác.
Trong tương lai, nếu bạn chịu khó có thể vượt trội hơn cả.

Về hàm Lookup("zzz",Vùng so và lấy giá trị): "zzz" là chuỗi ký tự lớn hơn các ký tự alphabet có thể có, ví dụ: bạn lấy A1="zzz"<"T1", nó trả về trị FALSE, nếu đổi dấu A1="zzz">"T1", nó trả về trị TRUE. Về công dụng như tôi đã đề cập tại bài #11, nó sẽ lấy giá trị chuỗi "nhỏ hơn gần bằng" với chuỗi "zzz" nhất thì trả về giá trị đó. (Xem lại giải thích bài #11)

Tương tự, nếu Vùng so và lấy giá trị là số, thì bạn dùng LOOKUP(10^10,Vùng so và lấy giá trị), cũng cùng chức năng dùng lấy giá trị cho các trường hợp merge cell (hay giá trị ô cách khoảng).
Ví dụ: A1=1, A2="", A3="", A4=2, A5=""

• Giả sử công thức tại B3=LOOKUP(10^10,A$1:A3) thì nó trả về giá trị 1
• Giả sử công thức tại B5=LOOKUP(10^10,A$1:A5) thì nó trả về giá trị 2

Công thức Sheet SL NHOT. tôi sẽ giải thích sau, nhưng giờ thì chưa sắp xếp được nha! bạn thông cảm.
hihi ^o^

 

[hoasenhong2017]

Công thức Sheet SL NHOT. tôi sẽ giải thích sau, nhưng giờ thì chưa sắp xếp được nha! bạn thông cảm

Cảm ơn bạn, khi nào bạn có thời gian bạn giải thích giúp mình nhé. Vì chỉ có bạn mới chịu giải thích giúp người như mình thôi. Mình trình độ có hạn nên các công thức gộp hoặc khó xíu là mù tịt luôn, mặc dù mình cũng chăm đọc chăm tìm hiểu mà não mình ngắn quá bạn ơi.