Phân các Vùng để Cộng hoặc Tính toán trị giá theo dòng

Chia sẻ bởi:hands

★★★★★

‹ Cú pháp thể hiện khi dùng MMULT Hàm tìm tất cả các giá trị... ›

Quảng cáo

Chào các bác. Em cần trợ giúp tạo một công thức Tại I7 của Sheet3 (sheet và vùng tô vàng) như file kèm theo. Công thức này có thể kéo xuống I123 và cột K7-K123 sao cho thực hiện được các phép tính có giá trị như file hiện hữu. Có thể tận dụng và tham khảo tại ô J6 của Sheet1 (theo cảm nhận của em) nhưng tại em còn yếu nên chưa áp dụng nó được 🙁 . Mong nhận được sự quan tâm của các cao thủ. Xin cảm ơn!

Sao file của bạn có cả tá vòng lặp vậy, có tác dụng gì không hay bị nhầm lẫn "nho nhỏ"?

Vòng lặp theo ý của bác là sao nhỉ… File này các sheet có sự liên kết liên hệ với nhau và hầu như làm bằng link thủ công. Riêng 2 sheet 1 và sheet 3 có sử dụng công thức của các bác trên diễn đàn GPE này. Tại sheet3 như công thức cần trợ giúp I7 thì em nghỉ nó chỉ ở phạm vi Sheet3 thôi. Bác có thể giúp lập 1 công thức chung theo cách tính hiện tại ở vùng em tô vàng không?

Vòng lặp đại loại nó như thế này: A3=A1+A2+A3, lấy nó tính luôn cho chính nó, người ta chỉ sử dụng vòng lặp cho 1 vài trường hợp cần thiết thôi. Hiện tại máy mình chưa có form chữ hỗ trợ file của bạn nên mình chưa giúp được gì, đang chuyển form chữ 🙂

Có cách nào khác nửa để tránh lặp cho chính nó không, lập cột phụ gì đó chẳng hạn. Máy của bác thiếu font hay là định dạng ở Excel cao để em chuyển xuống… File của em sử dụng font ABC bác có thể cài thêm hoặc lấy font sau cài thêm. Mong được sự trợ giúp từ bác và mọi người.
Định dạng file ở Excel 97-2003. Nếu thiếu font ABC bác có thể cài thêm.

Tôi có nhận được tin nhắn hồ sơ từ bạn, nay gửi bạn giải pháp sau:

Cột phụ 1: bạn tự gõ vào theo quy tắc: Mục tổng ('Mẹ') (A, B, C….) lấy số từ: 10, đến 11, 12, 13 …. Các Mục 'Con': Lấy số của 'Mẹ' ghép thứ tự phát sinh của nó, như: 10.01, 10.02, 10.03…. Tương tự như vậy cho 'Cháu', như: 10.04.01, 10.04.02, 10.04.03….. Bạn chỉ cần xác định 'Mẹ; Con; hay Cháu' tại các đề mục cần cộng (xem cột L)

Cột phụ 2: dùng công thức:

M7=IF(L7<>"",L7,LOOKUP("zzz",$L$7:L7)&"."&TEXT(COUNTIF($M6:M$7,LOOKUP("zzz",$L$7:L7)&"*"),"00"))

fill xuống.

Công thức cho các cột I:J:K như sau:
```
I7=IFERROR(1/(1/($E7*F7)),SUMIF($M8:$M$500,$M7&".??",I8:I$500))
```
copy qua phải, rồi fill cả 3 cột xuống dưới.

Xem file kèm.
Thân!

Bác tài thật. Cách xử lý và công thức của bác cho kết quả chính xác. Em sẽ học hỏi và áp dụng. Xin cảm ơn và chúc bác sức khỏe!❤️

Vậy, để tôi điều chỉnh luôn Sheet1 theo hướng dùng: bộ mã phân "Mẹ-Con" và Sumif(…, ".??" , ….) dùng để cộng phân Vùng cho bạn tiện tham khảo và áp dụng đồng nhất luôn nha! 🙂

Mã STT phân "Mẹ – Con":

A6=IF($B6<>"",TRIM(COUNTIF($B$6:B6,"<>")), LEFT($A5&".",FIND("|",SUBSTITUTE($A5&".",".","|",2-($E6="")))-1)&"."&TEXT(COUNTIF($A$5:$A5,LEFT($A5&".",FIND("|",SUBSTITUTE($A5&".",".","|",2-($E6="")))-1)&".??")+1,"00"))

Fill xuống. Nên nhớ bộ mã này là "cốt lõi" quan trọng, dành để phân biệt Vùng bạn muốn Cộng Vùng, hay tính toán Trị giá dòng.

Cộng phân Vùng:

J6=IFERROR(1/(1/IF(OR($D6={"M¸y kh¸c";"VËt liÖu kh¸c"}),SUM(OFFSET($J$5,MAX(INDEX(($C5:$C$6="")*(ROW($C5:$C$6)-5),))+1,):$J5)*$G6%,PRODUCT($G6,$H6,$I6))),SUMIF($A7:$A$500,$A6&".??",J7:J$500))

Fill xuống.

Nó gọn hơn công thức trước, bạn xem thử file đính kèm (Sheet GPE), và kiểm tra ra sao nha!

Thân!

Hì :). Bác nhiệt tình quá. Công thức của bác có gọn hơn trước vì em nghĩ do bác có cách giải quyết vấn đề khác hơn. Quan trọng là kết quả cũng chính xác nửa. 🙂 Trong quá trình xem Sheet GPE của bác, có phát hiện ra rằng sheet này bị ẩn toàn bộ các cột từ sau cột K. Thấy nó hay hay, bác có thể chỉ cách thực hiện như thế nào, chắc hàng cũng tương tự luôn nhỉ…

Muốn ẩn:

Cột: Chọn các cột muốn ẩn, Vd: đang tại ô H1, nhấn Ctrl+Space Bar, xong nhấn Ctrl+Shift+Mũi tên phải, lúc này đã chọn từ cột H đến cột cuối bảng, nhấn Ctrl+0 (số không có dấu ")" ở trên nó). Muốn mở ra lại, nhấn chọn góc trái bảng (phía góc giao của hàng và cột: tức trên số 1 và bên trái chữ A): tức chọn toàn bộ bảng, xong nhấn nút phải chuột lên bất kỳ ký tự tiêu đề cột A, B, C…. nào, chọn Unhide.
Hàng: Sau khi chọn: Shift+Space bar, nhấn Ctrl+9 (số 9 có dấu "(" ở trên nó). Muốn mở chọn toàn bộ bảng và click vào tiêu đề dòng bất kỳ chọn 'Unhide'.

Mỗi công thức của anh em trên đều có cái hay riêng trong cách xử lý, do vậy tùy theo sở thích của bạn mà tùy nghi sử dụng thuận tiện.

Thân!

🙁 @@ Thấy vào EDIT chỉ sửa được nội dung, làm sao sửa được tiêu đề "Xin trợ giúp công thức"thành"Phân các Vùng để Cộng hoặc Tính toán trị giá theo dòng" bác nhỉ?

Tôi vừa kiểm tra lại cho bạn thấy:

Thay vì dùng PRODUCT($G6,$H6,$I6) bạn nên dùng: PRODUCT($G6*$H6,$I6), nếu không sửa nó sẽ sai tại đề mục thứ 35…..
Nếu sử dụng cách tính phân vùng như bài 10 thì tại Sheet3, các công thức tại các cột F,H,G phải chỉnh theo. Tôi đã kiểm tra toàn bộ lại các công thức, cái nào rút gọn được thì cũng chỉnh lại luôn.

Bạn xem file kèm.

Thân!