trợ giúp xuất sắc

trợ giúp xuất sắc

Xin chào,

Tôi cần chuyển đổi bảng

Giả vờ có cái này

1 50 100 156 200
2 12 20 30 40
3 30 60 80 300
4 15 25 35 85

Tôi phải biến đổi như thế này

1 50
1 100
1 156
1 200
2 12
2 20
2 30
2 40
3 30
3 60
3 80
3 300
4 15
4 25
4 35
4 85

Sử dụng chức năng gì?

Cảm ơn




Trả lời:

Tham khảo hình ảnh bên dưới:

Nhập công thức dưới đây vào ô A9 và sao chép:

=IFERROR(IF(COUNTIF(A$8:A8,A8)<IFERROR(MAX(MATCH(A8,$A$2:$A$5,0),4),0),A8,INDEX($A$2:$A $5,MATCH(0,INDEX(COUNTIF(A$8:A8,$A$2:$A$5),0,0),0))),"")

Nhập công thức dưới đây vào ô B9 và sao chép:

=IF($A9="","",OFFSET(INDEX($B$2:$E$2,MOD(ROW()-ROW($A$9),4)+1),ROUNDUP((ROW()- HÀNG($A$9)+1)/4,0)-1,))

LUÂN PHIÊN:

Tham khảo hình ảnh bên dưới - thay thế nếu bạn muốn kết quả của mình trong một trang tính riêng:

Nhập công thức dưới đây vào ô A2 và sao chép:

=IFERROR(IF(COUNTIF(A$1:A1,A1)<IFERROR(MAX(MATCH(A1,Sheet1!$A$2:$A$5,0),4),0),A1,INDEX(Sheet1!$A $2:$A$5,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!$A$2:$A$5),0,0),0))),"")

Nhập công thức dưới đây vào ô B2 và sao chép:

=IF($A2="","",OFFSET(INDEX(Sheet1!$B$2:$E$2,MOD(ROW()-ROW($A$2),4)+1),ROUNDUP((ROW( )-ROW($A$2)+1)/4,0)-1,))

Bạn có thể tải xuống tệp excel từ liên kết bên dưới trong đó điều này đã được minh họa:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Repeat_Transpose_SpecificTimes_1.xlsx

Trân trọng,

Amit tandon

www.globaliconnect.com



Trả lời:

Cảm ơn bạn đã phát lại của bạn.

Nó hoạt động tốt. Nhưng, cụ thể hơn, tình hình thực tế là như thế này

HF102 D69.3 E06.1 E27.1 G35 H16.0 H16.1
HF098 D69.3 E06.1 E27.1 G35

HF131 D69.3 E06.1 E27.1 G35 H16.0 H16.1
HF104 D69.3 E06.1 E27.1
HF105 D69.3 E06.1 E27.1 G35 G70.0 J45.0
JF517 D80.1 D80.2 D80.3 D80.4

JF518 D80.1 D80.2
JF519 D80.1 D80.2 D80.3 D80.4 D80.5 D80.6
JF520 D80.1 D80.2 D80.3 D80.4 D80.5 D80.6
JF521 D80.1 D80.2 D80.3 D80.4 D80.5 D80.6
JF522 D80.1 D80.2 D80.3 D80.4 D80.5 D80.6

Tôi có khoảng 1600 hàng, các hàng này phải được nhân lên theo số cột mà tôi có các giá trị. Một số hàng có một hoặc hai cột, một số có tới 60 cột.

Đối với hàng đầu tiên ở trên, tôi phải thực hiện 6 hàng bắt đầu bằng HF102 và đối với mỗi hàng trong cột thứ hai D69.3, E06.1, v.v.

Đối với hàng bắt đầu bằng JF518, tôi phải thực hiện 2 hàng bắt đầu bằng JF518 và cho mỗi hàng trong cột thứ hai D80.1, D80.2



Trả lời:

Sử dụng các công thức bên dưới trong Trang tính 2 (giả sử dữ liệu của bạn nằm trong Trang tính 1, "A2:AZ1000"):

Nhập công thức dưới đây vào ô A2 và sao chép:

=IFERROR(IF(COUNTIF(A$1:A1,A1)<IFERROR(SUMPRODUCT(--((LEN(Sheet1!$B$2:$AZ$1000)>0)*(ROW(Sheet1!$B$2:$AZ $1000)-ROW(Sheet1!$B$2)+1)=MATCH(A1,Sheet1!$A$2:$A$1000,0))),0),A1,INDEX(Sheet1!$A$2:$A$1000 ,MATCH(0,INDEX(COUNTIF(A$1:A1,Sheet1!$A$2:$A$1000&""),0,0),0))&""),"")

Nhập công thức dưới đây vào ô B2 và sao chép:

=IF(A2="","",OFFSET(Sheet1!$B$2,MATCH($A2,Sheet1!$A$2:$A$1000,0)-1,COUNTIF(A$2:A2,A2)-1 ))

Bạn có thể tải xuống tệp excel từ liên kết bên dưới trong đó điều này đã được minh họa:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Repeat_Transpose_SpecificTimes_2.xlsx

Điều này sẽ tự động tính toán số lượng cột đối với ô đầu tiên của mỗi hàng sao cho một số hàng có thể có 1 hoặc 2 cột trong khi một số có thể có 25. Tuy nhiên, do có nhiều phép tính nên quá trình xử lý có thể trở nên phức tạp đối với một số lượng lớn bản ghi. chậm. Trong trường hợp đó, bạn có thể sử dụng mã vba như sau:

Nhập mã bên dưới vào một mô-đun tiêu chuẩn:

Sub RowsToColumn()
'Các giá trị trong mỗi hàng của Sheet1 (B2 cho đến giá trị cuối cùng của hàng 2) sẽ được hiển thị trong cột B _
của Sheet2 trong mỗi hàng liên tiếp
'Các giá trị của Cột A (Trang tính 1) sẽ hiển thị trong cột A (Trang tính 2), chỉ khi bất kỳ giá trị Nhóm nào là _
có mặt trong cùng một hàng

Dim wsSource dưới dạng trang tính, wsDest dưới dạng trang tính
Dim lastRow As Long, lastCol As Long, rSource As Long, rDest As Long, c As Long

Đặt wsSource = Sheets("Sheet1")
Đặt wsDest = Sheets("Sheet2")

'nhập tiêu đề vào hàng đầu tiên của Sheet2, sau khi xóa tất cả nội dung của trang tính
Với wsDest
.Cells.ClearContents
.Range("A1") = "Tên"
.Range("B1") = "Nhóm"
.Range("A1:B1").Font.Bold = True
Kết thúc với

'xác định hàng cuối cùng trong cột A của Sheet1
lastRow = wsSource.Range("A" & Rows.Count).End(xlUp).Row

rDest = 2
Đối với rSource = 2 Đến hàng cuối cùng
'xác định cột cuối cùng trong mỗi hàng của Sheet1
lastCol = wsSource.Cells(rSource, Columns.Count).End(xlToLeft).Column
Nếu lastCol > 1 Thì
Với c = 2 Đến lastCol
wsDest.Cells(rDest, 1) = wsSource.Cells(rSource, 1)
wsDest.Cells(rDest, 2) = wsSource.Cells(rSource, c)
'tăng hàng trong Sheet2 cho mục nhập tiếp theo
rDest = rDest + 1
Tiếp theo c
kết thúc nếu
rSource tiếp theo

'tự động khớp các cột của Sheet2
wsDest.Columns.AutoFit

kết thúc phụ

Bạn có thể tải xuống tệp excel từ liên kết bên dưới trong đó điều này đã được minh họa:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Repeat_Transpose_SpecificTimes_2.xlsm

Trân trọng,

Amit tandon

www.globaliconnect.com



Trả lời:

CHÀO

Có một cách dễ dàng hơn và nhanh hơn để làm điều này:

Sử dụng Get & Transform (đối với Excel 2016) hoặc Power Query (đối với Excel 2010/2013).

Tải bảng bằng cách sử dụng tùy chọn nhập 'từ bảng' (trong cùng một sổ làm việc), trình soạn thảo truy vấn sẽ mở ra và trong đó, hãy chọn cột đầu tiên, chuyển đến dải băng Chuyển đổi và trên nút tách 'Unpivot Column', chọn ' Unpivot Other Columns ', điều này sẽ tạo cho bạn bảng bạn cần (bạn có thể xóa thêm một cột).

Việc chọn 'Tải vào' ở giai đoạn này sẽ đóng trình soạn thảo truy vấn và sẽ tạo một bảng như vậy trong sổ làm việc của bạn.

Bạn có thể tìm thấy chức năng này trong Excel 2016 trong dải băng Dữ liệu -> Nhận & Chuyển đổi hoặc trong Excel 2010/2013 trong dải băng Power Query (để tải xuống và cài đặt Phần bổ sung Power Query cho Excel 2010/2013, hãy truy cập tại đây: https:// www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=39021b6b-dd98-41f8-bfc3-6bffe8d96e2e.

Tốt nhất

Hê-li



Trả lời:

CHÀO,

Giải pháp đơn giản nhất (cách 2 lần nhấp chuột) là sử dụng "Unpivot Other column" của Power Query - phần bổ trợ này miễn phí để tải xuống và sử dụng từ Microsoft). Trong trường hợp bạn không thể tải xuống phần bổ trợ, bạn có thể tham khảo giải pháp của tôi tại đây .

Hi vọng điêu nay co ich.



Trả lời:

Rất dễ dàng, và hoạt động tốt.

Cảm ơn

Comments

Post a Comment

Popular posts from this blog

Excel 2016 - mở tất cả các tệp trong MỘT phiên bản

Image
Excel 2016 - mở tất cả các tệp trong MỘT phiên bản Xin chào, Tôi gặp sự cố với Excel 2016 (16.0.10228.20051 32-bit): Tôi có một phiên bản Excel đang chạy. (NB - hộp kiểm " Bỏ qua các ứng dụng khác sử dụng Trao đổi dữ liệu động (DDE) " trong Tùy chọn nâng cao KHÔNG được chọn.) Điều tôi cần là - đối với tất cả các tệp Excel mà tôi sắp mở để mở trong phiên bản Excel đầu tiên và duy nhất của mình e, luôn luôn và bất kể: - tôi mở bao nhiêu tập tin cùng một lúc, - nơi chứa các tập tin (tức là trên ổ đĩa cục bộ hoặc trên OneDrive nên hệ thống cần tải chúng xuống trước khi mở) và kể cả khi mở tệp Excel từ các ứng dụng Office khác, tức là tệp đính kèm Outlook. Hành vi mà tôi thấy trong Excel 2016 tốt nhất là không nhất quán: 1. Khi mở nhiều tệp bổ sung cùng một lúc từ đĩa (chọn nhiều tệp/nhấp chuột phải/Mở) - nó sẽ mở n tệp đầu tiên trong phiên bản Excel đầu tiên, nhưng sau đó từ tệp n +1, nó bắt đầu mở chúng trong phiên bản mới . 2. Một biến thể của trường hợp trên là k...
Read more

Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể.

Image
Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể. Trong excel 2003, trong mục tùy chỉnh thanh công cụ, người ta có thể lấy một nút và chỉnh sửa hình ảnh của nút đó. Điều tôi muốn làm trong excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy cập nhanh và để nút đó chạy một macro cụ thể. Ví dụ, tạo nút có chữ G lớn và khi nhấp vào, nút sẽ thực hiện truy vấn web và nhập giá vàng từ kitco.com. Có cách nào để thực hiện việc này trong Excel 2010 không? Trả lời: Xin chào, Để bật tab Nhà phát triển 1. Trên tab Tệp , chọn Tùy chọn để mở hộp thoại Tùy chọn . 2. Nhấp vào Tùy chỉnh Ribbon ở phía bên trái của hộp thoại. 3. Trong mục Chọn lệnh từ ở phía bên trái hộp thoại, chọn Lệnh phổ biến . 4. Trong mục Tùy chỉnh Dải băng ở phía bên phải hộp thoại, chọn Tab chính trong hộp danh sách thả xuống, sau đó chọn hộp kiểm Nhà phát triển . 5. Nhấp vào OK . Để biết thêm thông tin về cách tạo ...
Read more

Khẩn cấp - File Excel chứa các ký tự đặc biệt ở họ và tên liên hệ

Khẩn cấp - File Excel chứa các ký tự đặc biệt ở họ và tên liên hệ Kính gửi bộ phận hỗ trợ, Đây là một vấn đề được ưu tiên hàng đầu đối với các marcomms của chúng tôi. Tôi có tệp Microsoft Excel chứa các ký tự đặc biệt trong họ và tên của liên hệ. Chúng tôi muốn gửi một email được cá nhân hóa dựa trên họ và tên liên hệ. Tôi đã thực hiện rất nhiều nghiên cứu và xem video nơi mọi người chia sẻ giải pháp nhưng không hiệu quả. Tôi sẽ đánh giá cao nếu bạn có thể chia sẻ cách loại bỏ những ký tự đặc biệt đó cùng một lúc. Mong muốn được nghe từ bạn. Trân trọng, Mohsin Ali Trả lời: Bạn sẽ cần phải cung cấp một số chi tiết ít nhất là một đoạn excel đính kèm với bài đăng của bạn Trả lời: Tải dữ liệu của bạn vào PowerQuery. Có lệnh xóa ký tự cụ thể theo vị trí > https://docs.microsoft.com/en-us/powerquery-m/text-removerange > https://www.thebiccountant.com/2020/03/20/trimming-text-with-custom-characters-in-power-bi-and-power-query/ > https://www.mrexcel.com/excel-tips/extracti...
Read more