Top 8 # Xem Nhiều Nhất Chức Năng Transpose Trong Excel Mới Nhất 3/2023 # Top Like

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

Lượt Xem:653

Chức năng INPUTTING trong excel – Nhập các hàm Excel

Cách rõ ràng nhất để nhập một hàm Excel là chỉ cần gõ hàm trực tiếp vào một ô Excel, với các đối số của nó được đặt trong dấu ngoặc đơn.

Tuy nhiên, nếu bạn là người mới bắt đầu hoặc đang viết một công thức phức tạp, bạn có thể thấy dễ dàng hơn khi sử dụng hộp thoại Hàm đối số, giúp bạn nhập các hàm và công thức dễ dàng hơn với các chức năng excel cơ bản

Sử dụng Hộp thoại đối số chức năng

Hộp thoại ‘ Đối số chức năng‘ sẽ tự động bật lên khi bạn chọn một hàm Excel bằng một trong các phương pháp sau:

Nút Chèn chức năng của Excel ở bên trái của thanh công thức

Chọn nút Chèn Hàm ( được biểu thị bằng biểu tượng ƒ χ ) từ phía bên trái của thanh công thức (xem bên phải ), rồi chọn tên hàm từ hộp thoại ‘Chèn Hàm’.

Chọn một hàm từ một trong các danh sách trong nhóm ‘T hư viện Chức năng‘, trên tab Công thức của ruy-băng Excel (xem bên dưới).

Khi bạn chọn một hàm bằng cách sử dụng một trong các phương pháp trên, Excel sẽ tự động hiển thị hộp thoại ‘ Đối số chức năng‘ để hỗ trợ bạn nhập chức năng đã chọn của bạn. Hộp thoại này (được hiển thị bên dưới) cho bạn biết hàm này làm gì và hàm nào mà hàm này yêu cầu.

Hộp thoại đối số hàm chức năng Excel cho hàm if

Các hình ảnh bên phải cho thấy hộp thoại cho Excel chức năng Nếu . Trong ví dụ này, con trỏ hiện đang được định vị trong trường nhập cho đối số hàm đầu tiên, Logical_test và do đó văn bản ở giữa hộp thoại cung cấp mô tả về đối số này.

Khi bạn di chuyển con trỏ đến các trường nhập khác, văn bản ở giữa hộp thoại mô tả dữ liệu / thông tin sẽ được nhập cho đối số đã chọn.

Nếu, như một phần của đối số của bạn, bạn muốn chỉ định một phạm vi trong bảng tính Excel hiện tại hoặc trong một bảng tính mở khác, điều này có thể được thực hiện bằng cách sử dụng chuột để chọn phạm vi được yêu cầu. Bất kỳ ô hoặc dải ô nào bạn chọn bằng chuột sẽ tự động được chèn vào trường đối số hiện tại.

Nhập các hàm trực tiếp vào một ô

Khi bạn trở nên quen với việc nhập các hàm và công thức Excel, bạn có thể tìm thấy nó nhanh hơn để chèn các hàm bằng cách nhập chúng trực tiếp vào một ô hoặc vào thanh công thức.

Trong trường hợp này, Excel vẫn cung cấp cho bạn các lời nhắc hữu ích, như trong ví dụ bên dưới. Trong ví dụ này, người dùng đã bắt đầu nhập hàm Excel If trực tiếp vào thanh công thức. Có thể thấy rằng, khi người dùng đã gõ vào tên hàm và khung mở, Excel sẽ hiển thị một dấu nhắc nhỏ, hiển thị cú pháp của hàm và chỉ ra các đối số cần chèn vào.

Bạn cũng có thể sử dụng chuột để chọn phạm vi mà bạn muốn tạo thành một phần của bất kỳ hàm hoặc công thức Excel nào. Để thực hiện việc này, hãy đảm bảo con trỏ của bạn ở vị trí (trong công thức), nơi cần tham chiếu ô hoặc phạm vi ô và sau đó chỉ cần sử dụng chuột để chọn phạm vi được yêu cầu. Excel sau đó sẽ tự động chèn một tham chiếu đến phạm vi đã chọn vào hàm của bạn, như được hiển thị trong hình bên dưới.

Có thể sử dụng phương pháp chọn phạm vi ở trên để chọn các ô trong bất kỳ Trang tính nào của bất kỳ Sổ làm việc nào hiện đang mở. Điều này đặc biệt hữu ích nếu bạn đang chọn một phạm vi trong sổ làm việc riêng biệt, vì tham chiếu cần bao gồm tên bảng tính, tên trang tính và phạm vi ô, có thể cồng kềnh để nhập và dễ bị lỗi.

Các bài viết mới

Các tin cũ hơn

1. Bạn biết bao nhiêu cách để Transpose trong Excel

1.1. Sử dụng Copy, Paste

Cách đầu tiên và chắc có lẽ là cách đơn giản nhất là việc Copy và Paste Transpose. Tính năng Copy và Paste không chỉ trong Excel mà trong các ứng dụng khác của bộ Office có rất nhiều điều tuyệt vời như Copy và Paste chỉ lấy giá trị, chỉ lấy công thức, chỉ lấy định dạng,….Và Copy, Paste Transform cũng không ngoại lệ

Bạn Copy (Ctrl + C), di chuyển chuột sang 1 vị trí mà bạn muốn Paste, ấn Ctrl + Alt + V (để mở hộp thoại Paste Special), tại cửa sổ này bạn chọn tới Transform, ấn OK và bạn đã có được dữ liệu được Transpose từ dữ liệu mà bạn đã Copy.

1.2. Sử dụng hàm TRANSPOSE

Hàm này với đối số truyền vào là 1 mảng, cú pháp hàm như sau: TRANSPOSE(array)

Ở đây mình dùng Excel 365 nên mọi việc sẽ dễ dàng với với tính năng Array resize. Tuy nhiên, nếu như bạn không dùng Excel 365 thì bạn có thể sẽ phải thực hiện như sau:

Data cần Transpose của mình ở đây là vùng từ B2:C6 (có 5 hàng và 2 cột). Với Excel không có tính năng Array Resize bạn cần chọn 1 vùng với 2 hàng (tương ứng với 2 cột của Data gốc) và 5 cột (tương ứng với 5 hàng của Data gốc) sau đó gõ công thức TRANSPOSE(B2:C6) và kết thúc bằng Ctrl + Shift + Enter (CSE)

Bạn cần chọn 1 vùng để đặt công thức với số hàng bằng số cột của Data gốc và số cột bằng số hàng của Data gốc.

1.3. Sử dụng Power Query

Nếu như bạn có tìm hiểu về Power Query thì chắc hẳn cũng đã biết về tính năng này trong Power Query Editor.

Bạn chỉ cần xuất kết quả ra Excel, dữ liệu của chúng ta lúc này trông sẽ như sau

2. Hướng dẫn cách xoá bỏ giá trị trùng lặp với Power Query

Để tiến hành thực hiện các thao tác trong cửa sổ Power Query Editor thì trước hết ta cần đưa dữ liệu từ Excel vào trong Power Query Editor. Điều này khá đơn giản, cụ thể ta làm như sau:

Ở đây mình dùng Excel 365 nên chức năng Power Query nằm ở trong Tabl Data trên thanh Ribbon của Excel (nếu bạn dùng các bản Excel khác thì có thể chức năng Power Query này có thể ở nơi khác)

Tại Power Query bạn tìm tới From Table/Range để tiến hành nhập dữ liệu từ 1 Table hoặc 1 vùng (range) vào Power Query Editor để xử lý. Dữ liệu mình lấy làm ví dụ cho bài viết hướng dẫn loại bỏ giá trị trùng lặp này như bạn có thể thấy ở trên (danh sách hàng hoá với các loại hoa quả), ta sẽ loại bỏ những loại hoa quả trùng lặp này như sau.

Sau khi đã đưa dữ liệu vào Power Query Editor, dữ liệu của bạn trông sẽ như sau trong cửa sổ Power Query Editor

Việc loại bỏ trùng lặp trong này rất đơn giản, bạn thực hiện như sau:

Bạn chọn Remove Duplicates để thực hiện xoá các giá trị trùng lặp. Ta được kết quả sau

Tại Applied Steps bạn sẽ thấy 1 bước được Power Query Editor ghi lại cho thao tác xoá trùng bạn vừa thực hiện trở trên.

Bây giờ bạn chỉ cần xuất kết quả đó vào Excel, lúc này ta có kết quả

Bạn đã học qua cách dùng các hàm và các kỹ thuật phức tạp trong excel, Hôm nay tôi hướng dẫn các bạn cách làm một ứng dụng nho nhỏ nhưng rất hữu ích, đặt biệt nó rất cần thiết đối với các bạn kế toán thường phải làm tem nhãn để dán lên tài sản cố định, hay các bạn nhân viên hành chính nhân sự phải in phiếu lương, thư mời…

Ví dụ tôi có bản danh sách nhân viên nằm ở Sheet2 như sau:

Và trên phiếu lương tôi cần xuất 3 thông tin xuất hiện đó là Mã Nhân Viên, Họ tên nhân viên và Phòng ban.

Theo danh sách bên trên thì mỗi nhân viên ứng với 1 con số ở bên cột No. Như vậy Cách đơn giản nhất là dùng Hàm Vlookup để gọi thông tin của từng nhân viên dựa trên số thứ tự của nó tương ứng. do đó tôi thiết kế một cái nhãn ở một trang tính khác như sau:

A B 1 Số thứ Tự 2 3 Mã Nhân Viên =vlookup(B1,sheet2!A:D,4,0)4 Họ Và Tên =vlookup(B1,sheet2!A:E,5,0)

5 Phòng Ban =vlookup(B1,sheet2!A:F,6,0)

Mỗi khi bạn nhập các số từ 1 cho đến số cuối cùng trong bản danh sách trên thì mọi thông tin của nhân viên đó được gọi ra để bạn tiến hành in.Và lưu ý là số thứ tự trong cột A không được trùng nhau.

Tuy nhiên cách làm như trên thì mỗi lần in chỉ hiện ra thông tin của một nhân viên trên một trang giấy như thế thì mất thời gian và tốn kém khi danh sách nhân viên, hay tài sản dài hàng trăm, hàng ngàn dòng.

Lúc này đòi hỏi ta thiết kết lại trang in sao cho mối lần in là nhiều nhất (tùy theo khổ giấy in đặc biệt là loại giấy có thiết kế và định dạng sẵn có keo dán.

Ở đây tôi in trên nền giấy in sẵn A5 với bốn nhãn nên tôi thiết kế trang in tương ứng như sau:

Cũng theo cách làm trên mỗi khi ta nhập một giá trị tương ứng với số thứ tự trong cột A của sheet2 vào ô D3 thì các nhãn sẽ tự động điền thông tin của 4 nhân viên có số thứ tự liền kề với giá trị của ô D3.

Như vậy thay vì in 1 lần 1 người như ở cách làm thứ 1, lúc này một lần in ta có thể in được 4 người. Nếu khổ giấy của bạn có thể in nhiều hơn thì bạn chỉ việc copy cả ba dòng chứa mã nhân viên, Họ Tên và phòng ban rồi dán xuống bên dưới. hoặc bên phải. Sau đó chỉnh sửa lại giá trị Lookup_value của hàm Vlookup thành D3+4; D3+5 v.v.

Đến đây thì ứng dụng hoàn thành 90% rồi. Sở dĩ như vậy vì nếu bạn làm việc cho công ty lớn, mỗi phòng ban có nhiều người, Nếu in theo thứ tự từ 1~ hết nhân viên trong công ty sau đó mới bọc nhãn ra, sắp xếp lại rồi bàn giao cho từng phòng. Làm như thế này cũng rất mất thời gian phân loại và sắp xếp sau khi in.

Lúc này bạn nghĩ đến việc làm sao để in theo phòng ban để không phải sắp xếp các bản in?

Cách thứ nhất,rất đơn giản đó là bạn chuyển sang Sheet2 chứa danh sách nhân viên, ta thiết lập chức năng Data Filter ở dòng thứ 1 rồi dùng chức năng Sort để sắp sếp cột Phongban theo thứ tự Alphabet, và sau đó đánh số lại cột No (Cột A), Lúc này ta có thể in theo từng phòng ban rồi.

Cách thứ 2:Như bạn thấy ở danh sách nhân viên bên trên tôi có thêm cột P.ban_No được đánh số thứ tự theo số người trong mỗi phòng ban. Đây là điểm mấu chốt của cách 2, Vậy làm sao để đánh số thứ tự theo từng phòng ban? Ta dùng Hàm IF để thực hiện việc đánh số tự động.

Để Hàm Vlookup tìm kiếm chính xác và không bị lỗi thì ta phải thiết lập Data Validation cho cột phòng ban ở danh sách nhân viên (Sheet2) và ô D1 của trang in

Lưu ý lúc này Lookup_Array là vùng từ cột C:F, và công thức hàm bên trên ta có thể lấy vùng tham chiếu từ C:F áp dụng cho tất cả các công thức trên.

Tối ưu thêm kết quả in:

Theo thiết lập trên thì ta đã hoàn chỉnh được chức năng in tự động giống với Merge Mail rồi, tuy nhiên sẽ có vấn đề phát sinh trong quá trình in, ví dụ như trang in bị rách một phần, làm cho chất lượng của một vùng nào đó của kết quả in bị xấu hoặc bị mất, lúc này ta muốn in lại một nhân viên bất kỳ mà không phải dò tìm nhân viên đó ở vị trí nào trong danh sách.

Giả sử như cột A đã được đánh số theo thứ tự từ 1 ở dòng số 2 thì, ta sẽ định vị được vị trí của nhân viên là kết quả của Hàm Match trừ đi 1 đơn vị. từ đó ta dùng hàm Vlookup để trả về số thứ tự của nó trong mỗi phòng ban, nó thuộc phòng ban nào.

Ta đặt công thức ở cột C2 như sau: =Vlookup(MATCH(D2,Sheet2!D:D,0)-1,Sheet2!A:B,2,0)&” “&Vlookup(MATCH(D2,Sheet2!D:D,0)-1,Sheet2!A:F,6,0)

lúc này mỗi khi nhập mã nhân viên vào ô D2 thì C2 sẽ hiện ra vị trí của nhân viên đó ở phòng ban nào. Căn cứ vào đó ta sẽ nhập lại tùy chọn ở ô D1 và D3 và in lấy kết quả.

Thêm một ý tưởng nữa là chúng ta sẽ dự báo số lần in cho mỗi một phòng ban và số lần in còn lại để giúp ta chuẩn bị giấy in tốt hơn.

Dự báo số trang in (ô D4) : Ta dùng Hàm CountIF để đếm số nhân viên căn cứ vào giá trị của ô D4 (Phòng ban) sau đó chia số nhân viên trong mỗi lần inC4=countif(sheet2!D:D,Sheet1!D1)D4=C4/ 4

Dự báo số trang in còn lại (ô D5) ta dùng Hàm And để kết hợp với Hàm IF để xuất ra các hướng dẫn cho người in.