Hỏi về hàm transpose và cách thêm ký tự trống (1 người xem)

[LuuGiaPhúc]

Em chào các anh chị,
Em có 2 câu hỏi , nhờ các anh chị hỗ trợ giúp :
Câu hỏi 1 :
Em có dữ liệu tại ô A1 có giá trị 202401 , 202402 , 202403 ..... Tại ô D1, em dùng hàm =TRANSPOSE(A1:A12) thì nó cho kết quả kéo ngang dữ liệu như hình



Bây giờ nếu em muốn thêm 2 ô trống vào sau mỗi giá trị thì em phải dùng hàm gì ?




Câu hỏi 2 : em có 2 cột là danh sách Miền và cửa hàng , em phải dùng hàm gì để có kết quả như cột F và G (cái này do yêu cầu của file nên không thể dùng được pivot table) Lý do : vì trong file có vùng chứa công thức , có vùng là kết quả tính toán bằng code VBA , bằng macro.... nên sau khi tổng kết số liệu xong, em sẽ dùng code VBA để chuyển tất cả công thức thành value trước khi gởi đi , nên không thể sử dụng pivot trong sheet này được
Em biết trường hợp câu hỏi 2, dùng pivot table là xong ngay , nhưng ngoài pivot table ra, không biết có công thức nào làm được như vậy hay không ạ ?
Cảm ơn các anh chị

-

 

[Nhattanktnn]

Bạn xem thử:

Câu hỏi 1 :
Em có dữ liệu tại ô A1 có giá trị 202401 , 202402 , 202403 ..... Tại ô D1, em dùng hàm =TRANSPOSE(A1:A12) thì nó cho kết quả kéo ngang dữ liệu như hình

=IFERROR(1/(1/TOROW(A1:C12)),"")

Câu hỏi 2 : em có 2 cột là danh sách Miền và cửa hàng , em phải dùng hàm gì để có kết quả như cột F và G (cái này do yêu cầu của file nên không thể dùng được pivot table)

=LET(m,UNIQUE(A2:A16),REDUCE(A1:B1,m,LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(B2:B16,A2:A16=i)),"")))))

 

[huhumalu]

Câu 01: bạn dùng thử công thức tại ô D1

=IF(MOD((COLUMN()-COLUMN($D$1)),3)=0,OFFSET($A$1,INT((COLUMN()-COLUMN($D$1))/3),0),"")

Câu 02: thì dùng Data > Remove Duplicates sau dùng Vlookup

 

[LuuGiaPhúc]

Bạn xem thử:

=IFERROR(1/(1/TOROW(A1:C12)),"")

View attachment 302239

=LET(m,UNIQUE(A2:A16),REDUCE(A1:B1,m,LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(B2:B16,A2:A16=i)),"")))))

View attachment 302240

Tuyệt vời , Công thức của bạn đúng. Nhưng có 1 tình huống ở câu 1 mà mình chưa lường được lúc đưa file mẫu , đó là cột B cà cột C của mình có dữ liệu chứ không phải là cột trống. *nếu cột B và C trống thì kết quả đúng, còn nếu B và C không trống thì có thể sửa công thức 1 chút hay không

 

[Nhattanktnn]

nếu B và C không trống thì có thể sửa công thức 1 chút hay không

Bạn sửa thế này:

=LET(rng,A1:A12,v,B1:C1/rng/0,IFERROR(TOROW(HSTACK(rng,v)),""))

 

[LuuGiaPhúc]

Câu 01: bạn dùng thử công thức tại ô D1

=IF(MOD((COLUMN()-COLUMN($D$1)),3)=0,OFFSET($A$1,INT((COLUMN()-COLUMN($D$1))/3),0),"")

Câu 02: thì dùng Data > Remove Duplicates sau dùng Vlookup

Công thức này của bạn chỉ ra 1 kết quả duy nhất là 202401 tại ô D1

Bài đã được tự động gộp: 8/7/24

Bạn sửa thế này:

=LET(rng,A1:A12,v,B1:C1/rng/0,IFERROR(TOROW(HSTACK(rng,v)),""))

Quá tuyệt vời luôn, cảm ơn bạn rất nhiều

 

[Phuocam]

Công thức của Excel 2021,Không hỗ trợ hàm TOROW và HSTACK:

=LET(a,SEQUENCE(,ROWS(A1:A12)*3,3),INDEX(A1:C12,a/3,MOD(a,3)+1))

 

[Nhattanktnn]

Công thức của Excel 2021,Không hỗ trợ hàm TOROW và HSTACK:

=LET(a,SEQUENCE(,ROWS(A1:A12)*3,3),INDEX(A1:C12,a/3,MOD(a,3)+1))

vậy vẫn phải dựa vào giá trị B:C rồi bác
em viết cách khác thế này

=LET(a,SEQUENCE(,ROWS(A1:A12)*3,0),IF(MOD(a,3),"",INDEX(A1:A12,INT(a/3)+1)))

 

[Phuocam]

vậy vẫn phải dựa vào giá trị B:C rồi bác
em viết cách khác thế này

=LET(a,SEQUENCE(,ROWS(A1:A12)*3,0),IF(MOD(a,3),"",INDEX(A1:A12,INT(a/3)+1)))

Có thử cách này rồi, tôi muốn lấy kết quả cả 3 cột. Haha, đọc đề không kỹ.

 

[Nhattanktnn]

Có thử cách này rồi, tôi muốn lấy kết quả cả 3 cột.

Vậy thì không đúng ý chủ thớt rồi bác nhé hihi

 

[LmoovoenX]

Công thức này của bạn chỉ ra 1 kết quả duy nhất là 202401 tại ô D1
View attachment 302242

Bài đã được tự động gộp: 8/7/24

Quá tuyệt vời luôn, cảm ơn bạn rất nhiều

Nếu bạn xài excel 365 có thể thử nhé

1: =TOROW(EXPAND(A1:A12;;3;""))
2: =REDUCE(A1:B1;UNIQUE(A2:A16);LAMBDA(x;y;IFNA(VSTACK(x;HSTACK(y;VSTACK("";SORT(FILTER(B2:B16;A2:A16=y)))));"")))

 

[LuuGiaPhúc]

Thật tuyệt vời, em cảm ơn các anh chị rất nhiều.
Câu 1 , cả 3 cách này đều cho kết quả đúng như em mong muốn :
=LET(rng,A1:A12,v,B1:C1/rng/0,IFERROR(1/(1/TOROW(HSTACK(rng,v))),""))
=LET(a,SEQUENCE(,ROWS(A1:A12)*3,0),IF(MOD(a,3),"",INDEX(A1:A12,INT(a/3)+1)))
=TOROW(EXPAND(A1:A12,,3,""))

Câu 2 : 2 cách này cũng cho kết quả đúng như em mong muốn
=LET(m,UNIQUE(A2:A16),REDUCE(A1:B1,m,LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(B2:B16,A2:A16=i)),"")))))
=REDUCE(A1:B1;UNIQUE(A2:A16);LAMBDA(x;y;IFNA(VSTACK(x;HSTACK(y;VSTACK("";SORT(FILTER(B2:B16;A2:A16=y)))));"")))

Cảm phiền các anh chị , cho em hỏi thêm 1 yêu cầu bổ sung ở câu 2, nếu em có thêm 1 ô để lọc tháng như vậy thì sẽ sửa công thức như thế nào ạ.
Yêu cầu này , em mới vừa nghĩ ra sau khi dùng được cả 2 công thức mà các anh chị đã hướng dẫn, nên thật xin lỗi vì đã phiền thêm các anh chị do em chưa nghĩ tới ở lúc đầu tiên đặt câu hỏi.

 

[Nhattanktnn]

nếu em có thêm 1 ô để lọc tháng như vậy thì sẽ sửa công thức như thế nào ạ

Bạn thử lại công thức sau:

=LET(d,FILTER(B2:C16,(A2:A16=H1)+(H1="(All)")),c,CHOOSECOLS,m,c(d,1),REDUCE(B1:C1,UNIQUE(m),LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(c(d,2),m=i)),"")))))

Bạn sửa lại H1 và ký tự "(ALL)" cho phù hợp nếu thay đổi.

----------------------------------------------------------------------------------------------------
Nhờ BQT xem giúp, em không thể tô màu để hướng dẫn được:

 

[befaint]

Trong tag CODE(s) không định dạng được.
Muốn định dạng thì viết ở phần nội dung.

 

[huuthang_bd]

=LET(d,FILTER(B2:C16,(A2:A16=H1)+(H1="(All)")),c,CHOOSECOLS,m,c(d,1),REDUCE(B1:C1,UNIQUE(m),LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(c(d,2),m=i)),"")))))

@Nhattanktnn Em chọn như hình là định dạng được
--

 

[LuuGiaPhúc]

Bạn thử lại công thức sau:

=LET(d,FILTER(B2:C16,(A2:A16=H1)+(H1="(All)")),c,CHOOSECOLS,m,c(d,1),REDUCE(B1:C1,UNIQUE(m),LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(c(d,2),m=i)),"")))))

Bạn sửa lại H1 và ký tự "(ALL)" cho phù hợp nếu thay đổi.

----------------------------------------------------------------------------------------------------
Nhờ BQT xem giúp, em không thể tô màu để hướng dẫn được:
View attachment 302248

Nó báo lỗi #Name? ạ , Ô H1 , em nghĩ là sẽ chỉ có các giá trị là tháng 202401 hoặc 202402 .... nếu muốn lấy hết toàn bộ (cả năm) thì em sẽ để trống
(trong pivot thường lấy hết là (All) nhưng trường hợp này là công thức, nên em nghĩ 1 là chọn tháng cần lấy, 2 là để trống là lấy hết (nên em sẽ nhập tay các giá trị tháng cần lấy vào ô H1 , nếu muốn lấy hết thì em xóa trắng ô H1 là xong)

 

[Nhattanktnn]

Trong tag CODE(s) không định dạng được.
Muốn định dạng thì viết ở phần nội dung.

Cảm ơn anh nhé !
--------------

=LET(d,FILTER(B2:C16,(A2:A16=H1)+(H1="(All)")),c,CHOOSECOLS,m,c(d,1),REDUCE(B1:C1,UNIQUE(m),LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(c(d,2),m=i)),"")))))

@Nhattanktnn Em chọn như hình là định dạng được
--
View attachment 302249

Để lần sau em thử, cảm ơn anh
---------------

Nó báo lỗi #Name? ạ

À cái zụ khai báo let(c,choosecols,..) này nó có từ phiên bản đã có Groupby. Chắc phiên bản bạn dùng chưa hỗ trợ.
Vậy chỗ "c,choosecols," xóa đi nhé. Và phía sau chỗ nào có "c(...)" thì thay bằng "choosecols(...)". Mình nghĩ tự bạn làm được

 

[LuuGiaPhúc]

Cảm ơn anh nhé !
--------------

Để lần sau em thử, cảm ơn anh
---------------


À cái zụ khai báo let(c,choosecols,..) này nó có từ phiên bản đã có Groupby. Chắc phiên bản bạn dùng chưa hỗ trợ.
Vậy chỗ "c,choosecols," xóa đi nhé. Và phía sau chỗ nào có "c(...)" thì thay bằng "choosecols(...)". Mình nghĩ tự bạn làm được

Cảm ơn bạn rất nhiều, đúng là bản excel 365 của mình chưa có các hàm mới như groupby .
Mình sửa công thức như bạn hướng dẫn và đã làm được rồi : =LET(d,FILTER(B2:C16,A2:A16=H1),m,CHOOSECOLS(d,1),REDUCE(B1:C1,UNIQUE(m),LAMBDA(a,i,VSTACK(a,HSTACK(i,""),IFNA(HSTACK("",FILTER(CHOOSECOLS(d,2),m=i)),"")))))


Cảm ơn bạn đã rất nhiệt tình hướng dẫn mình.