Top 11 # Chức Năng Transpose Trong Excel Xem Nhiều Nhất, Mới Nhất 6/2023 # Top Trend

Nếu bạn làm việc rộng rãi với Excel ở nhà, tại cơ quan hoặc tại văn phòng gia đình, bạn có thể thiết lập các bảng tính của mình một cách chính xác như bạn muốn. Tuy nhiên, mỗi người có những bí quyết và phương pháp riêng để làm việc với Excel. Một số người thích sắp xếp dữ liệu thành các cột và các hàng khác thành hàng.

Nếu ai đó cung cấp cho bạn tệp Excel và bạn muốn dữ liệu được hiển thị theo một sắp xếp khác, bạn có thể dễ dàng chuyển cột thành hàng và hàng thành cột bằng cách sử dụng Excel được tích hợp sẵn chức năng Transpose. Về cơ bản có hai cách để chuyển dữ liệu trong Excel: bằng cách sao chép và dán hoặc bằng cách sử dụng hàm chuyển vị. Mặc dù cả hai sẽ chuyển dữ liệu của bạn, chúng hoạt động khác nhau, chúng tôi sẽ giải thích bên dưới.

Chuyển đổi bằng cách sử dụng hàm TRANSPOSE

Giả sử ai đó cung cấp cho bạn tệp Excel với dữ liệu được sắp xếp thành các cột và bạn thích dữ liệu ở trong hàng.

Bắt đầu bằng cách chọn vùng mà bạn muốn chuyển dữ liệu từ cột này sang hàng khác. Lưu ý trong ví dụ trên, dữ liệu chiếm A1 đến B6. Đó là bảng dữ liệu 2 x 6 (2 × 6). Để chọn khu vực chuyển vị, bạn cần đối diện hoặc khu vực 6 x 2 (6 × 2). Bắt đầu từ ô A12 (hoặc bất kỳ nơi nào khác bạn muốn dữ liệu được chuyển đổi), đánh dấu một khu vực 6 × 2.

Lưu ý rằng các ô mà chúng tôi đã chọn bao gồm A12 đến F13, một vùng 6 × 2. Với khu vực này được chọn, nhấp vào thanh công thức ở trên. Đảm bảo rằng khu vực bạn đã chọn vẫn được chọn trước khi bạn bắt đầu nhập công thức. Bây giờ, hãy nhập công thức sau vào thanh công thức

=transpose(a1:b6)

nhưng chưa nhấn Enter. Nhập công thức này vào Excel khác với hầu hết các công thức khác. Bạn cần giữ phím Ctrl và Shift khi bạn nhấn Enter. Vì vậy, nhấn Ctrl + Shift + Enter. Bảng tính Excel của bạn bây giờ sẽ trông giống như sau:

Tổ hợp phím Ctrl + Shift + Enter đặt một cặp dấu ngoặc ôm quanh công thức. Điều này cho Excel biết rằng đầu ra của công thức sẽ là một mảng dữ liệu thay vì chỉ một ô đơn. Kỳ lạ thay, bạn không thể gõ niềng răng vào chính mình; bạn phải sử dụng tổ hợp phím Ctrl + Shift + Enter.

Khi bạn sử dụng hàm chuyển vị để chuyển dữ liệu, cả hai tập dữ liệu được liên kết. Điều này có nghĩa rằng nếu bạn thay đổi dữ liệu trong A1, ví dụ, nó sẽ thay đổi giá trị trong A12 quá. Ngay cả khi các ô có công thức, nó sẽ cập nhật các giá trị ở cả hai vị trí.

Trong ví dụ bên dưới, tôi có một số dữ liệu về công nhân, giờ làm việc và tổng số tiền thanh toán. Tôi đã sử dụng chức năng chuyển đổi để chuyển dữ liệu.

Tôi đã tiếp tục và thay đổi một tên và giờ làm việc cho tất cả các cá nhân và như bạn có thể thấy, cả hai bộ dữ liệu được đồng bộ hóa.

Điều này cũng có nghĩa là nếu bạn xóa các ô hoặc các hàng dữ liệu gốc, bạn sẽ nhận được một lỗi tham chiếu trong các tế bào transposed! Nếu bạn không muốn hai bộ dữ liệu được liên kết, tùy chọn tốt hơn là sử dụng phương pháp sao chép và dán bên dưới, sao chép dữ liệu thay vì liên kết dữ liệu đó.

Chuyển đổi bằng Sao chép và Dán

Cách dễ dàng hơn để chuyển dữ liệu trong Excel là sử dụng tính năng sao chép và dán. Dữ liệu sẽ không được liên kết, vì vậy bạn có thể xóa một cách an toàn tập dữ liệu gốc nếu muốn. Tuy nhiên, nếu bạn thực hiện thay đổi đối với tập dữ liệu ban đầu, dữ liệu sẽ không được phản ánh trong dữ liệu được chuyển đổi vì nó chỉ là bản sao.

Chọn dữ liệu bạn muốn chuyển đổi rồi nhấp chuột phải và chọn Sao chéphoặc nhấn CTRL + C trên bàn phím của bạn.

Bây giờ nhấp chuột phải vào bất kỳ ô trống nào mà bạn muốn dán dữ liệu và nhấp vào Chuyển vịmạnh. Nếu bạn chỉ cần di chuột qua nút chuyển vị trí, nó sẽ thực sự cung cấp cho bạn bản xem trước trực tiếp dữ liệu trên trang tính.

Nếu bạn thay đổi bất kỳ dữ liệu nào trong tập dữ liệu ban đầu, nó sẽ không ảnh hưởng đến dữ liệu được chuyển đổi và ngược lại. Đó là về nó.

Học Excel cơ bản – #8 – Cách sử dụng chức năng và hàm Transpose trong Excel 2016

Related posts:

13.04.2010

Hướng dẫn giải thích cú pháp của hàm TRANSPOSE và chỉ ra cách sử dụng chính xác để chuyển đổi dữ liệu trong Excel.

Không có kế toán cho thị hiếu. Nó cũng đúng cho thói quen làm việc. Một số người dùng Excel thích tổ chức dữ liệu theo chiều dọc trong các cột trong khi những người khác chọn sắp xếp theo chiều ngang theo hàng. Trong các tình huống khi bạn cần nhanh chóng thay đổi hướng của một phạm vi nhất định, TRANSPOSE là chức năng sử dụng.

Hàm Excel TRANSPOSE – cú pháp

Mục đích của hàm TRANSPOSE trong Excel là chuyển đổi các hàng thành các cột, tức là chuyển hướng của một phạm vi nhất định từ ngang sang dọc hoặc ngược lại.

Hàm chỉ lấy một đối số:

= TRANSPOSE (mảng)

Ở đâu mảng là phạm vi của các tế bào để hoán vị. Mảng được xoay theo cách này: hàng đầu tiên của mảng ban đầu trở thành cột đầu tiên của mảng mới, hàng thứ hai trở thành cột thứ hai, v.v.

Lưu ý quan trọng! Để hàm TRANSPOSE hoạt động, bạn phải nhập nó dưới dạng công thức mảng bằng cách nhấn Ctrl + Shift + Enter.

Cách sử dụng hàm TRANSPOSE trong Excel

1. Đếm số lượng cột và hàng trong bảng gốc

Để bắt đầu, hãy tìm hiểu có bao nhiêu cột và hàng mà bảng nguồn của bạn chứa. Bạn sẽ cần những con số này trong bước tiếp theo.

Trong ví dụ này, chúng ta sẽ hoán chuyển bảng hiển thị khối lượng xuất khẩu trái cây tươi theo hạt:

Bảng nguồn của chúng tôi có 4 cột và 5 hàng. Giữ những số liệu này trong tâm trí, tiến hành bước tiếp theo.

2. Chọn cùng số lượng ô, nhưng thay đổi hướng

Bảng mới của bạn sẽ chứa cùng số lượng ô nhưng sẽ được xoay từ hướng ngang sang dọc hoặc ngược lại. Vì vậy, bạn chọn một phạm vi ô trống chiếm cùng số lượng hàng như bảng gốc có các cột và cùng số cột như bảng gốc có các hàng.

Trong trường hợp của chúng tôi, chúng tôi chọn một phạm vi gồm 5 cột và 4 hàng:

3. Nhập công thức TRANSPOSE

Với một loạt các ô trống được chọn, hãy nhập công thức Transpose:

Đầu tiên, bạn nhập dấu bằng, tên hàm và mở ngoặc: = TRANSPOSE (

Sau đó, chọn phạm vi nguồn bằng chuột hoặc nhập thủ công:

Cuối cùng, nhập dấu ngoặc đơn đóng, nhưng đừng nhấn phím Enter! Tại thời điểm này, công thức Chuyển đổi Excel của bạn sẽ trông giống như sau:

4. Hoàn thành công thức TRANSPOSE

nhấn Ctrl + Shift + Enter để hoàn thành công thức mảng của bạn đúng cách. Tại sao bạn cần điều này? Bởi vì công thức được áp dụng cho nhiều hơn một ô và đó chính xác là những gì công thức mảng được mục đích cho.

Khi bạn nhấn Ctrl + Shift + Enter, Excel sẽ bao quanh công thức Transpose của bạn với {dấu ngoặc nhọn} có thể nhìn thấy trong thanh công thức và là một dấu hiệu trực quan của công thức mảng. Trong mọi trường hợp, bạn nên gõ chúng bằng tay, điều đó sẽ không hoạt động.

Ảnh chụp màn hình bên dưới cho thấy bảng nguồn của chúng tôi đã được chuyển đổi thành công và 4 cột được chuyển đổi thành 4 hàng:

Cách chuyển đổi dữ liệu trong Excel mà không có số 0 cho khoảng trống

Nếu một hoặc nhiều ô trong bảng gốc trống, các ô đó sẽ có giá trị 0 trong bảng chuyển đổi, như trong ảnh chụp màn hình bên dưới:

Nếu bạn muốn trả về các ô trống thay vào đó, hãy lồng hàm IF bên trong công thức TRANSPOSE của bạn để kiểm tra xem một ô có trống hay không. Nếu ô trống, IF sẽ trả về một chuỗi trống (“”), nếu không thì cung cấp giá trị để hoán vị:

=TRANSPOSE(IF(A1:D5="","",A1:D5))

Nhập công thức như đã giải thích ở trên (hãy nhớ nhấn Ctrl + Shift + Enter để hoàn thành công thức mảng một cách chính xác) và bạn sẽ có kết quả tương tự như sau:

Mẹo và lưu ý khi sử dụng TRANSPOSE trong Excel

1. Cách chỉnh sửa công thức TRANSPOSE

Là một hàm mảng, TRANSPOSE không cho phép thay đổi một phần của mảng mà nó trả về. Để chỉnh sửa công thức Transpose, chọn toàn bộ phạm vi mà công thức đề cập đến, thực hiện thay đổi mong muốn và nhấn Ctrl + Shift + Enter để lưu công thức cập nhật.

2. Cách xóa công thức TRANSPOSE

Để xóa công thức Transpose khỏi bảng tính của bạn, hãy chọn toàn bộ phạm vi được tham chiếu trong công thức và nhấn phím Xoá.

3. Thay thế công thức TRANSPOSE bằng các giá trị

Khi bạn lật một phạm vi bằng cách sử dụng hàm TRANSPOSE, phạm vi nguồn và phạm vi đầu ra sẽ được liên kết. Điều này có nghĩa là bất cứ khi nào bạn thay đổi một số giá trị trong bảng gốc, giá trị tương ứng trong bảng chuyển đổi sẽ tự động thay đổi.

Để biết thêm thông tin, xin vui lòng xem Cách chuyển đổi công thức thành giá trị.

Các cách khác để chuyển đổi dữ liệu trong Excel

Hàm TRANSPOSE là cách phổ biến nhất nhưng không phải là cách duy nhất để thay đổi các cột thành các hàng trong Excel. Tùy thuộc vào nhiệm vụ cụ thể của bạn, bạn có thể muốn sử dụng một trong các phương pháp sau:

Nếu bạn tránh sử dụng các công thức mảng trong bảng tính của mình, hãy kết hợp các hàm INDIRECT và ADDRESS để xoay một phạm vi với bình thường công thức không mảng như thể hiện trong ví dụ này.

Đối với chuyển đổi cột thành hàng tự động, macro sau có thể có ích: Cách chuyển đổi trong Excel với VBA.

Để hoán chuyển các cột thành các hàng bằng Paste Special nhưng liên kết bảng kết quả với dữ liệu nguồn, hãy làm theo những hướng dẫn này.

Để lật một bảng trong một vài lần nhấp chuột và giữ định dạng ban đầu, hãy tận dụng một đặc biệt Công cụ chuyển đổi.

Embed Size (px)

Cc phm chc nng: F1: Hin th tr gip F2: a con tr vo trong F3: Dn tn khi vo cng thc F4: Lp li thao tc lnh trc cho nhng bi en F5: Tm trang F6: Di chuyn n phn ca s k tip F7: Kim tra chnh t ting Anh F8: Mc ni F9: Thc hin php tnh F10: Chn thanh thc n ALT + Cc phm khc: Alt + D: Vo thc n Data Alt + F: Vo thc n File tng t cho Edit, Insert, Format… Alt+ =: Tnh tng CTRL + Cc phm khc: Ctrl + A: Bi en ton b bng tnh Ctrl + B: Ch m Ctrl + R: T ng sao chp bn tri sang bn phi Ctrl + 0: n ct Ctrl + 1: M hp nh dng Ctrl + 9: n hng Ctrl + F9: Thu nh mn hnh file Ctrl + -: M hp thoi Delete hoc xa hng, ct CTRL + SHIFT + Cc phm khc: Ctrl + Shift + F: Hin danh sch fng ch Ctrl + Shift + P: Hin danh sch c ch Ctrl + Shift + 0: B n ct Ctrl + Shift + 2: nh dng theo thi gian Ctrl + Shift + 3: nh dng theo ngy, thng Ctrl + Shift + 4: nh dng theo n v tin t Ctrl + Shift + 5: nh dng theo % Ctrl + Shift + 9: B n hng Ctrl + Shift + Mi tn xung: Bi en ton b ct Ctrl + Shift + Mi tn sang phi: Bi en ton b dng nh dng d Ctrl + Shift + Ctrl + Shift + Ctrl + Shift + liu: ~: Chn dng s t nhin $: Chn dng tin t vi hai s thp phn %: Chn dng phn trm lm trn

MICROSOFT EXCEL Nhng phim tt c ban: Phm tt Chc nng Ctrl + A Chon toan b bang tinh Ctrl + C Sao chep. Enter: dan mt ln. Ctrl + V dan nhiu ln Ctrl + F Bt hp thoai tim kim Ctrl + H Bt hp thoai tim kim va thay th. Ctrl + N Tao mi mt bang tinh trng Ctrl + P Bt hp thoai in n Ctrl + S Lu bang tinh Ctrl + X ct mt ni dung ang chon Ctrl + Z Phuc hi thao tac trc o

Ctrl + * Chon vung d liu lin quan n hin tai. Ctrl + F4, Alt + F4 ng bng tnh, ng Excel Phm tt trong di chuyn Phm tt Chc nng Ctrl + Mi tn Di chuyn n vng d liu k tip Ctrl + Home V A1 Ctrl + End v c d liu cui cng Ctrl + Shift + Home Chn t hin ti n A1 Ctrl + Shift + End Chn t hin ti n c d liu cui cng Phm tt trong nh dng Ctrl + B: inh dang in m Ctrl + I: inh dang in nghing. Ctrl + U: inh dang gch chn. Ctrl + 1: Hin thi hp thoai Format Cells.

Chn ct, dng, trang bng tnh Ctrl + Spacebar: Chn ct Shift + Spacebar: Chn dng Shift + F11: Chn mt trang bng tnh mi

Cng thc mang: Ctrl + G: Bt hp thoai Go to n mt mang a c t tn trong bang tinh. Shift + F3: Nhp cng thc bng ca s Insert Function Ctrl + Shift + Enter : Kt thuc mt cng thc mang Ctrl + F3: t tn mang cho mt vung d liu. F3: Dan mt tn mang vao cng thc. n hin cac ct. Ctrl + 0 : n cac ct ang chon. Ctrl + Shift + 0: Hin cac ct bi n trong vung ang chon.

Chon cac vung khng lin tuc chon cac vung , day khng lin tuc. Ban dung chut kt hp gi phim Ctrl chon cac vung khng lin tuc cn chon. Chuyn i gia cac bang tinh ang m. Ctrl + Tab, hoc Ctrl + F6 chuyn i qua lai gia cac bang tinh ang m.

Chuyn i gia cac trang bang tinh (sheet) Ctrl + Page Up: Chuyn sang sheet trc. Ctrl + Page Down: Chuyn sang sheet k tip Dan ni dung cho nhiu cung luc. Chon mt ni dung cn sao chep, nhn Ctrl + C.

Nhp ia chi vung cn dan ni dung vao muc Name Box trn thanh Fomular dang : Nhn Enter dan ni dung vao vung trn Khng chuyn sang khac sau khi nhp Nhn t hp phim Ctrl + Enter sau khi nhp khng di chuyn con tro sang k tip Hoc vao menu Tools – Options. Chon the Edit. Bo chon muc Move selection after Enter Direction.

Chức năng đặt tên – define name giúp dễ nhớ các vùng, bảng dữ liệu – dễ quản lý dữ liệu và áp dụng vào công thức, giúp công thức ngắn gọn dễ hiểu,…

1. Chức năng đặt tên – chức năng define name giúp bạn như thế nào?

– Chức năng đặt tên – define name g iúp dễ nhớ các vùng, bảng dữ liệu – dễ quản lý dữ liệu và áp dụng vào công thức.

– Giúp công thức ngắn gọn dễ hiểu, dễ sửa chữa – chỉ cần thay đổi nội dung trong vùng dữ liệu thì kết quả công thức sẽ thay đổi theo mà không phải sửa lại công thức.

– Thực hiện một số công thức phức tạp, hoặc với số lượng lớn công thức cần xử lý.

– Làm đơn giản hóa Excel, gồm các hàm, hoặc các công thức phức tạp.

– Tránh sai sót, nhầm lẫn trong việc quản lý dữ liệu hay trong việc lập công thức.

2. Thao tác với Define name trong Excel

Chọn ô, dãy ô mà bạn muốn đặt tên.

Cách 1: Nhấp hộp Name ở bên trái ngoài cùng của thanh công thức rồi gõ nhập tên muốn đặt. Nhấn Enter.

Giao diện Define Name (New Name)

Những qui định về việc đặt tên cho các ô, các công thức, các hằng :

1. Các ký tự được phép: Ký tự đầu tiên của 1 tên phải là mẫu tự, hoặc ký tự gạch dưới. Các ký tự còn lại trong tên có thể là các mẫu tự, các số, các dấu chấm, và các ký tự gạch dưới. Ví dụ : _01_01_04 hay HH_01.

2. Các tên không thể là một tham chiếu ô, VD: R1C1 hay A$1.

3. Có thể dùng nhiều từ, nhưng không có khoảng trống. Các ký tự gạch dưới hay dấu chấm có thể được dùng để nối từ. Ví dụ: Product_01 hay Product.01.

4. Số ký tự : Một tên có thể lên đến 255 ký tự.

5. Loại kiểu chữ dùng trong tên: Excel không phân biệt ký tự in hoa và ký tự thường trong các tên.

Lưu ý: Excel không nhận ra các nhãn trong công thức nên bạn hãy vào Tools/Options/Calculation. Bên trong Workbook options bạn hãy chọn hộp kiểm Accept labels in formulas.

3. Một số ứng dụng trong sử dụng tên vùng

Bạn đang có nhu cầu: khi bạn nhập mã vào 1 ô, thì ô bên cạnh sẽ hiện tên đầy đủ của hàng hóa đó.

Ví dụ tại ô D2 bạn nhập mã BUT01 – bạn muốn kết quả tại ô E2 là Bút bi Thiên Long.

Bạn sẽ dụng hàm vlookup như sau: Tại ô E2 bạn nhập =vlookup($D$2,$A$1:$B$6,2,0). nhìn vào công thức trên, bạn sẽ thấy địa chỉ làm căn cứ tham chiếu cho hàm được thể hiện bằng tọa độ.

Như vậy sẽ khó khăn cho bạn khi muốn kiểm tra xem dữ liệu tham chiếu ở đâu, lại có nhiều ký hiệu tọa độ khiến bạn khó nhớ, khó sử dụng công thức.

Ta hãy áp dụng chức năng đặt tên vùng như sau:

Bôi đen vùng A1:B6 , đặt tên vùng là DanhMuc (theo quy ước đặt tên không phân biệt chữ hoa, chữ thường. Do đó đặt chữ hoa xen lẫn chỉ để dễ phân biệt các từ).

Đặt tên ô D2 là GiaTri

Khi đó bạn tạo hàm vlookup là =vlookup(GiaTri,DanhMuc,2,0).

Hàm cho kết quả tương tự như hàm đã tạo ở trên, nhưng bạn dễ nhớ hơn và sử dụng nhanh hơn.

Với 1 bảng số liệu gồm 5 cột, 101 hàng, tại sheet 1

Ở sheet 2, bạn cần làm các yêu cầu

Tại ô A2, bạn nhập 1 mã hàng, dùng công thức để xác định Tên (ô B2), Nhà sản xuất (C2), Giá nhập (D2), Chi tiết (E2).

Như vậy bạn cần làm tối thiểu 4 hàm vlookup cho 4 ô.

Nếu nhập công thức không dùng tên vùng, ta sẽ thấy B2 = vlookup($A$2,Sheet1!$A$1:$B$101,2,0), và nếu quên đặt dấu giá trị tuyệt đối, bạn sẽ bị sai kết quả khi sao chép công thức từ ô B2 sang C2, D2, E2.

Tất nhiên bạn phải sửa giá trị 2 ở công thức B2 thành 3 khi sang ô C2 (tham chiếu tại cột thứ 3 – tìm hiểu kỹ hàm vlookup), thành 4 khi sang ô D2.

Như vậy rất khó theo dõi nội dung hàm, cũng như cần chú ý nhiều hơn khi lập hàm.

Nếu dùng tên vùng thì sao:

Đặt tên vùng tại sheet 1 từ A1:B101 là DanhMuc.

Đặt tên vùng tại sheet 2 ô A2 là GiaTri.

Hàm vlookup sẽ là vlookup(GiaTri,DanhMuc,2,0).

Bạn không cần phải nhớ dấu giá trị tuyệt đối cho tham chiếu vùng, cho ô giá trị, vì khi đặt tên vùng nó đã tự tham chiếu bằng giá trị tuyệt đối cho vùng được đặt tên.

Khi bạn thêm/ bớt ô, cột, hàng trong vùng DanhMuc thì sao?

Công thức dạng tham chiếu tuyệt đối vlookup($A$2,Sheet1!$A$1:$B$101,2,0) không tự thay đổi khi DanhMuc thay đổi. Các giá trị tham chiếu giữ nguyên. Do đó, phần thêm/bớt có thể không được tính hoặc tính sai.

Công thức dạng tham chiếu tên vlookup(GiaTri,DanhMuc,2,0) sẽ tự thay đổi khi nội dung của vùng đặt tên được thay đổi. Nếu nội dung trong DanhMuc tăng/ giảm thì DanhMuc cũng thay đổi theo. Do đó, nó được dùng để tính toán.

Một tác dụng nữa của chức năng đặt tên vùng là thay thế công thức làm đơn giản hóa công thức

Theo ví dụ trên, có yêu cầu là tại sheet 2, ô A2 cho mã, ô B2 cho số lượng, tại C2 yêu cầu tính trực tiếp ra kết quả thành tiền. Bạn sẽ đặt công thức là C2 =B2*(vlookup(GiaTri,DanhMuc,2,0)).

Để đơn giản công thức trên, ta tạo 1 tên là DonGia. Bạn vào Define Name, tại bảng New Name, mục Name nhập DonGia, mục Refer to bạn nhập =vlookup(GiaTri,DanhMuc,2,0) rồi ấn ok.

Khi đó công thức C2 có thể đơn giản hóa là =B2*DonGia.

Việc áp dụng chức năng đặt tên vùng vào công thức tính mang lại rất nhiều lợi ích, cũng như thú vị hơn khi bạn sử dụng Excel, giúp đem lại cảm giác gần gũi, linh hoạt, đẹp mắt hơn. Ta không thấy các công thức khô khan với các tọa độ, dài dòng khó hiểu với các công thức ghép nối chằng chịt. Với chức năng đặt tên, mọi thứ đơn giản hơn rất nhiều, dễ tìm, dễ nhớ, dễ sử dụng không chỉ cho người viết ra mà còn cho cả người xem.

Cái tên nói lên tất cả, và chỉ cần thông qua 1 bước đặt tên, bạn đã dễ dàng kiểm soát được những điều tưởng như rất rắc rối, khô khan và khó khăn.