Learn

The forum of documents and methods for studying - Lượm lặt kiến thức


    Chuyển đổi bảng dọc-ngang

    QaniTri
    QaniTri
    Admin

    Nam Libra Monkey
    Tổng số bài gửi : 1568
    Tiền xu Ⓑ : 3891
    Được cảm ơn № : 6
    Ngày khởi sự Ngày khởi sự : 07/01/2013
    Đến từ Đến từ : HCMC
    Côngviệc / Sởthix Côngviệc / Sởthix : Languages, Softwares, Sciences, Martial arts

    Chuyển đổi bảng dọc-ngang Empty Chuyển đổi bảng dọc-ngang

    Bài gửi by QaniTri 5th May 2016, 01:23

    Sometimes you need to switch or rotate cells. You can do this by copying, pasting, and using the Transpose option. But doing that creates duplicated data. If you don't want that, you can type a formula instead using the TRANSPOSE function. For example, in the following picture the formula =TRANSPOSE(A1:B4) takes the cells A1 through B4 and arranges them horizontally.


    Chuyển đổi bảng dọc-ngang 0887570b-6f95-49b9-8811-2bfb7bdae231


    The key to getting TRANSPOSE to work: Make sure to press CTRL+SHIFT+ENTER after typing the formula. If you've never entered this kind of formula before, the following steps will guide you through the process.


    Step 1: Select blank cells



    First select some blank cells. But make sure to select the same number of cells as the original set of cells, but in the other direction. For example, there are 8 cells here that are arranged vertically:


    Chuyển đổi bảng dọc-ngang 4e28050c-4c72-4f19-86ad-7e1f0e4aaf60


    So, we need to select eight horizontal cells, like this:


    Chuyển đổi bảng dọc-ngang Db13f266-e8a4-43f1-8d36-f720057d1434


    This is where the new, transposed cells will end up.


    Step 2: Type =TRANSPOSE(



    With those blank cells still selected, type: =TRANSPOSE(


    Excel will look similar to this:


    Chuyển đổi bảng dọc-ngang De782748-86ea-48de-82ba-78c4ec4c61eb


    Notice that the eight cells are still selected even though we have started typing a formula.


    Step 3: Type the range of the original cells.



    Now type the range of the cells you want to transpose. In this example, we want to transpose cells from A1 to B4. So the formula for this example would be: =TRANSPOSE(A1:B4) -- but don't press ENTER yet! Just stop typing, and go to the next step.


    Excel will look similar to this:


    Chuyển đổi bảng dọc-ngang 130c90d4-a2b6-421d-82e0-114e9a034c4e


    Step 4: Finally, press CTRL+SHIFT+ENTER



    Now press CTRL+SHIFT+ENTER. Why? Because the TRANSPOSE function is only used in array formulas, and that's how you finish an array formula. An array formula, in short, is a formula that gets applied to more than one cell. Because you selected more than one cell in step 1 (you did, didn't you?), the formula will get applied to more than one cell. Here's the result after pressing CTRL+SHIFT+ENTER:


    Chuyển đổi bảng dọc-ngang 23c47f8f-620d-4147-822b-3604e9eb5cb9


    Tips




    • You don't have to type the range by hand. After typing =TRANSPOSE( you can use your mouse to select the range. Just click and drag from the beginning of the range to the end. But remember: press CTRL+SHIFT+ENTER when you are done, not ENTER by itself.


    • Need text and cell formatting to be transposed as well? Try copying, pasting, and using the Transpose option. But keep in mind that this creates duplicates. So if your original cells change, the copies will not get updated.


    • There's more to learn about array formulas. You can learn about entering array formulas here. Or, you can read aboutdetailed guidelines and examples of them here.





    Technical details



    The TRANSPOSE function returns a vertical range of cells as a horizontal range, or vice versa. The TRANSPOSE function must be entered as an array formula in a range that has the same number of rows and columns, respectively, as the source range has columns and rows. Use TRANSPOSE to shift the vertical and horizontal orientation of an array or range on a worksheet.


    Syntax



    TRANSPOSE(array)


    The TRANSPOSE function syntax has the following argument:



    • array    Required. An array or range of cells on a worksheet that you want to transpose. The transpose of an array is created by using the first row of the array as the first column of the new array, the second row of the array as the second column of the new array, and so on. If you're not sure of how to enter an array formula, see Enter an array formula.





    See Also



    Transpose (rotate) data from rows to columns or vice versa


    Enter an array formula


    Rotate or align cell data


    Guidelines and examples of array formulas


    support.office.com


      Hôm nay: 28th March 2024, 23:50