TÌM 2 GIÁ TRỊ TRONG HÀNG VỚI SỐ LẦN XUẤT HIỆN NHIỀU NHẤT (1 người xem)

Sơn Mã · 29/4/17

Nhờ các bạn trên GPE giúp đỡ trường hợp tìm giá trị có trong hàng với số lần xuất hiện nhiều nhất.

- Mình có dùng hàm MODE tìm được giá trị xuất hiện nhiều nhất có trong hàng nhưng lại không tìm được giá trị thứ 2 với số lần xuất hiện nhiều thứ 2 có trong hàng

(Trong trường hợp trong hàng có nhiều giá trị với số lần xuất hiện nhiều nhất là như nhau thì ưu tiên lấy giá trị trong hàng từ trái qua phải)

- Rất mong sự giúp đỡ của các bạn! Xin cảm ơn!

quocgiacan · 29/4/17

Sơn Mã đã viết:
Nhờ các bạn trên GPE giúp đỡ trường hợp tìm giá trị có trong hàng với số lần xuất hiện nhiều nhất.

- Mình có dùng hàm MODE tìm được giá trị xuất hiện nhiều nhất có trong hàng nhưng lại không tìm được giá trị thứ 2 với số lần xuất hiện nhiều thứ 2 có trong hàng

(Trong trường hợp trong hàng có nhiều giá trị với số lần xuất hiện nhiều nhất là như nhau thì ưu tiên lấy giá trị trong hàng từ trái qua phải)

- Rất mong sự giúp đỡ của các bạn! Xin cảm ơn!

PHP:

D2=LOOKUP(1,1/(IF((FREQUENCY(G2:QSV2,ROW($1:$37)-1)>=LARGE(FREQUENCY(G2:QSV2,ROW($1:$37)-1),2)),ROW($1:$37)-1,GPE)<>C2),ROW($1:$37)-1)

Ctrl+Shift+Enter fill xuống.

eke_rula · 29/4/17

Sơn Mã đã viết:
Nhờ các bạn trên GPE giúp đỡ trường hợp tìm giá trị có trong hàng với số lần xuất hiện nhiều nhất.

- Mình có dùng hàm MODE tìm được giá trị xuất hiện nhiều nhất có trong hàng nhưng lại không tìm được giá trị thứ 2 với số lần xuất hiện nhiều thứ 2 có trong hàng

(Trong trường hợp trong hàng có nhiều giá trị với số lần xuất hiện nhiều nhất là như nhau thì ưu tiên lấy giá trị trong hàng từ trái qua phải)

- Rất mong sự giúp đỡ của các bạn! Xin cảm ơn!

Bài này có nhiều cách nếu muốn dùng MODE thì bạn dùng CT này tại D2:
D2=MODE(IF(COUNTIF($C2:C2,$H2:$QSW2)=0,IF($H2:$QSW2<>"",$H2:$QSW2)))
Ctrl+Shift+Enter rồi fill xuống, fill sang cột F thì cột E là số lần xuất hiện nhiều nhất thứ 2, cột F là thứ 3....

Sơn Mã · 29/4/17

eke_rula đã viết:
Bài này có nhiều cách nếu muốn dùng MODE thì bạn dùng CT này tại D2:
D2=MODE(IF(COUNTIF($C2:C2,$H2:$QSW2)=0,IF($H2:$QSW2<>"",$H2:$QSW2)))
Ctrl+Shift+Enter rồi fill xuống, fill sang cột F thì cột E là số lần xuất hiện nhiều nhất thứ 2, cột F là thứ 3....

Cảm ơn bạn nhiều. Bạn ơi, trong công thức này dữ liệu bắt đầu có từ cột G, sao trong công thức lại là H vậy bạn?

Sơn Mã · 29/4/17

quocgiacan đã viết:
PHP:

D2=LOOKUP(1,1/(IF((FREQUENCY(G2:QSV2,ROW($1:$37)-1)>=LARGE(FREQUENCY(G2:QSV2,ROW($1:$37)-1),2)),ROW($1:$37)-1,GPE)<>C2),ROW($1:$37)-1)

Ctrl+Shift+Enter fill xuống.

Cảm ơn bạn. Bạn ơi trong công thức này mình thấy báo lỗi chỗ GPE bạn ạ?

quocgiacan · 29/4/17

Sơn Mã đã viết:
Cảm ơn bạn. Bạn ơi trong công thức này mình thấy báo lỗi chỗ GPE bạn ạ?

Nhờ nó mới phân biệt với số 0 có trong dãy số bạn yêu cầu.

Có thể thay thế bằng 1/0 cũng được, miễn làm sao nó báo lỗi là được.

Sơn Mã · 30/4/17

quocgiacan đã viết:
Nhờ nó mới phân biệt với số 0 có trong dãy số bạn yêu cầu.

Có thể thay thế bằng 1/0 cũng được, miễn làm sao nó báo lỗi là được.

Mình dùng công thức đó thấy báo lỗi là :"the syntax of this name is not correct" bạn ạ!

Sơn Mã · 30/4/17

eke_rula đã viết:
Bài này có nhiều cách nếu muốn dùng MODE thì bạn dùng CT này tại D2:
D2=MODE(IF(COUNTIF($C2:C2,$H2:$QSW2)=0,IF($H2:$QSW2<>"",$H2:$QSW2)))
Ctrl+Shift+Enter rồi fill xuống, fill sang cột F thì cột E là số lần xuất hiện nhiều nhất thứ 2, cột F là thứ 3....

Bạn ơi cho mình hỏi ạ: nếu mình muốn tìm ngược lại là thay cho tìm 2 giá trị với số lần xuất hiện nhiều nhất như trên thì bây giờ muốn tìm 2 giá trị với số lần xuất hiện ít nhất mình làm như thế nào ạ? Rất Mong sự hồi âm của các bạn!

eke_rula · 30/4/17

Sơn Mã đã viết:
Bạn ơi cho mình hỏi ạ: nếu mình muốn tìm ngược lại là thay cho tìm 2 giá trị với số lần xuất hiện nhiều nhất như trên thì bây giờ muốn tìm 2 giá trị với số lần xuất hiện ít nhất mình làm như thế nào ạ? Rất Mong sự hồi âm của các bạn!

Vậy không dùng MODE được nữa, thấy các số của bạn nhiều nhất có 2 chữ số, bạn dùng CT này ở C2:

PHP:

C2=MATCH(TRUE,COUNTIF($G2:$QSV2,ROW($1:$100)-1)/(COUNTIF($B2:B2,ROW($1:$100)-1)=0)=MIN(IFERROR(1/(1/COUNTIF($G2:$QSV2,ROW($1:$100)-1)/(COUNTIF($B2:B2,ROW($1:$100)-1)=0)),"")),)-1

Ctrl+Shift+Enter rồi fill xuống, rồi fill qua cột D và E...Cột C là số lần xuất hiện ít nhất, cột D là số lần xuất hiện ít thứ 2, cột E là số lần xuất hiện ít thứ 3.....

quocgiacan · 30/4/17

Sơn Mã đã viết:
Mình dùng công thức đó thấy báo lỗi là :"the syntax of this name is not correct" bạn ạ!

Tôi thấy bình thường mà. Xem file kèm GPE.Max và Max1.0

Sơn Mã đã viết:
Bạn ơi cho mình hỏi ạ: nếu mình muốn tìm ngược lại là thay cho tìm 2 giá trị với số lần xuất hiện nhiều nhất như trên thì bây giờ muốn tìm 2 giá trị với số lần xuất hiện ít nhất mình làm như thế nào ạ? Rất Mong sự hồi âm của các bạn!

Công thức đầu không thể dùng Mode được:

Mã:

C2=MATCH(MIN(IFERROR(1/(1/FREQUENCY(G2:QSV2,ROW($1:$37)-1)),"")),FREQUENCY(G2:QSV2,ROW($1:$37)-1),0)-1

Ctrl+Shift+Enter, fill xuống.
Công thức cho vị trí thứ 2 không điều chỉnh nhiều:

Mã:

B2=LOOKUP(1,1/(IF((FREQUENCY(G2:QSV2,ROW($1:$37)-1)<=SMALL(IFERROR(1/(1/FREQUENCY(G2:QSV2,ROW($1:$37)-1)),""),2)),ROW($1:$37)-1,gpe)<>C2),ROW($1:$37)-1)

Ctrl+Shift+Enter, fill xuống.
Vẫn tính theo nguyên tắc: hễ cùng tần suất xuất hiện thì số phát sinh trước thì tính trước. (xem dòng 10)
Bạn tham khảo file kèm.

Chúc bạn ngày vui. /-*+/

quocgiacan · 30/4/17

Sơn Mã đã viết:
Bạn ơi cho mình hỏi ạ: nếu mình muốn tìm ngược lại là thay cho tìm 2 giá trị với số lần xuất hiện nhiều nhất như trên thì bây giờ muốn tìm 2 giá trị với số lần xuất hiện ít nhất mình làm như thế nào ạ? Rất Mong sự hồi âm của các bạn!

Xem lại thấy dùng MODE tốt hơn cho trường hợp cùng tần suất xuất hiện thì số nào xuất hiện trước sẽ được ưu tiên liệt kê trước:

Mã:

C2=MODE(IFERROR(IF(MATCH(G2:QSV2,SMALL(IF((FREQUENCY(G2:QSV2,ROW($1:$37)-1)=SMALL(IFERROR(1/(1/FREQUENCY(G2:QSV2,ROW($1:$37)-1)),""),COLUMN(A$1))),ROW($1:$37)-1,""),ROW($1:$10)),0),G2:QSV2,""),""))

Ctrl+Shift+Enter, copy qua phải rồi kéo cả hai xuống.

Bạn tham khảo file kèm.

Chúc bạn ngày vui. /-*+/

eke_rula · 30/4/17

Thanks anh, không để ý tới chỗ đó vẫn có thể chỉnh lại được chỉnh Row(1:100) thành vùng $G2:$QSV2 là được nhưng như vậy công thức nặng.
Mà công thức của anh cũng chưa đúng, vẫn dòng 10 nếu tính số xuất hiện ít nhất thứ 3 lẽ ra là 7, mà CT của anh khi kéo qua thêm 1 cột nữa vẫn là 35, tức là giả sử lần thứ 1 và 2 bị trùng thì nó chỉ lấy được một số đầu tiên, các số sau nó bỏ qua!!

quocgiacan · 30/4/17

eke_rula đã viết:
Thanks anh, không để ý tới chỗ đó vẫn có thể chỉnh lại được chỉnh Row(1:100) thành vùng $G2:$QSV2 là được nhưng như vậy công thức nặng.
Mà công thức của anh cũng chưa đúng, vẫn dòng 10 nếu tính số xuất hiện ít nhất thứ 3 lẽ ra là 7, mà CT của anh khi kéo qua thêm 1 cột nữa vẫn là 35, tức là giả sử lần thứ 1 và 2 bị trùng thì nó chỉ lấy được một số đầu tiên, các số sau nó bỏ qua!!

Nếu chủ thớt muốn liệt kê thêm nữa (nhiều hơn 2 vị trí) thì độn thêm đoạn công thức loại các số đã xuất hiện rồi ở đằng trước: ....IF(($G2:$QSV2<>"")*NOT(COUNTIF($B2:B2,$G2:$QSV2)),$G2:$QSV2,"")....

Mã:

C2=MODE(IFERROR(IF(MATCH($G2:$QSV2,SMALL(IF((FREQUENCY(IF(($G2:$QSV2<>"")*NOT(COUNTIF($B2:B2,$G2:$QSV2)),$G2:$QSV2,""),ROW($1:$37)-1)=SMALL(IFERROR(1/(1/FREQUENCY($G2:$QSV2,ROW($1:$37)-1)),""),COLUMN(A$1))),ROW($1:$37)-1,""),ROW($1:$10)),0),$G2:$QSV2,""),""))

Ctrl+Shift+Enter, fill xuống, copy qua phải ra kết quả vị trí 1,2,3,4...

Chúc anh em ngày thiệt vui. /-*+/

eke_rula · 1/5/17

quocgiacan đã viết:
Nếu chủ thớt muốn liệt kê thêm nữa (nhiều hơn 2 vị trí) thì độn thêm đoạn công thức loại các số đã xuất hiện rồi ở đằng trước: ....IF(($G2:$QSV2<>"")*NOT(COUNTIF($B2:B2,$G2:$QSV2)),$G2:$QSV2,"")....

Mã:

C2=MODE(IFERROR(IF(MATCH($G2:$QSV2,SMALL(IF((FREQUENCY(IF(($G2:$QSV2<>"")*NOT(COUNTIF($B2:B2,$G2:$QSV2)),$G2:$QSV2,""),ROW($1:$37)-1)=SMALL(IFERROR(1/(1/FREQUENCY($G2:$QSV2,ROW($1:$37)-1)),""),COLUMN(A$1))),ROW($1:$37)-1,""),ROW($1:$10)),0),$G2:$QSV2,""),""))

Ctrl+Shift+Enter, fill xuống, copy qua phải ra kết quả vị trí 1,2,3,4...

Chúc anh em ngày thiệt vui.

Bài này dùng Index để xét vị trí đầu tiên cũng được, nhưng dùng mode có lẽ ngắn hơn được chút, CT của anh có thể rút lại được chút:

Mã:

=MODE(IFERROR(MATCH($G2:$QSV2,IF((FREQUENCY(NOT(COUNTIF($B2:B2,$G2:$QSV2))*$G2:$QSV2,ROW($1:$37)-1)=SMALL(IFERROR(1/(1/FREQUENCY($G2:$QSV2,ROW($1:$37)-1)),""),COLUMN(A$1))),ROW($1:$37)-1,""),0),""))-1

Không cần dùng Small(,row(1:10)) do đó cũng không cần IF(,$G2:$QSV2,"") Frequency nó không xét ô trống và chuỗi nên cũng không cần đk $G2:$QSV2<>"", có thể công thức sẽ nhẹ hon7 được tý!!

quocgiacan · 1/5/17

eke_rula đã viết:
Bài này dùng Index để xét vị trí đầu tiên cũng được, nhưng dùng mode có lẽ ngắn hơn được chút, CT của anh có thể rút lại được chút:

Mã:

=MODE(IFERROR(MATCH($G2:$QSV2,IF((FREQUENCY(NOT(COUNTIF($B2:B2,$G2:$QSV2))*$G2:$QSV2,ROW($1:$37)-1)=SMALL(IFERROR(1/(1/FREQUENCY($G2:$QSV2,ROW($1:$37)-1)),""),COLUMN(A$1))),ROW($1:$37)-1,""),0),""))-1

Không cần dùng Small(,row(1:10)) do đó cũng không cần IF(,$G2:$QSV2,"") Frequency nó không xét ô trống và chuỗi nên cũng không cần đk $G2:$QSV2<>"", có thể công thức sẽ nhẹ hon7 được tý!!

Công thức của em sẽ bị sai vì không tính được giá trị 0 với tần suất xuất hiện hiện hữu.

Xem file kèm em sẽ hiểu.

quocgiacan · 1/5/17

eke_rula đã viết:
...... Frequency nó không xét ô trống và chuỗi nên cũng không cần đk $G2:$QSV2<>"", có thể công thức sẽ nhẹ hon7 được tý!!

Frequency() vẫn tính ô trống là giá trị 0, nên công thức của em sẽ cộng dồn cho giá trị 0 với tần suất khoảng >4000, vã lại nó loại trừ toàn bộ số 0 bằng việc 1/1/n cả giá trị 0 và ô trống.. Chính nhờ $G2:$QSV2<>"" mới phân biệt được giá trị 0 và ô trống.

Ba Tê · 1/5/17

Sơn Mã đã viết:
Nhờ các bạn trên GPE giúp đỡ trường hợp tìm giá trị có trong hàng với số lần xuất hiện nhiều nhất.

- Mình có dùng hàm MODE tìm được giá trị xuất hiện nhiều nhất có trong hàng nhưng lại không tìm được giá trị thứ 2 với số lần xuất hiện nhiều thứ 2 có trong hàng

(Trong trường hợp trong hàng có nhiều giá trị với số lần xuất hiện nhiều nhất là như nhau thì ưu tiên lấy giá trị trong hàng từ trái qua phải)

- Rất mong sự giúp đỡ của các bạn! Xin cảm ơn!

Đã có 15 bài xử lý bằng công thức, vui thiệt nhưng hoa cả mắt.
Thử với VBA coi sao nhé.

eke_rula · 1/5/17

quocgiacan đã viết:
Frequency() vẫn tính ô trống là giá trị 0, nên công thức của em sẽ cộng dồn cho giá trị 0 với tần suất khoảng >4000, vã lại nó loại trừ toàn bộ số 0 bằng việc 1/1/n cả giá trị 0 và ô trống.. Chính nhờ $G2:$QSV2<>"" mới phân biệt được giá trị 0 và ô trống.

Do countif anh khi đếm nó hiểu ô trống và 0 là như nhau, chứ 1 ô trống bình thường Frequency nó sẽ bỏ qua, vậy là không bỏ điều kiện $G2:$QSV2<>"" này được, thanks anh!!!

quocgiacan · 1/5/17

Ba Tê đã viết:
Đã có 15 bài xử lý bằng công thức, vui thiệt nhưng hoa cả mắt.
Thử với VBA coi sao nhé.

Khà khà khà! --=0

"Người ta đi bắt Pôkề...
(Còn) anh em mình ham xử chủ đề Excel..."

Ngày lễ thảnh thơi 30/4, do không có pháo hoa coi chơi, nên hai anh em đã làm phiền anh Ba Tê coi "pháo hoa" công thức chút chút mà!
Đâu dám chọc giận anh. /-*+/

Chúc anh em ngày nghỉ lễ thật vui vẻ, an bình bên gia đình.

quocgiacan · 1/5/17

Ba Tê đã viết:
Đã có 15 bài xử lý bằng công thức, vui thiệt nhưng hoa cả mắt.
Thử với VBA coi sao nhé.

Có xem file của anh gửi, nhưng hình như có chút gì đó... chắc nhờ anh xem lại kết quả của hai sheets.

Cảm ơn anh nhiều.

TÌM 2 GIÁ TRỊ TRONG HÀNG VỚI SỐ LẦN XUẤT HIỆN NHIỀU NHẤT (1 người xem)

Người dùng đang xem chủ đề này

Thành viên hoạt động

File đính kèm

-⍟-

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

Thành viên hoạt động

Thành viên hoạt động

-⍟-

Thành viên hoạt động

Thành viên hoạt động

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

-⍟-

File đính kèm

-⍟-

File đính kèm

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

-⍟-

File đính kèm

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

-⍟-

File đính kèm

-⍟-

Cạo Rồi Khỏi Gội

File đính kèm

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

-⍟-

-⍟-

File đính kèm

Đếm ngược thời gian