Bài tập hàm Excel căn bản.

[bebo021999]

Viết chung đi chứ phải dùng cột phụ làm gì. Nếu để giải thích thì được. Nhưng thực hành cỡ bebo thì viết chung.

Thì có 2 phần đó sư phụ, bên trái là CT chung.
Bên phải là phần giải thích , dùng cột phụ, dành cho bạn nào muốn tìm hiểu sâu hơn về cách làm.

 

[cantl]

Bài tập 6: Chuyển dữ liệu 1 cột thành bảng nhiều cột và ngược lại. File đính kèm gồm 2 sheet, 4 câu.
Chỉ dùng công thức thường của 2019 trở xuống.

Em mới làm xong câu 1:
=INDEX($C$2:$F$5;INT((COUNTA($K$1:K1)-1)/4)+1;TEXT(MOD(ROW(A1);4);"[=0]\4"))
=INDEX($C$2:$F$5;TEXT(MOD(ROW(A1);4);"[=0]\4");INT((COUNTA($K$21:K21)-1)/4)+1)
Bài này em vừa làm xong tuần trước ấy bác.

 

[xuan.97]

Em cũng tham gia ạ hihi
Em làm câu 1 và câu 2 trường hợp đầu tiên

 

[cantl]

Bài tập 6: Chuyển dữ liệu 1 cột thành bảng nhiều cột và ngược lại. File đính kèm gồm 2 sheet, 4 câu.
Chỉ dùng công thức thường của 2019 trở xuống.

Em gửi câu 2, công thức quá dài.
=INDEX($C$2:$C$17;(COUNTA($H$1:$H1)-1)*4+TEXT(MOD(COLUMN(A$1);4);"[=0]\4");1)
=IF(MOD(ROW($G9);4)=1;INDEX($C$2:$C$17;(SUM(N($H$8:$H8<>""))-1)*4+TEXT(MOD(COLUMN(A$1);4);"[=0]\4");1);"")
Nghi là mod sẽ thêm đề bài bonus cho bài này.

 

[Lê Duy Thương]

lâu rồi dùng 365 nên cũng không còn nhớ các công thức cũ để áp dụng cho bài của sư phụ chiet tiet

K2=OFFSET($B$1,LOOKUP(ROWS($K$1:$K1),{1,5,9,13},{1,2,3,4}),LOOKUP(MOD(ROWS($K$1:K1),4),{0,1,2,3},{4,1,2,3}),,)

K21=OFFSET($B$1,LOOKUP(MOD(ROWS($K$21:K21),4),{0,1,2,3},{4,1,2,3}),LOOKUP(ROWS($K$21:$K21),{1,5,9,13},{1,2,3,4}),,)

108 ký tự cho công thức trên

 

[ptm0412]

Em gửi câu 2, công thức quá dài.
=INDEX($C$2:$C$17;(COUNTA($H$1:$H1)-1)*4+TEXT(MOD(COLUMN(A$1);4);"[=0]\4");1)
=IF(MOD(ROW($G9);4)=1;INDEX($C$2:$C$17;(SUM(N($H$8:$H8<>""))-1)*4+TEXT(MOD(COLUMN(A$1);4);"[=0]\4");1);"")
Nghi là mod sẽ thêm đề bài bonus cho bài này.

Suy nghĩ đơn giản đi, phức tạp hóa làm chi. Cái [=0]\4 tôi còn không thèm nghĩ tới nó.

Em cũng tham gia ạ hihi
Em làm câu 1 và câu 2 trường hợp đầu tiên

Đã đúng và rất đơn giản. Câu 2 trường hợp sau cũng đơn giản như vậy.

lâu rồi dùng 365 nên cũng không còn nhớ các công thức cũ để áp dụng cho bài của sư phụ chiet tiet

Mai mốt đừng xưng là đệ tử lão chết tiệt nữa, xấu hổ cho lão ấy nhé. 2 vùng tô vàng kìa. Còn 365 thì quên đi, công thức cũ đơn giản hơn, nhất là câu 2.2

Thì có 2 phần đó sư phụ, bên trái là CT chung.
Bên phải là phần giải thích , dùng cột phụ, dành cho bạn nào muốn tìm hiểu sâu hơn về cách làm.

Ghép vào công thức chung thì như sau:

=IF(MOD(ROWS($1:1),4)=1,INDEX($C$2:$C$17,ROWS($1:1)+COLUMNS($A:A)-1),"")

Index không có tham số thứ 3. May mà không bị lỗi.

 

[LmoovoenX]

Bài tập 6: Chuyển dữ liệu 1 cột thành bảng nhiều cột và ngược lại. File đính kèm gồm 2 sheet, 4 câu.
Chỉ dùng công thức thường của 2019 trở xuống.

Em góp vui công thức, không tối ưu nhưng cũng là 1 cách nhìn khác.
Đặc biệt khi vùng dữ liệu có ô trống cần bỏ qua.

H2=INDIRECT(SUBSTITUTE(TEXT(SMALL(ROW(C$2:F$5)+COLUMN(C$2:F$5)/1000;COUNTA(H$1:H1));"r0,000");",";"c");0)

 

[ptm0412]

Em góp vui công thức, không tối ưu nhưng cũng là 1 cách nhìn khác.

Tôi ráp vào chưa ra được kết quả nào

 

[LmoovoenX]

Tôi ráp vào chưa ra được kết quả nào

Dấu phân cách thập phân em đang dùng là ",", nếu là "." thì phải đổi lại.
Nếu có ô trống trong dữ liệu nguồn thì dùng aggregate bác ạ.

 

[ptm0412]

Dấu phân cách thập phân em đang dùng là ",", nếu là "." thì phải đổi lại.
Nếu có ô trống trong dữ liệu nguồn thì dùng aggregate bác ạ.

À tôi lại tưởng của sheet 2

 

[Lê Duy Thương]

Suy nghĩ đơn giản đi, phức tạp hóa làm chi. Cái [=0]\4 tôi còn không thèm nghĩ tới nó.

Đã đúng và rất đơn giản. Câu 2 trường hợp sau cũng đơn giản như vậy.

Mai mốt đừng xưng là đệ tử lão chết tiệt nữa, xấu hổ cho lão ấy nhé. 2 vùng tô vàng kìa. Còn 365 thì quên đi, công thức cũ đơn giản hơn, nhất là câu 2.2

View attachment 289137

Ghép vào công thức chung thì như sau:

=IF(MOD(ROWS($1:1),4)=1,INDEX($C$2:$C$17,ROWS($1:1)+COLUMNS($A:A)-1),"")

Index không có tham số thứ 3. May mà không bị lỗi.

Hình như lão chết tiệt ráp công thức câu 1 cho câu 2 nên mới vàng khè.

 

[LmoovoenX]

À tôi lại tưởng của sheet 2

Sheet 2, đáp án như sau:

H2=INDIRECT("c"&(ROW()-2)*4+COLUMN()-6;1)

H9=IF(MOD(ROW();4)=1;INDIRECT("c"&ROW()-9+COLUMN()-6;1);"")

 

[NT Ngoc Yen]

Bài tập 6: Chuyển dữ liệu 1 cột thành bảng nhiều cột và ngược lại. File đính kèm gồm 2 sheet, 4 câu.
Chỉ dùng công thức thường của 2019 trở xuống.

Gởi lời giải bài tập 6:

 

[ptm0412]

Hình như lão chết tiệt ráp công thức câu 1 cho câu 2 nên mới vàng khè.

Đề có 4 yêu cầu, quăng lên 2 công thức lại không nói của sheet nào!
Hai công thức của sheet 1 và 1 công thức sheet 2 chưa tổng quát và rườm rà quá:
Dữ liệu 4 dòng thì lookup mảng 4 phần tử: LOOKUP(ROWS($K$1:$K1),{1,5,9,13},{1,2,3,4})
Nếu dữ liệu 8 dòng, 10 dòng, 100 dòng thì ngồi đó mà gõ mảng á hả?
Công thức câu 2.2 cũng phức tạp quá.

Sheet 2, đáp án như sau:

Công thức của bạn cũng đơn giản gọn gàng.

 

[ptm0412]

Gởi lời giải bài tập 6:

Câu 2.1
INDEX($C$2:$C$17,4*ROW(A1)-3+COLUMN(A1)-1)
Trừ 3 rồi trừ 1 thành -4. Chi bằng trừ ROW(A1) cho 1 rồi hãy nhân 4, khỏi cộng trừ thêm
(ROW(B1)-1)*4 + COLUMN(A1)
Thứ hai, bạn và nhiều người khác mà tôi biết, trong đó có bebo ở trên; bỏ qua tham số cột của hàm Index. May là không lỗi, nếu Index bảng nguồn nhiều cột là lỗi chắc luôn.
Câu 2.2
4*(QUOTIENT(ROW(A1)-1,4)+1)-3
Theo tính toán riêng cho nó thì chính là Row(A1), vậy thì viết dài chi cho khổ!

 

[ptm0412]

Công thức của tôi viết như sau:
Câu 1.1
=INDEX($C$2:$F$5,INT((ROW($B1)-1)/4)+1,MOD(ROW($B1)-1,4)+1)
Câu 1.2
=INDEX($C$2:$F$5,MOD(ROW($B1)-1,4)+1,INT((ROW($B1)-1)/4)+1)
Câu 2.1
=INDEX($C$2:$C$17,COLUMN(A1)+(ROW($B1)-1)*4,1)
Câu 2.2
=IF(MOD(ROW(B1),4)=1,INDEX($C$2:$C$17,ROW(B1)+COLUMN(A1)-1,1),"")

Ghi chú:
Cách viết Row(B1) và Column(A1) của tôi có thế viết cách khác, mỗi cách có một đặc điểm như sau
- Row(B1) và Column(A1) có thể copy công thức bỏ vào bất kỳ chỗ nào bằng cách paste vào thanh công thức, nhưng khi đã có công thức không copy ô đó qua ô khác được. Nhưng vì tôi làm chỉ 1 chỗ nên không quan tâm mấy
- Row(1:1) hoặc Column(A:A) cách này tôi chúa ghét nên không bao giờ dùng, cũng chỉ copy công thức paste vào thanh công thức, không copy ô paste vào ô khác được
- Đứng tại E2 gõ Row() -1 (hoặc -2 theo tính toán): Cách này bắt buộc paste công thức hoặc paste ô vào đúng dòng 2. Column()-5 cũng vậy, bắt buộc phải paste công thức hoặc ô vào đúng cột chỉ định
- Rows(A$1:A1) hoặc Columns($A1:A1): cách này an toàn nhất: copy paste công thức hay copy ô vào chỗ nào cũng được.

 

[xuan.97]

Công thức của tôi viết như sau:
Câu 1.1
=INDEX($C$2:$F$5,INT((ROW($B1)-1)/4)+1,MOD(ROW($B1)-1,4)+1)
Câu 1.2
=INDEX($C$2:$F$5,MOD(ROW($B1)-1,4)+1,INT((ROW($B1)-1)/4)+1)
Câu 2.1
=INDEX($C$2:$C$17,COLUMN(A1)+(ROW($B1)-1)*4,1)
Câu 2.2
=IF(MOD(ROW(B1),4)=1,INDEX($C$2:$C$17,ROW(B1)+COLUMN(A1)-1,1),"")

Ghi chú:
Cách viết Row(B1) và Column(A1) của tôi có thế viết cách khác, mỗi cách có một đặc điểm như sau
- Row(B1) và Column(A1) có thể copy công thức bỏ vào bất kỳ chỗ nào bằng cách paste vào thanh công thức, nhưng khi đã có công thức không copy ô đó qua ô khác được. Nhưng vì tôi làm chỉ 1 chỗ nên không quan tâm mấy
- Row(1:1) hoặc Column(A:A) cách này tôi chúa ghét nên không bao giờ dùng, cũng chỉ copy công thức paste vào thanh công thức, không copy ô paste vào 6 được
- Đứng tại E2 gõ Row() -1 (hoặc -2 theo tính toán): Cách này bắt buộc paste công thức hoặc paste ô vào đúng dòng 2. Column()-5 cũng vậy, bắt buộc phải paste công thức hoặc ô vào đúng cột chỉ định
- Rows(A$1:A1) hoặc Columns($A1:A1): cách này an toàn nhất: copy paste công thức hay copy ô vào chỗ nào cũng được.

Thầy giải đáp giúp em với ạ!!!

Trường hợp nào thì nên dùng Offset, trường hợp nào thì nên dùng Index để tối ưu ạ??? Như trong bài tập 6 của thầy em dùng Offset còn mọi người em thấy đều dùng Index

 

[ptm0412]

Thầy giải đáp giúp em với ạ!!!

Trường hợp nào thì nên dùng Offset, trường hợp nào thì nên dùng Index để tối ưu ạ??? Như trong bài tập 6 của thầy em dùng Offset còn mọi người em thấy đều dùng Index

Kết quả thì như nhau, nhưng Offset là 1 hàm volatile, sẽ được tính lại khi có bất kỳ thay đổi nào trên bảng tính, nên hạn chế dùng. Name dùng Offset thì được vì chỉ tính lại 5, 10 name, còn dùng cho 100 ô, ngàn ô thì ...

 

[VetMini]

Đọc càng lúc càng thấy mục đích các câu hỏi là cốt ý thử tài quý vị vặn vẹo công thức chứ đâu có thấy căn bản?

Hồi nào giờ tôi nghĩ rằng "hàm căn bản" phải có ít nhất hai yếu tố:
- Cách hoạt động của hàm rất quen thuộc với chuyên ngành của nó. Ví dụ COUNTIF, VLookup trong ngành chung chung; MID, ROUND, các hàm ma trận trong ngành toán; LINEST trong ngành phân tích; vv...
- Cách sử dụng nếu không đi sát với lô gic vấn đề thì cũng đừng moi móc kỹ xảo khiến người đọc chả hiểu gì cả. Tôi có thể dùng debug từ từ để tìm hiểu các hàm khủng, nhưng đó là kỹ thuật cao cấp. Test công thức cần kinh nghiệm,;ở đây số người có kinh nghiệm test đếm trên đầu ngón tay.

Túm lại, nếu là căn bản thì người giải phải vẽ được sơ đồ lô gic vấn đề (trong đầu nếu thông minh, bài bản như tôi thì vẽ ra giấy). Từ sơ đồ, nối mỗi bước của nó với một hoặc hai hàm. Tổng lại. Chỉnh chút xíu. Test. Hết.

Từ đàu bài #1 đến giờ, theo nhận xét của tôi là câu đố chứ đâu phải bài tập.

 

[Lê Duy Thương]

Công thức của tôi viết như sau:
Câu 1.1
=INDEX($C$2:$F$5,INT((ROW($B1)-1)/4)+1,MOD(ROW($B1)-1,4)+1)
Câu 1.2
=INDEX($C$2:$F$5,MOD(ROW($B1)-1,4)+1,INT((ROW($B1)-1)/4)+1)
Câu 2.1
=INDEX($C$2:$C$17,COLUMN(A1)+(ROW($B1)-1)*4,1)
Câu 2.2
=IF(MOD(ROW(B1),4)=1,INDEX($C$2:$C$17,ROW(B1)+COLUMN(A1)-1,1),"")

Ghi chú:
Cách viết Row(B1) và Column(A1) của tôi có thế viết cách khác, mỗi cách có một đặc điểm như sau
- Row(B1) và Column(A1) có thể copy công thức bỏ vào bất kỳ chỗ nào bằng cách paste vào thanh công thức, nhưng khi đã có công thức không copy ô đó qua ô khác được. Nhưng vì tôi làm chỉ 1 chỗ nên không quan tâm mấy
- Row(1:1) hoặc Column(A:A) cách này tôi chúa ghét nên không bao giờ dùng, cũng chỉ copy công thức paste vào thanh công thức, không copy ô paste vào ô khác được
- Đứng tại E2 gõ Row() -1 (hoặc -2 theo tính toán): Cách này bắt buộc paste công thức hoặc paste ô vào đúng dòng 2. Column()-5 cũng vậy, bắt buộc phải paste công thức hoặc ô vào đúng cột chỉ định
- Rows(A$1:A1) hoặc Columns($A1:A1): cách này an toàn nhất: copy paste công thức hay copy ô vào chỗ nào cũng được.

Thật ra bệnh nào thuốc nấy Sư phụ.. nhờ các bài trên em muốn có hàm tổng quát hơn mà khi thêm bớt dữ liệu không cần sủa công thức,
mới làm được 1 câu còn 3 câu khác chưa tìm ra ct tối ưu.

Bài đã được tự động gộp: 18/4/23

Đọc càng lúc càng thấy mục đích các câu hỏi là cốt ý thử tài quý vị vặn vẹo công thức chứ đâu có thấy căn bản?

Hồi nào giờ tôi nghĩ rằng "hàm căn bản" phải có ít nhất hai yếu tố:
- Cách hoạt động của hàm rất quen thuộc với chuyên ngành của nó. Ví dụ COUNTIF, VLookup trong ngành chung chung; MID, ROUND, các hàm ma trận trong ngành toán; LINEST trong ngành phân tích; vv...
- Cách sử dụng nếu không đi sát với lô gic vấn đề thì cũng đừng moi móc kỹ xảo khiến người đọc chả hiểu gì cả. Tôi có thể dùng debug từ từ để tìm hiểu các hàm khủng, nhưng đó là kỹ thuật cao cấp. Test công thức cần kinh nghiệm,;ở đây số người có kinh nghiệm test đếm trên đầu ngón tay.

Túm lại, nếu là căn bản thì người giải phải vẽ được sơ đồ lô gic vấn đề (trong đầu nếu thông minh, bài bản như tôi thì vẽ ra giấy). Từ sơ đồ, nối mỗi bước của nó với một hoặc hai hàm. Tổng lại. Chỉnh chút xíu. Test. Hết.

Từ đàu bài #1 đến giờ, theo nhận xét của tôi là câu đố chứ đâu phải bài tập.

Em thấy công thức trên nếu cơ bản làm được mới lạ. như các bài tập ở trường mà nhìn các công thức trên thì đảm bảo đứng hình.