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

[Phan Thế Hiệp]

Mò mò một lúc mới rút gọn được công thức
Bạn quá lợi hại (quá lợi và hại quá) nhìn sơ là biết dùng công thức mảng như thế nào cho gọn
Chúc bạn một ngày vui

=1.06*SUM(N(OFFSET($E$4,MATCH(H14,$C$5:$C$10,0),{1,2,3}))*{1.015,1.665,1.015})

Hoặc thêm cách Vlookup() cũng ổn: (lấy những cái hay của bài #595 và #600)

=1.06*SUMPRODUCT(VLOOKUP(H14,$C$5:$H$10,{4,5,6},)*(1.015+{0,0.65,0}))

Chúc anh ngày vui.

 

[HieuCD]

Hoặc thêm cách Vlookup() cũng ổn: (lấy những cái hay của bài #595 và #600)

=1.06*SUMPRODUCT(VLOOKUP(H14,$C$5:$H$10,{4,5,6},)*(1.015+{0,0.65,0}))

Chúc anh ngày vui.

Hàm Vlookup càng lợi hại hơn, không cần hàm N(), công thức mảng biến hóa ảo ảo diệu diệu

 

[amateurboy]

Chào all,

Mình làm lương thì phải tính TỔNG SỐ NGÀY LÀM TRONG THÁNG (gọi là A) [cột màu đỏ].
vì Ngày làm trong tháng có khi là CHỈ làm bình thường, có khi là CHỈ làm tăng ca (ví dụ như ngày 24 màu vàng trong hình dưới)
Hiện mình đang dùng phần màu cam SUMIFS để phụ trợ cho công thức ở màu đỏ COUNTIF
Chi tiết trong file đính kèm.


Vậy có cách nào gom vào chỉ tạo 1 công thức cho phần màu đỏ được ko?

Mong nhận ý kiến.

 

[Phan Thế Hiệp]

Chào all,

Mình làm lương thì phải tính TỔNG SỐ NGÀY LÀM TRONG THÁNG (gọi là A) [cột màu đỏ].
vì Ngày làm trong tháng có khi là CHỈ làm bình thường, có khi là CHỈ làm tăng ca (ví dụ như ngày 24 màu vàng trong hình dưới)
Hiện mình đang dùng phần màu cam SUMIFS để phụ trợ cho công thức ở màu đỏ COUNTIF
Chi tiết trong file đính kèm.
View attachment 213319

Vậy có cách nào gom vào chỉ tạo 1 công thức cho phần màu đỏ được ko?

Mong nhận ý kiến.

Thử:

BL5=SUMPRODUCT(N(($J$4:$BF$4="BT")*(J5:BF5>0)+($K$4:$BG$4="TG")*(K5:BG5>0)>0))
Hoặc
BL5=SUMPRODUCT(N(COUNTIF(OFFSET(J5,,(ROW($1:$25)-1)*2,,2),">0")>0))

Enter, fill xuống.

Thân

 

[HieuCD]

Thử:

BL5=SUMPRODUCT(N(($J$4:$BF$4="BT")*(J5:BF5>0)+($K$4:$BG$4="TG")*(K5:BG5>0)>0))
Hoặc
BL5=SUMPRODUCT(N(COUNTIF(OFFSET(J5,,(ROW($1:$25)-1)*2,,2),">0")>0))

Enter, fill xuống.

Thân

Công thức dài quá chỉ cần Sumproduct. Vọt lẹ

 

[dazkangel]

Công thức dài quá chỉ cần Sumproduct. Vọt lẹ

Vậy được không anh, Ctrl+Shift+Enter

=COUNT(1/MATCH(AL$3:BG$3,AL$2:BG$2/(AL5:BG5>0),0))

 

[HieuCD]

Vậy được không anh, Ctrl+Shift+Enter

=COUNT(1/MATCH(AL$3:BG$3,AL$2:BG$2/(AL5:BG5>0),0))

Công thức rất sáng tạo , nhưng vẫn còn dài và phải ctrl+shift+enter
Nghỉ theo hướng xét diều kiện đơn giản sẽ rất ... rất đơn giản

 

[batman1]

Mình làm lương thì phải tính TỔNG SỐ NGÀY LÀM TRONG THÁNG (gọi là A) [cột màu đỏ].

Thử tí. Không nghĩ nhiều, không có bảo hành

=SUMPRODUCT((AL5:BF5+AM5:BG5>0)*(AL$3:BF$3>0))

hoặc

=SUM(INDEX((AL5:BF5+AM5:BG5>0)*(AL$3:BF$3>0),0))

 

[dazkangel]

Công thức rất sáng tạo , nhưng vẫn còn dài và phải ctrl+shift+enter
Nghỉ theo hướng xét diều kiện đơn giản sẽ rất ... rất đơn giản

Theo bác batman vẫn thích count
=count(1/al$3:bf$3*al$5:bf$5+am$5:bg$5)

 

[HieuCD]

Theo bác batman vẫn thích count
=count(1/al$3:bf$3*al$5:bf$5+am$5:bg$5)

Thử trên máy yên tâm hơn

 

[dazkangel]

Thử trên máy yên tâm hơn

Híc không ngồi máy sai tè le:

=COUNT(1/(AL5:BF5+AM5:BG5)/AL$3:BF$3)

 

[halley189]

Các bác giúp mình vấn đề này với..

Mình có 2 sheet. sheet1 có insert 1 biểu tượng muỗi tên(bằng shape) để đến sheet 2 và sheet 2 có biểu tượng shape(back) để quay về sheet 1.
Giờ có cách gì để cố định các shape đó cố định trên màn hình để khi cuộn là ko bị mất ko nhỉ vì khi mình xuống page 2-3 thì phải cuộn lên trang đầu mới nhấn vào button để đi đến sheet 2.
Mình test hết mấy cái move or don't move trong format shape mà ko dc.
//Không đưa lên đỉnh và chọn chế độ Freeze vì mình có rất nhiều button như vậy trong 1 sheet.
Tks các bác!

 

[AnhThu-1976]

Các bác giúp mình vấn đề này với..

Mình có 2 sheet. sheet1 có insert 1 biểu tượng muỗi tên(bằng shape) để đến sheet 2 và sheet 2 có biểu tượng shape(back) để quay về sheet 1.
Giờ có cách gì để cố định các shape đó cố định trên màn hình để khi cuộn là ko bị mất ko nhỉ vì khi mình xuống page 2-3 thì phải cuộn lên trang đầu mới nhấn vào button để đi đến sheet 2.
Mình test hết mấy cái move or don't move trong format shape mà ko dc.
//Không đưa lên đỉnh và chọn chế độ Freeze vì mình có rất nhiều button như vậy trong 1 sheet.
Tks các bác!

Sao bạn không để nó ở tiêu đề rồi dùng Freeze panel nhỉ!

 

[halley189]

Sao bạn không để nó ở tiêu đề rồi dùng Freeze panel nhỉ!

Thì mình có giải thích ở trên rồi bạn. Mình muốn làm loạt mấy cái button ấy cho sheet 2, sheet 3, sheet 4 vv.. và bố trí theo hàng dọc=> không thể freezepanel dc(vì quá to)... Với lại mình sau này còn muốn ứng dụng vào mấy cái nữa...

Tks bạn!

 

[Thang Bui]

Chào tất cả mọi người cộng đồng GPE,
Tuần trước em có đăng bài trên GPE xin trợ giúp
"Em xin nhờ mọi người hỗ trợ xây dựng giúp hàm tìm ra 3 lỗi vi phạm nhiều nhất của học sinh (chi tiết trong file đính kèm). Em có tạo một list động tên các học sinh, để sao cho khi mình chọn tên học sinh thì 3 lỗi vi phạm nhiều nhất như trong bảng sẽ hiển thị xuống nội dung bên dưới. Nếu trong trường hợp, lỗi vi phạm có số lần mắc lỗi nhiều nhất trùng nhau thì sẽ được xếp trong 3 lỗi vi phạm nhiều nhất. Còn nếu số lỗi vi phạm bằng 0 thì không ghi vào nội dung lỗi vi phạm.

Em cũng nghĩ ra cách để tìm được nội dung lỗi vi phạm đó là vlookup tên học sinh theo bảng gốc để ra 1 dòng về lỗi vi phạm riêng, sau đó index ra tên lỗi. Nhưng chưa xử lý được vấn đề nếu 2 lỗi vi phạm có cùng số lỗi thì tham chiếu ra tên như thế nào. Và nếu ví dụ có hơn 3 lỗi vi phạm mà số lần vi phạm đều nhiều nhất, mình sẽ hiển thị thêm lỗi thứ 4 như thế nào?"

Em có được bác phuocam trợ giúp công thức
=INDEX($B$11:$F$11,MATCH(LARGE($B$12:$F$12+0.1/COLUMN($B$12:$F$12),ROWS($A$17:A17)),$B$12:$F$12+0.1/COLUMN($B$12:$F$12),0))
Chi tiết bài viết đây ạ: https://giaiphapexcel.com/diendan/t...Ựng-hÀm-tÌm-ra-lỖi-vi-phẠm-nhiỀu-nhẤt.141285/

Nhưng em tự tìm hiểu vẫn chưa hiểu được ý nghĩa của "$B$12:$F$12+0.1/COLUMN($B$12:$F$12)". Rất mong mọi người giải thích giúp em ạ!

 

[Phan Thế Hiệp]

ý nghĩa của "$B$12:$F$12+0.1/COLUMN($B$12:$F$12)"

Nhằm tạo ra sự khác biệt khi có hai hoặc nhiều số giống nhau.
Xem file kèm.

Thân

 

[Thang Bui]

Nhằm tạo ra sự khác biệt khi có hai hoặc nhiều số giống nhau.
Xem file kèm.

Thân

Cảm ơn Bác Phan Thế Hiệp rất nhiều. Vậy là em hiểu rõ hơn công thức bác Phuocam hướng dẫn rồi, còn công thức mới của bác hướng dẫn chỉ cần enter bác có thể giải thích giúp em được không "1/(LARGE($B$12:$F$12+0,1/COLUMN(B12:$F$12);ROW(A1))=$B$12:$F$12+0,1/COLUMN(B12:$F$12))" Tại sao lại là 1/(..) Cái này em không hiểu được ạ!
Trân trọng cảm ơn bác!

 

[Phan Thế Hiệp]

Cảm ơn Bác Phan Thế Hiệp rất nhiều. Vậy là em hiểu rõ hơn công thức bác Phuocam hướng dẫn rồi, còn công thức mới của bác hướng dẫn chỉ cần enter bác có thể giải thích giúp em được không "1/(LARGE($B$12:$F$12+0,1/COLUMN(B12:$F$12);ROW(A1))=$B$12:$F$12+0,1/COLUMN(B12:$F$12))" Tại sao lại là 1/(..) Cái này em không hiểu được ạ!
Trân trọng cảm ơn bác!

Xem giải thích công thức tại "Dò tìm theo nhiều điều kiện":
LOOKUP( 2 , 1/(ĐK1)/(ĐK2)/.../(ĐKn), Vùng Giá trị ra Kết Quả )

Thân

 

[tieuthubuongbinh]

Nhằm tạo ra sự khác biệt khi có hai hoặc nhiều số giống nhau.
Xem file kèm.

Thân

Tại sao cùng là 1 CT nhưng ở cell khác nhau lại cho ra kq khác nhau bác nhỉ? (kq của row 13)

=0.1/{2,3,4,5,6}

 

[Thang Bui]

Xem giải thích công thức tại "Dò tìm theo nhiều điều kiện":
LOOKUP( 2 , 1/(ĐK1)/(ĐK2)/.../(ĐKn), Vùng Giá trị ra Kết Quả )

Thân

Cảm ơn bác Hiệp rất nhiều về chỉ dẫn vô cùng cụ thể. Nhờ Bác và mọi người trên GPE em tìm hiểu và học thêm được rất nhiều!
Chúc bác và cộng đồng GPE luôn khỏe mạnh!
Trân trọng,