xin anh chị giúp em công thức tính tổng năm theo điều kiện ạ (2 người xem)

[Lan Ny]

em có một file excel như sau. trong file này chia ra làm 12 tháng, (tính từ tháng 4 tới tháng 3). một tháng trong đó có nhiều cột số liệu (tạm thời em ẩn các cột không liên quan đi vì không dùng đến trong thắc mắc này của em, chứ em không xóa cột luôn được vì còn dùng tới ạ) có hai cột em cần dùng để tính toán, là cột "năm ngoái" và cột "năm nay"
công thức tổng cho cả năm thì chỉ đơn giản là cộng 12 tháng lại thôi.
cái em cần anh chị giúp đỡ là công thức TÍNH TỔNG VÒNG MỘT NĂM GẦN NHẤT (cột em tô màu vàng, cột MG) theo kiểu sau thì lập như thế nào (em không biết dùng từ gì để diễn tả cho đúng). cụ thể, ví dụ bây giờ là em muốn tổng kết báo cáo cho tháng 6 đi, thì tổng vòng sẽ được tính là:
các tháng từ tháng 4 đến tháng tổng kết (ở đây là tháng 6) thì cộng số liệu từ cột "năm nay"
cộng với
các tháng từ tháng tiếp theo của tháng tổng kết (tháng 6) đến hết tháng 3 thì cộng số liệu từ cột "năm ngoái"

tức, khi em muốn làm báo cáo cho tháng 6, thì TỔNG VÒNG MỘT NĂM GẦN NHẤT sẽ là tổng của "tháng 4 năm nay + tháng 5 năm nay + tháng 6 năm nay"
cộng với "tháng 7 năm ngoái + tháng 8 năm ngoái + tháng 9 năm ngoái+.... + tháng 12 năm ngoái + tháng 1 năm ngoái ＋tháng 2 năm ngoái + tháng 3 năm ngoái"

tương tự, nếu tổng kết báo cáo cho tháng sau đi, tức là tháng 7, thì tổng vòng sẽ được tính là: tổng số liệu các cột "năm nay" của tháng 4 tới tháng 7 + tổng dữ liệu các cột "năm ngoái" của các tháng còn lại (tức tháng 8 tới tháng 3)

cứ vậy, khi cần báo cáo tổng kết của tháng nào, thì tổng vòng theo năm sẽ lấy được kết quả theo ý mình muốn.

ở file, em đã hỏi tới chat gpt và tự thử làm nhưng không thành công. cụ thể em làm như sau:
- em lập một bảng phụ, (cột NC, ND, NE)
- ở ô F6 e cho 12 lựa chọn tương tương với 12 tháng
- cột MG em đã làm thử công thức nhưng ra kết quả lại sai. (mà khi em chọn các tháng khác từ ô F3, thì công thức bị lỗi hết)
em thử tính nháp thủ công tại cột ML, kết quả ở cột đây mới là đúng.
các anh chị có thể giúp em công thức tính tổng vòng năm ở các ô màu vàng giúp em mà không dúng VBA được không ạ? kiểu muốn tính theo tháng nào, em chỉ việc chọn tháng đó, dựa theo tháng em chọn, tổng vòng cũng sẽ thay đổi theo tương ứng.
em trình bày có chỗ nào khó hiểu, các anh chị cứ chỉ em, em sẽ giải thích thêm ạ.
em cám ơn anh chị nhiều

 

[ngocbaovu]

Công thức của bạn sai vì không phân biệt được giới hạn tính từ tháng 4. Bạn tham khảo công thức trong file và xem kết quả đã đúng chưa? Ở đây ví dụ tính cho tháng 2 thì mình hiểu là tính tổng từ tháng 3 năm ngoái đến tháng 2 năm nay.

 

[Lan Ny]

Công thức của bạn sai vì không phân biệt được giới hạn tính từ tháng 4. Bạn tham khảo công thức trong file và xem kết quả đã đúng chưa? Ở đây ví dụ tính cho tháng 2 thì mình hiểu là tính tổng từ tháng 3 năm ngoái đến tháng 2 năm nay.

dạ em cảm ơn anh/chị rất nhiều ạ. với ba tháng là 1 2 3 (chọn tại ô F3) thì kết quả chưa khớp cho lắm ạ, ví dụ e tính thủ công cho tháng 2, thì chưa ra.

nếu được, anh/chị có thể cho em hỏi cách làm 2 cột NI NJ trong file anh/chị làm được không ạ? tại sao mà khi em chọn tháng ở ô F3, thì 2 cột này có thể thay nhau thay đổi hiển thị (cả kết quả lẫn công thức) như vậy hay vậy ạ? em thấy trên thanh công cụ chỗ hiển thị công thức, thì công thức nó màu mờ mờ, chứ không phải màu đen như bình thường cơ. anh/chị có phiền hướng dẫn em cách làm được không ạ? em xin cảm ơn nhiều lắm ạ.

 

[Phan Thế Hiệp]

em có một file excel như sau. trong file này chia ra làm 12 tháng, (tính từ tháng 4 tới tháng 3). một tháng trong đó có nhiều cột số liệu (tạm thời em ẩn các cột không liên quan đi vì không dùng đến trong thắc mắc này của em, chứ em không xóa cột luôn được vì còn dùng tới ạ) có hai cột em cần dùng để tính toán, là cột "năm ngoái" và cột "năm nay"
công thức tổng cho cả năm thì chỉ đơn giản là cộng 12 tháng lại thôi.
cái em cần anh chị giúp đỡ là công thức TÍNH TỔNG VÒNG MỘT NĂM GẦN NHẤT (cột em tô màu vàng, cột MG) theo kiểu sau thì lập như thế nào (em không biết dùng từ gì để diễn tả cho đúng). cụ thể, ví dụ bây giờ là em muốn tổng kết báo cáo cho tháng 6 đi, thì tổng vòng sẽ được tính là:
các tháng từ tháng 4 đến tháng tổng kết (ở đây là tháng 6) thì cộng số liệu từ cột "năm nay"
cộng với
các tháng từ tháng tiếp theo của tháng tổng kết (tháng 6) đến hết tháng 3 thì cộng số liệu từ cột "năm ngoái"

tức, khi em muốn làm báo cáo cho tháng 6, thì TỔNG VÒNG MỘT NĂM GẦN NHẤT sẽ là tổng của "tháng 4 năm nay + tháng 5 năm nay + tháng 6 năm nay"
cộng với "tháng 7 năm ngoái + tháng 8 năm ngoái + tháng 9 năm ngoái+.... + tháng 12 năm ngoái + tháng 1 năm ngoái ＋tháng 2 năm ngoái + tháng 3 năm ngoái"

tương tự, nếu tổng kết báo cáo cho tháng sau đi, tức là tháng 7, thì tổng vòng sẽ được tính là: tổng số liệu các cột "năm nay" của tháng 4 tới tháng 7 + tổng dữ liệu các cột "năm ngoái" của các tháng còn lại (tức tháng 8 tới tháng 3)

cứ vậy, khi cần báo cáo tổng kết của tháng nào, thì tổng vòng theo năm sẽ lấy được kết quả theo ý mình muốn.

ở file, em đã hỏi tới chat gpt và tự thử làm nhưng không thành công. cụ thể em làm như sau:
- em lập một bảng phụ, (cột NC, ND, NE)
- ở ô F6 e cho 12 lựa chọn tương tương với 12 tháng
- cột MG em đã làm thử công thức nhưng ra kết quả lại sai. (mà khi em chọn các tháng khác từ ô F3, thì công thức bị lỗi hết)
em thử tính nháp thủ công tại cột ML, kết quả ở cột đây mới là đúng.
các anh chị có thể giúp em công thức tính tổng vòng năm ở các ô màu vàng giúp em mà không dúng VBA được không ạ? kiểu muốn tính theo tháng nào, em chỉ việc chọn tháng đó, dựa theo tháng em chọn, tổng vòng cũng sẽ thay đổi theo tương ứng.
em trình bày có chỗ nào khó hiểu, các anh chị cứ chỉ em, em sẽ giải thích thêm ạ.
em cám ơn anh chị nhiều

Bạn đang sử dụng Excel phiên bản nào?
Có sử dụng được các hàm của phiên bản 365 không?

Thân

 

[ngoctinh87]

em có một file excel như sau. trong file này chia ra làm 12 tháng, (tính từ tháng 4 tới tháng 3). một tháng trong đó có nhiều cột số liệu (tạm thời em ẩn các cột không liên quan đi vì không dùng đến trong thắc mắc này của em, chứ em không xóa cột luôn được vì còn dùng tới ạ) có hai cột em cần dùng để tính toán, là cột "năm ngoái" và cột "năm nay"
công thức tổng cho cả năm thì chỉ đơn giản là cộng 12 tháng lại thôi.
cái em cần anh chị giúp đỡ là công thức TÍNH TỔNG VÒNG MỘT NĂM GẦN NHẤT (cột em tô màu vàng, cột MG) theo kiểu sau thì lập như thế nào (em không biết dùng từ gì để diễn tả cho đúng). cụ thể, ví dụ bây giờ là em muốn tổng kết báo cáo cho tháng 6 đi, thì tổng vòng sẽ được tính là:
các tháng từ tháng 4 đến tháng tổng kết (ở đây là tháng 6) thì cộng số liệu từ cột "năm nay"
cộng với
các tháng từ tháng tiếp theo của tháng tổng kết (tháng 6) đến hết tháng 3 thì cộng số liệu từ cột "năm ngoái"

tức, khi em muốn làm báo cáo cho tháng 6, thì TỔNG VÒNG MỘT NĂM GẦN NHẤT sẽ là tổng của "tháng 4 năm nay + tháng 5 năm nay + tháng 6 năm nay"
cộng với "tháng 7 năm ngoái + tháng 8 năm ngoái + tháng 9 năm ngoái+.... + tháng 12 năm ngoái + tháng 1 năm ngoái ＋tháng 2 năm ngoái + tháng 3 năm ngoái"

tương tự, nếu tổng kết báo cáo cho tháng sau đi, tức là tháng 7, thì tổng vòng sẽ được tính là: tổng số liệu các cột "năm nay" của tháng 4 tới tháng 7 + tổng dữ liệu các cột "năm ngoái" của các tháng còn lại (tức tháng 8 tới tháng 3)

cứ vậy, khi cần báo cáo tổng kết của tháng nào, thì tổng vòng theo năm sẽ lấy được kết quả theo ý mình muốn.

ở file, em đã hỏi tới chat gpt và tự thử làm nhưng không thành công. cụ thể em làm như sau:
- em lập một bảng phụ, (cột NC, ND, NE)
- ở ô F6 e cho 12 lựa chọn tương tương với 12 tháng
- cột MG em đã làm thử công thức nhưng ra kết quả lại sai. (mà khi em chọn các tháng khác từ ô F3, thì công thức bị lỗi hết)
em thử tính nháp thủ công tại cột ML, kết quả ở cột đây mới là đúng.
các anh chị có thể giúp em công thức tính tổng vòng năm ở các ô màu vàng giúp em mà không dúng VBA được không ạ? kiểu muốn tính theo tháng nào, em chỉ việc chọn tháng đó, dựa theo tháng em chọn, tổng vòng cũng sẽ thay đổi theo tương ứng.
em trình bày có chỗ nào khó hiểu, các anh chị cứ chỉ em, em sẽ giải thích thêm ạ.
em cám ơn anh chị nhiều

Bạn thử dùng công thức sau, có chỉnh lại cột NC

=SUM(N(INDIRECT(FILTER($NE$14:$NE$25&ROW(),$NC$14:$NC$25<=12*($F$3-3<=0)+$F$3-3))),N(INDIRECT(FILTER($ND$14:$ND$25&ROW(),$NC$14:$NC$25>12*($F$3-3<=0)+$F$3-3))))

 

[Phan Thế Hiệp]

cái em cần anh chị giúp đỡ là công thức TÍNH TỔNG VÒNG MỘT NĂM GẦN NHẤT (cột em tô màu vàng, cột MG)

Nhìn lại công thức của bạn thấy có dùng FILTER(...), nên tôi nghĩ là bạn có thể dùng các hàm 365.

Trước tiên: hủy Merge Cells của các tiêu đề "tháng 4, tháng 5..v.v", bạn sẽ thấy tại các cột "Năm nay" có chữ "5月", hãy xóa hết đi, Merge Cells như lúc đầu, thử công thức sau: ô MG8

=LET(Nnay,IFERROR(FIND("nay",$H$5:$JX$5),)>0,Nroi,IFERROR(FIND("ngoái",$H$5:$JX$5),)>0,Thg,IFERROR(FIND("tháng", SCAN(,$H$4:$JX$4,LAMBDA(x,y,IF(y>"",y,x)))),)=1,vong,MOD($F$3-4,12),SUM(TAKE(FILTER(H8:JX8,Nnay*Thg),,vong+1),IFERROR(TAKE(FILTER(H8:JX8, Nroi*Thg),,vong-11),)))

Fill xuống.

Không cần dùng bảng phụ đánh dấu các cột cần cộng tại bảng NC:NE

Xem file kèm

Thân

 

[VetMini]

Một người quản lý dữ liệu chuyên nghiệp sẽ không làm như bạn đang muốn làm.

Cách làm chính thức là dùng advanced filter để lọc dữ liệu trong khoảng thời gian cần. Xong rồi muốn tổng gì thì tổng. Mỗi lần cần đổi tháng thì làm lại.Các bạn chê là thủ công? Đúng là thủ công, nhưng ở đây tôi nói đến người quản lý chuyên nghiệp cực chẳng đã phải sử dụng bảng tính không được thiết kế chuẩn để phân tích dữ liệu.

Các phiên bản khá hơn của Excel (2016+) có những công cụ khá hơn, như Power Query để người ta chuẩn hóa bảng rồi dùng Pivot Table (Power Pivot) để tổng hợp và phân tích.

Phiên bản 365 và 2021 trở về sau dựa vào tính chất hổ trợ mảng/array rất uyển chuyển để lập bảng ảo (qua arrays) đưa dữ liệu về dạng chuẩn rồi tha hồ tổng hợp và phân tích.

* Nếu phải viết công thức cho bài này thì tôi sẽ viết trước một vài hàm Lambda (names) để tính vị trí cột-tháng. Công thức chính sẽ gọn và dễ hiểu hơn.

 

[HieuCD]

em có một file excel như sau. trong file này chia ra làm 12 tháng, (tính từ tháng 4 tới tháng 3). một tháng trong đó có nhiều cột số liệu (tạm thời em ẩn các cột không liên quan đi vì không dùng đến trong thắc mắc này của em, chứ em không xóa cột luôn được vì còn dùng tới ạ) có hai cột em cần dùng để tính toán, là cột "năm ngoái" và cột "năm nay"
công thức tổng cho cả năm thì chỉ đơn giản là cộng 12 tháng lại thôi.
cái em cần anh chị giúp đỡ là công thức TÍNH TỔNG VÒNG MỘT NĂM GẦN NHẤT (cột em tô màu vàng, cột MG) theo kiểu sau thì lập như thế nào (em không biết dùng từ gì để diễn tả cho đúng). cụ thể, ví dụ bây giờ là em muốn tổng kết báo cáo cho tháng 6 đi, thì tổng vòng sẽ được tính là:
các tháng từ tháng 4 đến tháng tổng kết (ở đây là tháng 6) thì cộng số liệu từ cột "năm nay"
cộng với
các tháng từ tháng tiếp theo của tháng tổng kết (tháng 6) đến hết tháng 3 thì cộng số liệu từ cột "năm ngoái"

tức, khi em muốn làm báo cáo cho tháng 6, thì TỔNG VÒNG MỘT NĂM GẦN NHẤT sẽ là tổng của "tháng 4 năm nay + tháng 5 năm nay + tháng 6 năm nay"
cộng với "tháng 7 năm ngoái + tháng 8 năm ngoái + tháng 9 năm ngoái+.... + tháng 12 năm ngoái + tháng 1 năm ngoái ＋tháng 2 năm ngoái + tháng 3 năm ngoái"

tương tự, nếu tổng kết báo cáo cho tháng sau đi, tức là tháng 7, thì tổng vòng sẽ được tính là: tổng số liệu các cột "năm nay" của tháng 4 tới tháng 7 + tổng dữ liệu các cột "năm ngoái" của các tháng còn lại (tức tháng 8 tới tháng 3)

cứ vậy, khi cần báo cáo tổng kết của tháng nào, thì tổng vòng theo năm sẽ lấy được kết quả theo ý mình muốn.

ở file, em đã hỏi tới chat gpt và tự thử làm nhưng không thành công. cụ thể em làm như sau:
- em lập một bảng phụ, (cột NC, ND, NE)
- ở ô F6 e cho 12 lựa chọn tương tương với 12 tháng
- cột MG em đã làm thử công thức nhưng ra kết quả lại sai. (mà khi em chọn các tháng khác từ ô F3, thì công thức bị lỗi hết)
em thử tính nháp thủ công tại cột ML, kết quả ở cột đây mới là đúng.
các anh chị có thể giúp em công thức tính tổng vòng năm ở các ô màu vàng giúp em mà không dúng VBA được không ạ? kiểu muốn tính theo tháng nào, em chỉ việc chọn tháng đó, dựa theo tháng em chọn, tổng vòng cũng sẽ thay đổi theo tương ứng.
em trình bày có chỗ nào khó hiểu, các anh chị cứ chỉ em, em sẽ giải thích thêm ạ.
em cám ơn anh chị nhiều

Góp vui công thức Excel bản cổ
MG8 =SUMPRODUCT(SUM(OFFSET(G8,,(TEXT($F$3-ROW($4:$15),"2;1;2")-1)*(6+{1;19;37;73;91;109;163;181;199;235;253;271})),OFFSET(G8,,(TEXT($F$3-ROW($4:$15),"1;2;1")-1)*{1;19;37;73;91;109;163;181;199;235;253;271})))

 

[Phan Thế Hiệp]

Góp vui công thức Excel bản cổ

Thói thường "đồ cổ" mới quý hiếm! Khà khà

Chúc anh ngày vui.

Bài đã được tự động gộp: Thứ bảy lúc 06:04

Nếu phải viết công thức cho bài này thì tôi sẽ viết trước một vài hàm Lambda (names) để tính vị trí cột-tháng. Công thức chính sẽ gọn và dễ hiểu hơn

Em xài excel trên xi-mát-phon anh ơi, nó hổng chịu cho định đặt "Names", nếu có em cũng đã chơi mấy thằng "nay", "ngoái" như anh chỉ bảo. Vừa gọn vừa lẹ. Hì hì

Chúc anh ngày vui

 

[HieuCD]

Theo gợi ý của bạn Phan Thế Hiệp, chỉnh lại công thức để xét trường hợp tháng hiện hành <4
MG8 = SUMPRODUCT(SUM(OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),"2;1;2")-1)*(6+{1;19;37;73;91;109;163;181;199;235;253;271})),OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),"1;2;1")-1)*{1;19;37;73;91;109;163;181;199;235;253;271})))

 

[Lan Ny]

Bạn đang sử dụng Excel phiên bản nào?
Có sử dụng được các hàm của phiên bản 365 không?

Thân

dạ cho em xin lỗi vì hôm nay em mới trả lời anh được. em dùng 365 được ạ

Bạn thử dùng công thức sau, có chỉnh lại cột NC

=SUM(N(INDIRECT(FILTER($NE$14:$NE$25&ROW(),$NC$14:$NC$25<=12*($F$3-3<=0)+$F$3-3))),N(INDIRECT(FILTER($ND$14:$ND$25&ROW(),$NC$14:$NC$25>12*($F$3-3<=0)+$F$3-3))))

dạ em cảm ơn anh/chị nhiều ạ. em thử thì thấy đúng cho tất cả các tháng luôn. mấy cái chỗ -3 trong công thức của anh/chị hay quá ạ. em có xem kĩ thì thấy tháng 3 chưa ra được kết quả. em sẽ xem lại xem em bị lỗi như vầy ấy ạ. không biết em bị nhầm chỗ nào, em đang xem lại chỗ này ạ.
em xin cảm ơn anh/chị đã giúp đỡ em.

Một người quản lý dữ liệu chuyên nghiệp sẽ không làm như bạn đang muốn làm.

Cách làm chính thức là dùng advanced filter để lọc dữ liệu trong khoảng thời gian cần. Xong rồi muốn tổng gì thì tổng. Mỗi lần cần đổi tháng thì làm lại.Các bạn chê là thủ công? Đúng là thủ công, nhưng ở đây tôi nói đến người quản lý chuyên nghiệp cực chẳng đã phải sử dụng bảng tính không được thiết kế chuẩn để phân tích dữ liệu.

Các phiên bản khá hơn của Excel (2016+) có những công cụ khá hơn, như Power Query để người ta chuẩn hóa bảng rồi dùng Pivot Table (Power Pivot) để tổng hợp và phân tích.

Phiên bản 365 và 2021 trở về sau dựa vào tính chất hổ trợ mảng/array rất uyển chuyển để lập bảng ảo (qua arrays) đưa dữ liệu về dạng chuẩn rồi tha hồ tổng hợp và phân tích.

* Nếu phải viết công thức cho bài này thì tôi sẽ viết trước một vài hàm Lambda (names) để tính vị trí cột-tháng. Công thức chính sẽ gọn và dễ hiểu hơn.

dạ em cảm ơn góp ý của anh ạ. em thì chỉ biết excel căn bản thôi, nên cách giải quyết các bài toán vẫn còn kém. em sẽ tìm hiểu thêm về names, cũng như các công thức nãy giờ các anh chị hướng dẫn cho em ạ. em cảm ơn anh nhiều ạ

Nhìn lại công thức của bạn thấy có dùng FILTER(...), nên tôi nghĩ là bạn có thể dùng các hàm 365.

Trước tiên: hủy Merge Cells của các tiêu đề "tháng 4, tháng 5..v.v", bạn sẽ thấy tại các cột "Năm nay" có chữ "5月", hãy xóa hết đi, Merge Cells như lúc đầu, thử công thức sau: ô MG8

=LET(Nnay,IFERROR(FIND("nay",$H$5:$JX$5),)>0,Nroi,IFERROR(FIND("ngoái",$H$5:$JX$5),)>0,Thg,IFERROR(FIND("tháng", SCAN(,$H$4:$JX$4,LAMBDA(x,y,IF(y>"",y,x)))),)=1,vong,MOD($F$3-4,12),SUM(TAKE(FILTER(H8:JX8,Nnay*Thg),,vong+1),IFERROR(TAKE(FILTER(H8:JX8, Nroi*Thg),,vong-11),)))

Fill xuống.

Không cần dùng bảng phụ đánh dấu các cột cần cộng tại bảng NC:NE

Xem file kèm

Thân

dạ em cảm ơn anh nhiều. em Merge Cells lại lúc đầu như anh bảo, xong thử thì công thức chạy được hết tất cả các tháng kể cả tháng 1 2 3 luôn rồi ạ. em sẽ nghiên cứu kỹ hơn để nắm được công thức của anh. thật lòng cảm ơn anh đã giúp em ạ.

Theo gợi ý của bạn Phan Thế Hiệp, chỉnh lại công thức để xét trường hợp tháng hiện hành <4
MG8 = SUMPRODUCT(SUM(OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),"2;1;2")-1)*(6+{1;19;37;73;91;109;163;181;199;235;253;271})),OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),"1;2;1")-1)*{1;19;37;73;91;109;163;181;199;235;253;271})))

đúng là công thức lúc đầu anh hướng dẫn em thì tháng 1 2 3 chưa chạy, nhưng công thức này thì chạy trơn tru hết luôn ạ.
em cảm ơn anh nhiều ạ
anh cho em hỏi thêm một chút được không ạ? em có tra google rồi nhưng em vẫn chưa hiểu lắm ạ, vì em còn kém excel lắm. các con số 1;19;37;73;91;109;163;181;199;235;253;271 trong công thức là mình tính như thế nào để ra được các số đó vậy ạ? và, giả sử nếu em thêm/bớt cột/hàng trong bảng, thì có áp dụng được công thức này được nữa không ạ?

 

[Phan Thế Hiệp]

anh cho em hỏi thêm một chút được không ạ? em có tra google rồi nhưng em vẫn chưa hiểu lắm ạ, vì em còn kém excel lắm. các con số 1;19;37;73;91;109;163;181;199;235;253;271

Nhìn cách hành văn trả lời của bạn rất chân thật, và có chí tìm hiểu những gì mình cần phải học, tôi có chút thiện cảm, nên tôi mạn phép ông anh @HieuCD của tôi trả lời thắc mắc của bạn, vì tính cách của ảnh rất khiêm cung, và kiệm lời lắm! Tôi thì ngược lại, "chuyện" hơi bị "nhiều" nên tôi chắc là ảnh sẽ đồng ý việc tôi làm này:

1/ Tại ô H3 bạn gõ số 1, ô I3 bạn gõ số 2, xong bạn chọn cả 2 ô đó rồi Fill qua phải đến ô JX3, bạn sẽ thấy các con số: 1;19;37;73;91;109;163;181;199;235;253;271 luôn nằm trên cột "Năm ngoái". Thay vì bạn tạo các cột như bảng NC:NE rồi dùng INDIRECT() để xác định các cột cần tìm, công thức của anh @HieuCD dùng Offset(), và dùng hệ thống số trên để lệnh cho nó tìm đúng cột cần tìm.

2/ Sau khi phân tích bảng của bạn, anh ấy thấy rằng từ cột Năm ngoái cách cột Năm nay là 6 cột, và khoảng cách này luôn cố định cho các tháng, vì vậy trong mệnh đề công thức có số 6+{1;29;37....}, tức tìm đến các cột Năm nay.

Rồi đó, còn thắc mắc gì cứ mạnh dạn nêu, chứ đôi khi tra "gô gồ" nó hổng hiểu ông anh của tui bằng tui đâu!

Khà khà khà

Chúc bạn học vui

 

[Lan Ny]

Nhìn cách hành văn trả lời của bạn rất chân thật, và có chí tìm hiểu những gì mình cần phải học, tôi có chút thiện cảm, nên tôi mạn phép ông anh @HieuCD của tôi trả lời thắc mắc của bạn, vì tính cách của ảnh rất khiêm cung, và kiệm lời lắm! Tôi thì ngược lại, "chuyện" hơi bị "nhiều" nên tôi chắc là ảnh sẽ đồng ý việc tôi làm này:

1/ Tại ô H3 bạn gõ số 1, ô I3 bạn gõ số 2, xong bạn chọn cả 2 ô đó rồi Fill qua phải đến ô JX3, bạn sẽ thấy các con số: 1;19;37;73;91;109;163;181;199;235;253;271 luôn nằm trên cột "Năm ngoái". Thay vì bạn tạo các cột như bảng NC:NE rồi dùng INDIRECT() để xác định các cột cần tìm, công thức của anh @HieuCD dùng Offset(), và dùng hệ thống số trên để lệnh cho nó tìm đúng cột cần tìm.

2/ Sau khi phân tích bảng của bạn, anh ấy thấy rằng từ cột Năm ngoái cách cột Năm nay là 6 cột, và khoảng cách này luôn cố định cho các tháng, vì vậy trong mệnh đề công thức có số 6+{1;29;37....}, tức tìm đến các cột Năm nay.

Rồi đó, còn thắc mắc gì cứ mạnh dạn nêu, chứ đôi khi tra "gô gồ" nó hổng hiểu ông anh của tui bằng tui đâu!

Khà khà khà

Chúc bạn học vui

aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa, dạ em hiểu ra được cách làm rồi anh ơi. em cảm ơn anh nhiều lắm.
em Fill xong thì đã hiểu ý nghĩa của dãy số đó rồi ạ. anh giải thích chỗ +6 cho ra cột năm nay thì em cũng hiểu luôn. thì ra có cách dò tìm theo vị trí cột như vậy. bây giờ em mới biết luôn ấy ạ.
em cảm ơn anh đã giải thích tận tình cho em hiểu.

chắc chắn là vẫn còn rất nhiều điều em chưa hiểu, chưa biết, tới lúc đó em lại phiền anh và các anh chị tiếp tục hướng dẫn, chỉ bảo em nữa được không ạ?
còn trong bài này của em thì em đã hiểu ra được rồi ạ.

em cảm ơn anh, cảm ơn tất cả các anh chị đã bỏ thời gian cũng như công sức ra giúp đỡ, hướng dẫn em.
em xin cảm ơn các anh chị rất nhiều ạ.

 

[Phan Thế Hiệp]

chắc chắn là vẫn còn rất nhiều điều em chưa hiểu, chưa biết, tới lúc đó em lại phiền anh và các anh chị tiếp tục hướng dẫn, chỉ bảo em nữa được không ạ?

Tôi luôn ủng hộ những bạn có chí cầu tiến ham học hỏi, cứ mạnh dạn thắc mắc, và hỏi có trọng tâm, và thực hành lại những gì người khác hướng dẫn như bạn đã làm ở trên, để mình hiểu rõ hơn.

Nhân đây, công thức anh @HieuCD khi ghi cho bạn thì tách 2 OFFSET() cho bạn dễ hiểu, chứ khi chỉ cho tụi tui thì ảnh làm gọn công thức trên như vầy:

=SUM(OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),{2,1}&";"&{1,2}&";"&{2,1})-1)*({6,0}+{1;19;37;73;91;109;163;181;199;235;253;271})))

Học, học nữa, học mãi
Cố gắng nha bạn

 

[Lan Ny]

Tôi luôn ủng hộ những bạn có chí cầu tiến ham học hỏi, cứ mạnh dạn thắc mắc, và hỏi có trọng tâm, và thực hành lại những gì người khác hướng dẫn như bạn đã làm ở trên, để mình hiểu rõ hơn.

Nhân đây, công thức anh @HieuCD khi ghi cho bạn thì tách 2 OFFSET() cho bạn dễ hiểu, chứ khi chỉ cho tụi tui thì ảnh làm gọn công thức trên như vầy:

=SUM(OFFSET(G8,,(TEXT($F$3+($F$3<4)*12-ROW($4:$15),{2,1}&";"&{1,2}&";"&{2,1})-1)*({6,0}+{1;19;37;73;91;109;163;181;199;235;253;271})))

Học, học nữa, học mãi
Cố gắng nha bạn

dạ em cảm ơn anh nhiều ạ. em xin lỗi vì phản hồi muộn ạ.
sáng giờ em ngồi ngâm cứu để hiểu được phần nào công thức rút gọn này, với người chỉ biết excel căn bản như em thì đúng là nâng cao thật anh ạ

em hiểu vì sao ban đầu anh hỏi em có dùng excel 365 không rồi, vì các mảng định dạng trong công thức hình như là chỉ chạy được trong excel phiên bản 365 thôi đúng không ạ?
ví dụ như với công thức trên, thì mấy chỗ
TEXT(... ... ...mảng chuỗi định dạng)
OFFSET(... ... ...mảng tham số)
... ... ...
excel phiên bản cũ không xử lý được các mảng nhiều giá trị như vầy, mà chỉ xử lý giá trị đơn được thôi. cũng không cần phải ctrl + shift + enter nữa


không biết em hiểu như vậy có đúng không ạ?
thật sự là cảm ơn anh, anh HieuCD cũng như các anh chị rất là nhiều ạ. em hỏi có một câu mà học được quá trời luôn.
em sẽ còn phải lên đây nhờ các anh chị chỉ bảo nhiều lần nữa, mong được anh và mọi người giúp đỡ em ạ.
em xin cảm ơn ạ

 

[Phan Thế Hiệp]

em hiểu vì sao ban đầu anh hỏi em có dùng excel 365 không rồi, vì các mảng định dạng trong công thức hình như là chỉ chạy được trong excel phiên bản 365 thôi đúng không ạ?

Không phải vậy.

Công thức của anh @HieuCD viết ra dùng cho các phiên bản nào cũng được (cứ cho là từ phiên bản 2010 đến các phiên bản gần đây). Công thức này sử dụng mảng để tính toán: thông qua hàm Offset() tạo mảng 2 cột, 12 hàng gồm các giá trị Năm nay và Năm ngoái, dùng Text(Mod(),"2;1;2")-1 và Text(Mod(,)"1;2;1")-1 tạo ra giá trị 1 hoặc 0 để cho ẩn/hiện các giá trị cần tìm phù hợp với tháng hiện tại F3 cùng điều kiện bạn đưa kèm theo. Cái hay của nó là tính toán trên 1 tổng thể của mảng, nhưng cái khó là người sử dụng phải hiểu và nhìn thấu mảng hoạt động ra sao mới quản lý nó được.

Còn tôi hỏi bạn dùng được excel 365 không, vì công thức của tôi dùng nhiều hàm của 365 để xử lý. Cái tiện ích của nó là nó nhiều "công cụ" hơn và có sẵn, để tôi có thể "xào trộn nấu nướng", "ngắc véo thêm bớt" làm ra kết quả đưa cho bạn. Nhờ đó bạn không cần phải dùng cột phụ hay hệ thống số định vị trí mà vẫn tìm đúng ô cần tìm. Ngoài ra nếu bạn chèn thêm 1 hoặc nhiều cột giữa Năm nay và Năm ngoái thì cũng không ảnh hưởng đến kết quả. Cái kẹt của nó là nếu bạn dùng phiên bản dưới 365 thì công thức của tôi vô dụng, còn công thức của anh @HieuCD thì "vô tư"!

Thân