Em xin hỏi thêm về cách liệt kê mảng số ra thành dòng (1 người xem)

[nguyenltx]

Chào các bác
Em đang có 1 vấn đề cần phải liệt kê những dãy số chỉ có số đầu và cuối thành từng ô riêng biệt để dễ dàng cho các công thức khác. Tìm 1 hồi trên mạng thì được cách là dùng {Row(Indirect(A1&":"&B1)} và em cũng đã áp dụng được. Tuy nhiên cách trên chỉ có list được 1 chuỗi duy nhất, em muốn mở rộng thêm là list nhiều chuỗi ra, chuỗi sau nối tiếp chuỗi đầu. Vd: {1;2;3}, {6;7}, {10;11} thì nó sẽ chạy ra các giá trị 1,2,3,6,7,10,11 trên từng ô riêng biệt. Em thử thêm thắt các kiểu nhưng vẫn không làm ra được, nay kính nhờ các bác hỗ trợ giúp em ạ.
Em cám ơn.

 

[VetMini]

... Vd: {1;2;3}, {6;7}, {10;11} thì nó sẽ chạy ra các giá trị 1,2,3,6,7,10,11 trên từng ô riêng biệt. Em thử thêm thắt các kiểu nhưng vẫn không làm ra được, nay kính nhờ các bác hỗ trợ giúp em ạ.

Đổi tất cả "{" và "}" và " " thành "". Và đổi ";" thành ","

 

[nguyenltx]

Đổi tất cả "{" và "}" và " " thành "". Và đổi ";" thành ","

Cám ơn bác nhưng hình như bác hiểu sai ý em rồi. Không phải em muốn đổi thành 1,2,3,6,7,10,11 trong 1 ô mà là list mỗi giá trị trên từng ô riêng biệt. Em có đính kèm file, bác xem lại giúp em.

 

[VetMini]

Tôi mách cho bạn cách chuyển chuỗi phức tạp thành chuỗi đơn giản; đúng như ví dụ.
Chuyện đổi nó thành từng ô bạn đã biết công thức rồi mà.

 

[nguyenltx]

Dạ vấn đề là em chỉ chuyển nó thành từng ô của 1 mảng được. Còn nối nhiều mảng (thực tế của em vài chục cái mảng như thế) thì em chưa biết làm sao do mỗi mảng nó chỉ có số đầu-số cuối.

 

[Phan Thế Hiệp]

Dạ vấn đề là em chỉ chuyển nó thành từng ô của 1 mảng được. Còn nối nhiều mảng (thực tế của em vài chục cái mảng như thế) thì em chưa biết làm sao do mỗi mảng nó chỉ có số đầu-số cuối.

Chọn J1: J100, dán công thức:

=AGGREGATE(15,6,CHOOSE({1,2,3},ROW(INDIRECT(B2&":"&C2)),ROW(INDIRECT(B3&":"&C3)),ROW(INDIRECT(B4&":"&C4))),ROW(1:100))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[nguyenltx]

Chọn J1: J100, dán công thức:

=AGGREGATE(15,6,CHOOSE({1,2,3},ROW(INDIRECT(B2&":"&C2)),ROW(INDIRECT(B3&":"&C3)),ROW(INDIRECT(B4&":"&C4))),ROW(1:100))

Kết thúc bằng Ctrl+Shift+Enter.

Thân

Cám ơn bác em đã làm được. Nhưng nếu có 50 cái mảng thì mình còn cách nào nhanh hơn là choose {1,2,3,4,5,...50} và lập 50 lần đoạn ROW(INDIRECT(B2&":"&C2)) không bác. Em nghĩ nếu dài dòng thì chắc phải làm trên VBA mới gọn được.

 

[Phan Thế Hiệp]

Cám ơn bác em đã làm được. Nhưng nếu có 50 cái mảng thì mình còn cách nào nhanh hơn là choose {1,2,3,4,5,...50} và lập 50 lần đoạn ROW(INDIRECT(B2&":"&C2)) không bác. Em nghĩ nếu dài dòng thì chắc phải làm trên VBA mới gọn được.

Đúng vậy, phải dùng VBA.

Thân

 

[CHAOQUAY]

Chào các bác
Em đang có 1 vấn đề cần phải liệt kê những dãy số chỉ có số đầu và cuối thành từng ô riêng biệt để dễ dàng cho các công thức khác. Tìm 1 hồi trên mạng thì được cách là dùng {Row(Indirect(A1&":"&B1)} và em cũng đã áp dụng được. Tuy nhiên cách trên chỉ có list được 1 chuỗi duy nhất, em muốn mở rộng thêm là list nhiều chuỗi ra, chuỗi sau nối tiếp chuỗi đầu. Vd: {1;2;3}, {6;7}, {10;11} thì nó sẽ chạy ra các giá trị 1,2,3,6,7,10,11 trên từng ô riêng biệt. Em thử thêm thắt các kiểu nhưng vẫn không làm ra được, nay kính nhờ các bác hỗ trợ giúp em ạ.
Em cám ơn.

Bạn thử công thức dưới đây
Kết quả hiển thị bắt đầu từ I2, I1 để trống.

I2=IFERROR(INDEX($B$2:$B$4,SUMPRODUCT(COUNTIF($C$2:$C$4,$I$1:I1))+1)+COUNTIF($I$1:I1,">="&INDEX($B$2:$B$4,SUMPRODUCT(COUNTIF($C$2:$C$4,$I$1:I1))+1)),"")

 

[Phan Thế Hiệp]

Cám ơn bác em đã làm được. Nhưng nếu có 50 cái mảng thì mình còn cách nào nhanh hơn là choose {1,2,3,4,5,...50} và lập 50 lần đoạn ROW(INDIRECT(B2&":"&C2)) không bác. Em nghĩ nếu dài dòng thì chắc phải làm trên VBA mới gọn được.

Nếu kết quả không cần tạo thành mảng (tức có thể lưu tạm trong bộ nhớ) như bài #6, mà chỉ muốn tính toán rồi liệt kê ra vùng theo từng ô hiện hữu trên bảng tính, thì cũng có thể dùng công thức sau:

I1=AGGREGATE(15,6,(1/(1/$B$2:$B$20)+COLUMN(A:GQ)-1)/($B$2:$B$20+COLUMN(A:GQ)-1<=$C$2:$C$20),ROW(A1))

Enter, fill xuống.

Giới hạn của từng mảng là số cuối cách số đầu không quá 200 đơn vị.

Thân

 

[excel_lv1.5]

Nếu kết quả không cần tạo thành mảng (tức có thể lưu tạm trong bộ nhớ) như bài #6, mà chỉ muốn tính toán rồi liệt kê ra vùng theo từng ô hiện hữu trên bảng tính, thì cũng có thể dùng công thức sau:

I1=AGGREGATE(15,6,(1/(1/$B$2:$B$20)+COLUMN(A:GQ)-1)/($B$2:$B$20+COLUMN(A:GQ)-1<=$C$2:$C$20),ROW(A1))

Enter, fill xuống.

Giới hạn của từng mảng là số cuối cách số đầu không quá 200 đơn vị.

Thân

Có thể tạo được mảng được bằng cách này, nhưng công thức ngắn một nữa .

 

[Phan Thế Hiệp]

Có thể tạo được mảng được bằng cách này, nhưng công thức ngắn một nữa .

Sức chỉ làm ngần này:

=AGGREGATE(15,6,$C$2:$C$20-(($C$2:$C$20-1/(1/$B$2:$B$20)-COLUMN(A:GQ)+1)^0.5)^2,ROW(A1))
Hoặc:
=AGGREGATE(15,6,TEXT(1/(1/$B$2:$B$20)+COLUMN(A:GQ)-1,"[<="&$C$2:$C$20&"]0;")+0,ROW(A1))

Còn giảm đến phân nữa thì chịu.

Thân

 

[excel_lv1.5]

Sức chỉ làm ngần này:

=AGGREGATE(15,6,$C$2:$C$20-(($C$2:$C$20-1/(1/$B$2:$B$20)-COLUMN(A:GQ)+1)^0.5)^2,ROW(A1))
Hoặc:
=AGGREGATE(15,6,TEXT(1/(1/$B$2:$B$20)+COLUMN(A:GQ)-1,"[<="&$C$2:$C$20&"]0;")+0,ROW(A1))

Còn giảm đến phân nữa thì chịu.

Thân

đúng là dùng text, tôi thấy bỏ được 1 nhánh, tưởng bớt đi phân nữa , text có thể sử dụng trong mảng 2 chiều nên sẽ rút ngắn kha khá mấy công thức mảng, thấy bữa bạn dazkangel sài cách này, giờ có lẽ bỏ kiểu SQRT và ^2, nếu kéo đúng vùng có thể viết vầy
AGGREGATE(15,6,TEXT($B$2:$B$4+COLUMN(A:GQ)-1,"[<="&$C$2:$C$20&"]")+0,ROW(A1))