dòng dưới cùng. Tạo báo cáo tóm tắt đếm các giao dịch theo danh mục khi dữ liệu chứa nhiều hàng trên mỗi giao dịch
trình độ kỹ năng. Trung gian
Video giải thích
Xem trên Youtube và Đăng ký kênh của chúng tôiTải xuống tệp Excel
Bạn có thể tải xuống tệp Excel chứa dữ liệu nguồn cho thử thách bên dưới
Data-Analysis-Challenge-3. xlsxTải xuống
Các thách thức
Thử thách phân tích dữ liệu này dựa trên một câu hỏi hay của Rob, một thành viên của Chương trình đào tạo Elevate Excel của chúng tôi
Anh ấy có dữ liệu quy trình bán hàng [CRM] và muốn tạo một báo cáo tóm tắt về số lượng giao dịch theo giai đoạn bán hàng. Vấn đề là mỗi thỏa thuận/giao dịch có nhiều hàng trong dữ liệu nguồn. Dữ liệu bao gồm các hàng cho từng sản phẩm trong giao dịch
Tuy nhiên, Rob chỉ muốn đếm từng giao dịch một lần trong báo cáo tóm tắt của mình theo giai đoạn bán hàng. Đây là hình ảnh về kết quả cuối cùng sẽ như thế nào
Nhiệm vụ của bạn, nếu bạn chọn chấp nhận, là sử dụng bất kỳ công cụ hoặc tính năng nào trong Excel để tạo báo cáo tóm tắt
Thử thách tiền thưởng
Tôi có thêm hai thử thách dành cho bạn. Những điều này không bắt buộc, nhưng buộc chúng tôi phải suy nghĩ về việc làm cho báo cáo trở nên linh hoạt và năng động để cập nhật trong tương lai
1. Tài khoản cho dữ liệu mới
Có dễ dàng đưa dữ liệu mới vào kết quả của bạn không?
Giải pháp của bạn sẽ có thể dễ dàng tính toán lại kết quả khi dữ liệu mới được thêm vào phạm vi nguồn. Tôi đặt một số dữ liệu bổ sung trên tab Dữ liệu mới trong sổ làm việc. Bạn có thể sao chép/dán phần này bên dưới dữ liệu nguồn ban đầu để kiểm tra giải pháp của mình
Chúng tôi không muốn phải tạo lại hoàn toàn báo cáo mỗi khi dữ liệu được cập nhật
2. Tài khoản cho các giai đoạn bán hàng mới
Nếu tên giai đoạn thay đổi hoặc giai đoạn mới được thêm vào hệ thống thì sao?
Hiện tại có ba giai đoạn [Thắng, Thua, Triển vọng]. Dữ liệu trên bảng Dữ liệu mới bao gồm một giai đoạn mới, Đề xuất. Giải pháp của bạn cũng có thể tự động đưa giai đoạn mới này vào kết quả. Hoặc bao gồm nó mà không cần thêm quá nhiều công việc
Chia sẻ giải pháp của bạn
Tôi thích câu hỏi này từ Rob vì có RẤT NHIỀU cách khác nhau để giải quyết vấn đề này trong Excel. Và tất nhiên, ưu và nhược điểm đối với từng
Tuy nhiên, tôi muốn bạn thử. Đừng lo đã là giải pháp “hoàn hảo”. Điều đó có lẽ không tồn tại dù sao…
Bạn có thể để lại một hoặc trên video YouTube với một mô tả ngắn gọn về các công cụ và kỹ thuật bạn đã sử dụng để giải quyết thử thách. Bạn cũng có thể tải tệp của mình lên bất kỳ dịch vụ đám mây nào như OneDrive, Google Drive hoặc Dropbox và đăng liên kết chia sẻ trong nhận xét của bạn
Tôi sẽ theo dõi bằng [các] video giải thích các kỹ thuật phổ biến nhất cũng như ưu và nhược điểm của từng kỹ thuật. Chúng tôi mong muốn được xem các giải pháp của bạn và học hỏi từ mọi người tham gia. Thanks. 🙂
Trước 10 phím tắt cần thiết cho bảng Excel
Tiếp theo Mẹo và lối tắt để chèn bảng Excel
Bạn cũng có thể thích
Cách ngăn Excel bị đóng băng hoặc mất nhiều thời gian khi xóa hàng
Mẹo nhanh và lối tắt để đổi tên bảng Excel
Cách tách văn bản trong ô bằng Flash Fill trong Excel
Cách khắc phục bảng Excel chậm cuộn hoặc phản hồi
Hanna nói.
Xin chào, giống như Andrew
định dạng dữ liệu dưới dạng bảng
chèn bảng Pivot với Thêm dữ liệu này vào mô hình dữ liệu
hàng – Giai đoạn bán hàng
tổng – ID giao dịch – thay đổi Cài đặt giá trị thành Số lượng khác biệt
với lượng dữ liệu mới này, cách nhanh nhất là sao chép và dán vào cuối bảng, tôi đoán vậy – sau đó làm mới trục…
để làm cho nó hoàn toàn tự động, chúng tôi có thể sử dụng truy vấn nguồn, tải cả hai bảng và nối thêm chúng
tiến sĩ Excel nói.
Xin chào John, cảm ơn bạn vì thử thách thú vị này. Tôi hy vọng tôi là người đầu tiên với giải pháp này
1] Tạo một bảng từ dữ liệu, tôi đặt tên cho nó là tbl_data
2] Nhập Công thức này vào B2. B30 trong tab “Kết quả”. Nhập nó dưới dạng Công thức mảng IFERROR[INDEX[tbl_data[ID giao dịch],SMALL[IF[MATCH[tbl_data[ID giao dịch],tbl_data[ID giao dịch],0]=ROW[INDIRECT[“1. ”&ROWS[tbl_data[ID giao dịch]]]],MATCH[tbl_data[ID giao dịch],tbl_data[ID giao dịch],0],””],ROW[INDIRECT[“1. ”&ROWS[tbl_data[ID giao dịch]]]]]],””]
3] Nhập công thức này vào C2. C30 trong tab “Kết quả”. IFERROR[VLOOKUP[B2,tbl_data[[ID giao dịch]. [Giai đoạn bán hàng]],2,SAI],””]
4] Nhập Công thức này vào E2. E30 trong tab “Kết quả”. IFERROR[INDEX[$C$2. $C$30,NHỎ[NẾU[TRẬN ĐẤU[$C$2. $C$30,$C$2. $C$30,0]=ROW[INDIRECT[“1. ”&ROWS[$C$2. $C$30]]],TRẬN ĐẤU[$C$2. $C$30,$C$2. $C$30,0],””],ROW[INDIRECT[“1. ”&ROWS[$C$2. $C$30]]]]],””]
5] Enter this formula in F2. F30 in tab “Results”. IF[E2=””,””,COUNTIF[$C$2. $C$30,E2]]
The formula updates automatically in every case mentioned
Martin Harvey says.
Quick if Argument + ifCount –
1 – Cell E4, insert formula =IF[A4=A3,0,B4] and copy down
2 – Cell I4 [under Deal Count heading] insert formula =COUNTIF[E. E,H4] và sao chép xuống
Bonus 1
Create table. Adding new lines auto increase table size and results
Bonus 2
Add new line with new stage name at bottom of Results and copy Deal Count formula from row above
Tangela says.
This was an excellent challenge. I hope I did it well
Tangela Lewis says.
Hello Jon and Rob,
Thank you for the challenge, and I hope you both are doing well
The challenge for me was deciding what route to take for the end result, so I decided to do a pivotable
I copied and pasted the data to the results sheet, created a table, and named it tblResults. Then, I inserted a pivot table for the count on the same sheet as tblResults; I added “Stage” in the row and Deal ID in the sum. Once I completed that portion, I went to the new data worksheet and copied and pasted and my table expanded, but I had to redo the the items in the pivot table, like a refresh. I added them back and the new items were counted as well
Is there another way I can share the spreadsheet. Tôi vẫn chưa cảm thấy thoải mái với các công cụ khác
Tôi sẽ rất vui khi gửi nó qua email cho một trong các bạn. Bởi vì tôi muốn phản hồi của bạn
Thanks,
Peter Tholstrup nói.
Thử thách tuyệt vời, cảm ơn rất nhiều
Cho đến nay, cách nhanh nhất và dễ dàng nhất đối với tôi là giải pháp Power Query thuần túy và đơn giản “loại bỏ trùng lặp/nhóm theo” mà nhiều người khác đã nghĩ ra
Tuy nhiên, tôi cũng đã thử một giải pháp công thức
1. Chuyển đổi dữ liệu thành bảng [ở đây có tên là “Dữ liệu”]
2. Tạo danh sách giai đoạn cho mỗi giao dịch [ở đây trong trang Dữ liệu F4]
=XLOOKUP[
DUY NHẤT[ Dữ liệu[ID giao dịch] ],
Dữ liệu [ID giao dịch],
Dữ liệu[Giai đoạn bán hàng]
]
3. Trong sheet Kết quả A4, lấy danh mục giai đoạn
=UNIQUE[ Dữ liệu[Giai đoạn bán hàng] ]
4. Trong sheet Kết quả B4, đếm các giá trị tương ứng từ danh sách đã tạo ở bước 2
=COUNTIFS[ Dữ liệu. F4#, Kết quả. A4# ]
Tôi đã cố gắng bỏ qua bước 2 bằng cách thay thế đối số đầu tiên ở bước 4 bằng công thức XLOOKUP từ bước 2, vừa trực tiếp vừa dễ đọc hơn bằng cách sử dụng LET[]. Nhưng điều đó chỉ mang lại cho tôi một loạt lỗi #VALUE. Tôi thực sự không thể hiểu tại sao, dường như phản trực giác với tôi. Hoặc là?
Kiện Walters nói.
Xin chào,
Tôi đã tạo phạm vi dữ liệu thành một bảng để đảm bảo nó sẽ động và sau đó trên tab 'Kết quả', tôi đã sử dụng hàm ĐỘC ĐÁO để xác định tất cả các giai đoạn hiện tại và tương lai ['Thắng', 'Thua' & 'Triển vọng'] thay vì . Điều này có nghĩa là họ sẽ cập nhật khi một giai đoạn mới được thêm vào
Sau đó, tôi đã sử dụng kết hợp sau để xác định số lượng cho từng giai đoạn
=COUNTA[UNIQUE[FILTER[Table1[Deal ID],Table1[Sales Stage]=A5]]]
'Win' rõ ràng là ở A5. Sau đó, tôi lặp lại công thức cho 'Mất mát' và 'Triển vọng'. Khi tôi đã thêm dữ liệu bổ sung từ tab 'Dữ liệu mới', bản tóm tắt của tôi được cập nhật với số lượng mới và nghệ danh 'Đề xuất' mới
Stein nói.
Xin chào
Đây là cách tôi thực hiện thử thách này [gần giống với thử thách của Swamy từ ngày 27 tháng 8]
1. Chuyển đổi phạm vi dữ liệu thành Bảng Excel
2. Đặt tên cho mỗi cột là Named Range
[Deal_ID;Giai đoạn;Sản phẩm;Đơn vị]
[Làm cho công thức của tôi dễ đọc hơn]
CÔNG THỨC
3. Trong bảng kết quả ô B5
= ĐỘC ĐÁO[Sân khấu]
4. Tại Ô C5
=COUNTA[DUY NHẤT[LỌC[Deal_ID;Giai đoạn=B5]]]
Sao chép công thức xuống
Với Phạm vi được đặt tên trong Bảng Excel, dữ liệu mới sẽ được thêm vào Kết quả
Một nhược điểm nhỏ mặc dù
bạn phải nhớ sao chép công thức từ ô C5 xuống dưới
if new Sales Stage is added
Vượt qua thử thách, tôi đã thêm một vài mảng
Số lượng giai đoạn trên mỗi giao dịch & Số lượng giai đoạn trên mỗi sản phẩm
liên kết
https. //1drv. ms/x/s. Ag8TofPLrav0mjXgZRBEpyYrP7G3?e=qJzYbq
jim nói.
không cần sao chép công thức từ C5 nếu bạn thêm “#” sau “Stage=B5” để áp dụng công thức cho toàn bộ mảng động từ B5
Gerhard Bossinga nói.
Xin chào Jon, tôi đã gặp khó khăn một chút khi cố gắng tìm giải pháp với các chức năng mới ĐỘC ĐÁO, LỌC, v.v., nhưng bằng cách nào đó không thể nhận được kết quả khi cố gắng làm cho nó hoạt động. Vì vậy, tôi nghĩ rằng việc sử dụng Pivot table không quá khó khăn. Và nó không phải là. Vì vậy, tôi đã tạo một bảng dữ liệu và thêm nó vào bảng Pivot. Nhãn hàng là 'Giai đoạn bán hàng' và giá trị là 'ID giao dịch'. Tuy nhiên, 'ID giao dịch' cần được định dạng là Số lượng khác biệt trong cài đặt trường Giá trị. Và ở đó bạn có một bảng tổng hợp với các giá trị riêng biệt cho mỗi giai đoạn Bán hàng. Vì vậy, không có gì phải làm với việc loại bỏ các bản sao, v.v. và nhóm theo 😉 Nhưng chắc chắn có nhiều cách để có được kết quả cần thiết
James Wilkie nói.
Đã sử dụng Excel VBA để sao chép dữ liệu Giai đoạn vào bảng tính “Kết quả”, sau đó loại bỏ các bản trùng lặp để nhận các tên Giai đoạn duy nhất. Đặt chúng vào một mảng
Sau đó, sao chép dữ liệu ID và Giai đoạn sang một khu vực khác của bảng tính “Kết quả” và cũng loại bỏ các bản trùng lặp để tôi có một danh sách dữ liệu ID và Giai đoạn duy nhất
Cuối cùng, đã sử dụng mảng Giai đoạn của tôi để chạy “CountIf” đối với ID và danh sách Giai đoạn
Với dữ liệu. Dữ liệu đã thêm
Chiến thắng. 5 thắng 7
Thua 1 Thua 2
Triển vọng 4 Triển vọng 4
đề xuất 1
Không có đầu mối nào để tải lên một sổ làm việc đã bật macro, vì vậy nếu bạn đặt mã VBA này vào một mô-đun trên cơ sở dữ liệu thử nghiệm của mình, bạn có thể thấy nó hoạt động như thế nào
Tùy chọn rõ ràng
Tùy chọn Cơ sở 1
Giải pháp phụ[]
Làm mờ WS1 dưới dạng trang tính, WS2 dưới dạng trang tính
Dim Arr[] dưới dạng chuỗi
Làm mờ DataRow dưới dạng số nguyên, NewRow dưới dạng số nguyên
Dim LRow1 As Long, LRow2 As Long, LRow3 As Long
Dim SZStage dưới dạng số nguyên
Dim SZID dưới dạng số nguyên
Dim StageArray[] dưới dạng chuỗi, IDArray[] dưới dạng chuỗi
Dim i As Long
Đặt WS1 = Trang tính[“Dữ liệu”]
Đặt WS2 = Trang tính[“Kết quả”]
Dòng dữ liệu = 4
Hàng mới = 3
Lrow1 = WS1. Phạm vi [“A. MỘT"]. Ô đặc biệt[xlCellTypeLastCell]. Chèo thuyền
WS1. Phạm vi[“B” & DataRow & “. B” & Lrow1]. Sao chép WS2. Phạm vi ["H" & DataRow]
WS2. Phạm vi[“H” & DataRow & “. H” & Lrow1]. Loại bỏ các cột trùng lặp. =1, Tiêu đề. =xlKhông
WS1. Phạm vi[“A” & DataRow & “. B” & Lrow1]. Sao chép WS2. Phạm vi ["E" & DataRow]
WS2. Phạm vi[“E” & DataRow & “. F” & Lrow1]. Loại bỏ các cột trùng lặp. =1, Tiêu đề. =xlKhông
Lrow2 = WS2. Phạm vi ["H" & DataRow]. Kết thúc[xlDown]. Chèo thuyền
SZStage = [LRow2 – DataRow] + 1
Lrow3 = WS2. Phạm vi ["F" & DataRow]. Kết thúc[xlDown]. Row
ReDim StageArray[1 đến SZStage]
Đối với i = 1 Đến SZStage
StageArray[i] = WS2. Ô[[DataRow – 1] + i, 8]. Giá trị
Kế tiếp
Đối với i = 1 Đến SZStage
Phạm vi[“Tôi” & [NewRow + i]]. Giá trị = WorksheetFunction. CountIf[Phạm vi[“F” & DataRow & “. F” & LRow3], StageArray[i]]
Kế tiếp
WS2. Phạm vi[“E” & DataRow & “. F” & Lrow3]. Xóa nội dung
WS2. Phạm vi[“H” & NewRow]. Giá trị = “Giai đoạn”
WS2. Phạm vi ["Tôi" & Hàng mới]. Giá trị = “Số giao dịch”
WS2. Phạm vi[“F” & NewRow & “. Tôi” & [NewRow + SZStage]]. Nét chữ. Đậm = Đúng
kết thúc phụ
rps nói.
đặt tên cho các cột, tạo một cột nơi bạn phải nhập nghệ danh [để cho phép đặt tên mới] sau đó đếm cho nghệ danh. đã đưa vào một câu lệnh if cho istext để bảng không có chuỗi số không
josh nói.
Đây là mã VBA tôi đã viết ở trên
Sub ListUnique[] ‘ giả sử ActiveSheet
Dim A dưới dạng biến thể, v dưới dạng biến thể
Dim C As Collection, K As Collection, R As Range
Dim dataLastRow As Long, outputRow As Long, n As Long
Const dataColumn As Long = 1 ‘ dữ liệu trong cột A
Const outputColumn As Long = dataColumn + 1
Const dataFirstRow As Long = 2 ‘ hàng 1 là tiêu đề
dataLastRow = Ô[Hàng. Đếm, cột dữ liệu]. Kết thúc[xlUp]. Chèo thuyền
Đặt R = Range[Cells[dataFirstRow, dataColumn], Cells[dataLastRow, dataColumn]]
Một = R. Giá trị ‘ mảng giá trị dữ liệu
Đặt C = Bộ sưu tập mới ‘ các mục độc đáo
Đặt K = Bộ sưu tập mới ‘ số lượng của từng mục duy nhất
On Error Resume Next ‘ bỏ qua lỗi khi thêm giá trị dữ liệu lặp lại
Cho Mỗi v Trong A
v = CStr[v]
Nếu v = vbNullString Thì v = “BlankCell”
sai. Thông thoáng
C. Thêm mục. =v, chìa khóa. =v
Nếu Err = 0 thì
K. Thêm mục. =1, Phím. = v
Khác
n = K. Mục[v] + 1
K. Xóa v
K. Thêm mục. =n, Phím. = v
kết thúc nếu
tiếp v
Khi Lỗi GoTo 0
outputRow = Ô [Hàng. Đếm, đầu raCột]. Kết thúc[xlUp]. Chèo thuyền
Phạm vi [Các ô [dataFirstRow, outputColumn], các ô [outputRow, outputColumn]]. Thông thoáng
đầu raRow = dataFirstRow
For Each v In C ‘ xuất kết quả
Các ô [đầu raRow, đầu raCột] = v & ” = ” & K. mục[v]
đầu raRow = đầu raRow + 1
tiếp v
kết thúc phụ
James Stout nói.
Tôi cũng nhận thấy rằng giải pháp Power Query là giải pháp dễ triển khai nhanh nhất VÀ dễ dàng thêm nhiều dữ liệu hơn. Tôi cũng có thể thực hiện các thay đổi nhỏ đối với các truy vấn. Sếp đổi ý và muốn biết có bao nhiêu giao dịch theo sản phẩm
Chris Beacock nói.
Mình cũng dùng Power Query nhưng hơi khác
1. Chọn dữ liệu nguồn và chuyển đổi thành bảng
2. Tải dữ liệu vào nguồn truy vấn từ bảng/phạm vi
3. Xóa 2 cột cuối cùng
4. Chọn cột giai đoạn bán hàng và thực hiện một nhóm cơ bản theo
5. Tên cột mới = Số giao dịch;
6. Đóng và tải vào trang tính hiện có dưới dạng bảng
Saverio Gagliardi nói.
Macro phụ1[]
Ứng dụng. Cảnh báo hiển thị = Sai
Khi gặp lỗi Tiếp tục Tiếp theo
Trang tính[“1”]. Xóa bỏ
ActiveWorkbook. trang tính. Thêm vào
Bảng hoạt động. Tên = “1”
Trang tính [“Dữ liệu”]. Lựa chọn
Phạm vi [“A3”]. Lựa chọn
Dim TabRange As Range
Làm mờ TabCache dưới dạng PivotCache
Làm mờ TabDin dưới dạng PivotTable
Dim n As Integer
Đặt TabRange = Cells[3, 1]. Vùng hiện tại
Đặt TabCache = ActiveWorkbook. PivotCaches _
Tạo[SourceType. =xlDatabase, SourceData. =Phạm vi tab]
Trang tính[“1″]. Lựa chọn
Đặt TabDin = TabCache. TạoPivotTable _
[Bảng điểm đến. =Ô[1, 1], Tên bảng. =”1”]
Với ActiveSheet. PivotTable[“1”]. PivotFields["ID giao dịch"]
Định hướng = xlRowField
Vị trí = 1
Kết thúc với
Với ActiveSheet. PivotTable[“1”]. PivotFields["Giai đoạn bán hàng"]
Định hướng = xlRowField
Vị trí = 2
Kết thúc với
Bảng hoạt động. PivotTable[“1”]. RowAxisLayout xlTabularRow
Bảng hoạt động. PivotTable[“1”]. PivotFields["ID giao dịch"]. Tổng phụ = Mảng[Sai, _
Sai, Sai, Sai, Sai, Sai, Sai, Sai, Sai, Sai, Sai, Sai]
Phạm vi [“A12”]. Lựa chọn
Với ActiveSheet. PivotTable[“1”]
CộtGrand = Sai
RowGrand = Sai
Kết thúc với
Khi gặp lỗi Tiếp tục Tiếp theo
Trang tính [“Kết quả”]. Xóa bỏ
Khi gặp lỗi Tiếp tục Tiếp theo
Trang tính [“2”]. Xóa bỏ
ActiveWorkbook. trang tính. Thêm vào
Bảng hoạt động. Tên = “2”
Làm mờ TabDin2 dưới dạng PivotTable
Đặt TabDin2 = TabCache. TạoPivotTable _
[Bảng điểm đến. =Ô[1, 1], Tên bảng. =”2″]
Với ActiveSheet. PivotTable[“Tabela dinâmica1”]. Trường khối lập phương [ _
“[Khoảng thời gian]. [Giai đoạn bán hàng]"]
Định hướng = xlRowField
Vị trí = 1
Kết thúc với
Bảng hoạt động. PivotTable[“Tabela dinâmica1”]. Trường Pivot[ _
“[Khoảng thời gian]. [Sales Stage]. [Giai đoạn bán hàng]"]. PivotItems[ _
“[Khoảng thời gian]. [Giai đoạn bán hàng]. &[Chiến thắng]"]. Vị trí = 1
Bảng hoạt động. PivotTable[“Tabela dinâmica1”]. Khối lập phương. GetMeasure _
“[Khoảng thời gian]. [ID giao dịch]”, xlCount, “Số lượng giao dịch”
Bảng hoạt động. PivotTable[“Tabela dinâmica1”]. AddDataField ActiveSheet. _
PivotTable[“Tabela dinâmica1”]. CubeFields[“[Measures]. [ID giao dịch Soma de]”], _
“ID giao dịch Soma de”
Bảng hoạt động. PivotTable[“Tabela dinâmica1”]. CompactLayoutRowHeader = “Giai đoạn”
Với ActiveSheet. PivotTable[“Tabela dinâmica1”]
CộtGrand = Sai
RowGrand = Sai
Kết thúc với
Bảng hoạt động. PivotTable[“2”]. ChangePivotCache ActiveWorkbook. PivotCaches. Tạo nên _
[Loại nguồn. =xlDatabase, SourceData. =ThisWorkbook. Trang tính[“1”]. Phạm vi đã sử dụng, Phiên bản. =xlPivotTableVersion15]
Với ActiveSheet. PivotTable[“2”]. PivotFields["Giai đoạn bán hàng"]
Định hướng = xlRowField
Vị trí = 1
Kết thúc với
Bảng hoạt động. PivotTable[“2”]. PivotFields["Giai đoạn bán hàng"]. PivotItems[“Giành chiến thắng”]. _
Vị trí = 1
Bảng hoạt động. PivotTable[“2”]. AddDataField ActiveSheet. PivotTable[“2”]. _
PivotFields[“ID giao dịch”], “Số lượng giao dịch”, xlCount
Phạm vi [“B6”]. Lựa chọn
Với ActiveSheet. PivotTable[“2”]
CộtGrand = Sai
RowGrand = Sai
Kết thúc với
Trang tính[“1”]. Lựa chọn
ActiveWindow. Trang tính đã chọn. Hiển thị = Sai
Trang tính [“2”]. Lựa chọn
Trang tính [“2”]. Tên = “Kết quả”
Trang tính [“Kết quả”]. Lựa chọn
Trang tính [“Kết quả”]. di chuyển trước. =Trang tính[5]
‘ ActiveWorkbook. Tiết kiệm
Ứng dụng. Cảnh báo hiển thị = Đúng
kết thúc phụ
josh nói.
tôi muốn cung cấp một giải pháp vba cho vấn đề chính
đây là mã
Sub ListUnique[] ‘ giả sử ActiveSheet
Dim A dưới dạng biến thể, v dưới dạng biến thể
Dim C As Collection, K As Collection, R As Range
Dim dataLastRow As Long, outputRow As Long, n As Long
Const dataColumn As Long = 1 ‘ dữ liệu trong cột A
Const outputColumn As Long = dataColumn + 1
Const dataFirstRow As Long = 2 ‘ hàng 1 là tiêu đề
dataLastRow = Ô[Hàng. Đếm, cột dữ liệu]. Kết thúc[xlUp]. Chèo thuyền
Đặt R = Range[Cells[dataFirstRow, dataColumn], Cells[dataLastRow, dataColumn]]
Một = R. Giá trị ‘ mảng giá trị dữ liệu
Đặt C = Bộ sưu tập mới ‘ các mục độc đáo
Đặt K = Bộ sưu tập mới ‘ số lượng của từng mục duy nhất
On Error Resume Next ‘ bỏ qua lỗi khi thêm giá trị dữ liệu lặp lại
Cho Mỗi v Trong A
v = CStr[v]
Nếu v = vbNullString Thì v = “BlankCell”
sai. Thông thoáng
C. Thêm mục. =v, chìa khóa. =v
Nếu Err = 0 thì
K. Thêm mục. =1, Phím. = v
Khác
n = K. Mục[v] + 1
K. Xóa v
K. Thêm mục. =n, Phím. = v
kết thúc nếu
tiếp v
Khi Lỗi GoTo 0
outputRow = Ô [Hàng. Đếm, đầu raCột]. Kết thúc[xlUp]. Chèo thuyền
Phạm vi [Các ô [dataFirstRow, outputColumn], các ô [outputRow, outputColumn]]. Thông thoáng
đầu raRow = dataFirstRow
For Each v In C ‘ xuất kết quả
Các ô [đầu raRow, đầu raCột] = v & ” = ” & K. mục[v]
đầu raRow = đầu raRow + 1
tiếp v
kết thúc phụ
Mohankumar nói.
Tôi nghĩ bảng Pivot là giải pháp tốt nhất để xem bất kỳ định dạng nào anh ấy cần. Người bán hàng khôn ngoan, sản phẩm khôn ngoan, được, mất và triển vọng khôn ngoan…
Wiley Thomas nói.
1. Chèn bảng [TblRawData] cho dữ liệu hiện có – [A6]
2. Giai đoạn rõ ràng [H4. giá trị H6]
3. Chuyển đổi Giai đoạn Kết quả [H4] sang công thức duy nhất – tràn ra bên dưới
4. Thêm khu vực Ưu đãi duy nhất trong cột P
5. Chèn TblRawData[Deal ID] duy nhất vào P4 – tràn bên dưới
6. Tra cứu Giai đoạn bán hàng cho từng ID giao dịch duy nhất bằng cách sử dụng Chỉ mục/Kết hợp với Nếu để trống các hàng không có giá trị ID giao dịch. Công thức này được sao chép xuống 100 hàng để phù hợp với sự tăng trưởng trong các giao dịch
7. Sử dụng Countif để đếm các giao dịch duy nhất phù hợp với Giai đoạn bán hàng trong cột H – bước 4 ở trên. Kiểm tra giai đoạn trống và kết quả đếm trống
TblRawData tự động phát triển nếu dữ liệu mới được dán vào phạm vi bảng bên dưới. Giai đoạn bán hàng duy nhất và ID giao dịch tự động tăng với tốc độ tăng TblRawData. Giai đoạn bán hàng và số lượng giao dịch sẽ tăng lên đến giới hạn đã nêu ở trên
Liên kết để gửi -
https. //lái xe. Google. com/file/d/1d9DkrNQ-UlAtlA5ktGAi7VNYyT6D_NkE/view?usp=sharing
Wiley Thomas nói.
Quên đề cập đến - phiên bản này hoạt động trên Excel cho Mac. Không cần PowerQuery
Paolo nói.
Giải pháp của tôi
Tạo dữ liệu dưới dạng bảng
Sử dụng power pivot và thêm vào mô hình bảng này
Tạo một trục điện. trong các hàng sử dụng Giai đoạn bán hàng;
Sửa đổi cài đặt trường giá trị của trường Id giao dịch áp dụng Số lượng riêng biệt
qua
Peter nói.
Giải pháp của tôi
1. Tạo một cột bên cạnh dữ liệu có đầu là Đếm và chứa công thức. =IFERROR[IF[MATCH[A4,A. A,0]=ROW[],1,0],0]. Điều này gắn cờ các hàng có ID giao dịch duy nhất
2. Create a pivot table filter Count = 1, Rows Sales Stage and Values = Count of Deal ID
Tôi không rành về Power Query, tôi thấy các loại giải pháp này phù hợp với nhu cầu của mình
Peter nói.
Xin chào
PT
1. Đặt phạm vi dữ liệu thành bảng
2. Thêm cột trợ giúp có tên Số lượng giao dịch và công thức. =1/Countif[[ID giao dịch];[@[ID giao dịch]]]
3. Thực hiện PT với Giai đoạn bán hàng trong hàng và Tổng số giao dịch được tính theo giá trị
4. Xong
Daniel Valka nói.
Xin chào các bạn yêu thích Excel
Giải pháp đầu tiên – khá dễ dàng – của tôi là sử dụng Bảng tổng hợp [chọn tùy chọn “Thêm vào mô hình dữ liệu” khi tạo bảng tổng hợp] với 'Giai đoạn bán hàng' trong Hàng và 'Số lượng DealID riêng biệt' trong Giá trị. Xong
Nhưng để làm cho nó khó khăn hơn, tôi quyết định đi theo lộ trình VBA
điều kiện tiên quyết
1. Dữ liệu nguồn được chuyển thành Bảng Excel [với tên mặc định là “Table1”]
2. Đã bật Tham chiếu đến Thời gian chạy Microsoft Scripting Trong VB Editor [Công cụ -> Tài liệu tham khảo -> kiểm tra Thời gian chạy Microsoft Scripting]
Tổng quan cấp cao về mã
1. Sao chép dữ liệu CRM từ bảng trang tính vào mảng
2. Tạo từ điển DealID[key]-SalesStage[value] từ mảng này
3. Từ từ điển đầu tiên tạo ra từ điển thứ hai SalesStage[key]-Count[value]
4. Chuyển đổi cái này thành mảng và in ra ô đã chọn trong sổ làm việc
Liên kết đến tệp Excel
https. //1drv. ms/x/s. AvZS9AXuvdoEyU08I5xgeP94XMlK?e=089gLG
Liên kết đến tệp txt chứa mã VBA
https. //1drv. ms/t/s. AvZS9AXuvdoEyU_8emMsaEp03zLR?e=OKaZrZ
Đa-ni-ên
Ignacio nói.
Sao chép hai cột đầu tiên sang một trang tính khác áp dụng chức năng ĐỘC ĐÁO rồi sử dụng COUNT. NẾU để đếm số lần thắng, thua, v.v.
Ignacio nói.
Đối với thử thách thứ hai, hãy chuyển đổi phạm vi thành bảng Excel, nó sẽ tự động cập nhật nếu bạn thêm dữ liệu mới hoặc dữ liệu đã được sửa đổi
Deepak nói.
Xin chào,
Tôi đã sử dụng công thức này để lấy kết quả biến
=COUNTIFS[INDEX[UNIQUE[Dữ liệu. $A. $B],,2],$C3]
trong đó $C3 là tham chiếu của “Thắng”, $C4 = thua , $C5 = Triển vọng
nó hoạt động tốt
Trân trọng
con sâu
Tom nói.
Một thử thách thú vị, đặc biệt đối với một người như tôi vẫn đang học tất cả những gì Excel có thể làm
Giải pháp của tôi liên quan đến việc sử dụng vba, phạm vi động, VLOOKUP và xác thực dữ liệu. Bạn thêm ID giao dịch và Giai đoạn bán hàng mới thông qua biểu mẫu người dùng, sau đó thêm dữ liệu vào bảng Dữ liệu trên trang tính Dữ liệu. Tôi đã tạo một bảng tính có tên là Phép tính thực hiện rất nhiều công việc nặng nhọc là tóm tắt dữ liệu, giúp có thể tổng hợp các Giai đoạn bán hàng khác nhau và cuối cùng tạo ra kết quả trong bảng Kết quả
Mục tiêu của tôi là làm cho nó đủ đơn giản để bất kỳ ai dù có kiến thức cơ bản nhất về Excel cũng có thể dễ dàng thêm dữ liệu vào biểu mẫu và cập nhật kết quả mà không cần thay đổi hoặc xóa bất kỳ dữ liệu gốc nào được nhập trong bảng Dữ liệu. Liên kết đến tập tin của tôi ở đây
https. //www. dropbox. com/scl/fi/4ujazraxd4xxqiyk1ce2f/Data-Analysis-Challenge-Solution. xlsm?dl=0&rlkey=8prt49ltxap6q0yahpkzs46oj
Tom nói.
Ai đó trong các nhận xét đã sử dụng Bộ lọc nâng cao để trích xuất các bản ghi duy nhất. Tôi chưa bao giờ thấy điều này, vì vậy tôi quyết định thử. Thật tuyệt vời, nó thực sự giải quyết vấn đề trong khoảng 5 phút. Thanks
Tôi quyết định sửa lại giải pháp ban đầu của mình và kết hợp tùy chọn Bộ lọc nâng cao. Nó loại bỏ nhu cầu tạo một bảng tính bổ sung và một loạt mã VBA để tạo ID đại lý mới. Tôi cũng đã tạo một số mã để tự động cập nhật bảng Kết quả trên Bảng dữ liệu bất cứ khi nào cột D [Đơn vị] được cập nhật. Tất cả phần còn lại của mã VBA tôi tạo lần đầu tiên có thể được chuyển sang giải pháp thứ hai này, với một số cập nhật nhỏ. Ngoài ra, tôi đã giữ danh sách xác thực dữ liệu, phạm vi động, định dạng có điều kiện và bảng Kết quả trên trang tính Kết quả từ giải pháp đầu tiên. Liên kết đến giải pháp thứ hai là ở đây
https. //www. dropbox. com/scl/fi/amqf7x30x1dp8hu3w60tn/Data-Analysis-Challenge-Second-Method. xlsm?dl=0&rlkey=5yhm5nksc7bcu8r5liftdwxl5
Meni Porat nói.
Giải pháp cho Thử thách Phân tích Dữ liệu Excel
Xin chào,
Vì hầu hết những người tham gia đã trình bày các giải pháp bằng Power Query hoặc Pivot Table, nên tôi đã chọn giải quyết thách thức bằng một công thức
Nói chính xác hơn, hai công thức. cái đầu tiên sử dụng các mảng động mới và cái thứ hai – các hàm “truyền thống”, “kiểu cũ”
Cả hai giải pháp đều động, khi bạn thêm hàng mới vào tập dữ liệu gốc hoặc khi thêm danh mục mới [“Giai đoạn bán hàng”]
Cần phải nói, cả hai đều sử dụng tính năng Table của Excel
Mỗi giải pháp có hai công thức. cái đầu tiên hiển thị các danh mục duy nhất và cái thứ hai hiển thị số lượng Giao dịch trên mỗi Giai đoạn bán hàng
Tất nhiên, cũng cần nói rõ rằng mỗi công thức có thể được kéo xuống dưới khi các hàng mới được thêm vào bảng, do đó hiển thị các giải pháp động.
Bây giờ, sau phần giới thiệu dài, hãy bắt tay vào công việc
Giải pháp 1. Sử dụng mảng động
Công thức 1 hiển thị các danh mục duy nhất [“Thắng”, “Thua”…. ]
Vì tên của bảng tôi đã đặt cho tập dữ liệu là Table1,
Các công thức trong ô H4. H6 là
=UNIQUE[Bảng1[Giai đoạn bán hàng]]
Ô I4. I6 hiển thị số lượng Giao dịch trên mỗi Giai đoạn bán hàng
Công thức trong các ô này là
=COUNT[UNIQUE[FILTER[Table1[Deal ID],Table1[Sales Stage]=$H4]]]
Giải pháp 2. Sử dụng các hàm “truyền thống”
Công thức 1 hiển thị các danh mục duy nhất [“Thắng”, “Thua”…. ]
Vì tên của bảng tôi đã đặt cho tập dữ liệu là Table2,
Các công thức trong ô H4. H6 là
=IFERROR[INDEX[Bảng2[Giai đoạn bán hàng],MATCH[,COUNTIF[H$3. H3,Table2[Giai đoạn bán hàng]],]],””]
Ô I4. I6 hiển thị số lượng Giao dịch trên mỗi Giai đoạn bán hàng
=SUMPRODUCT[[Bảng2[Giai đoạn bán hàng]=$H4]/COUNTIFS[Bảng2[ID giao dịch],Bảng2[ID giao dịch],Bảng2[Giai đoạn bán hàng],Bảng2[Giai đoạn bán hàng]]]
P. S
Tôi nghĩ rằng vấn đề này có thể được giải quyết bằng Bộ lọc nâng cao, nhưng tôi chưa thử
Trân trọng,
meni porat
jim nói.
Nếu bạn thêm dấu # vào $H4 trong công thức đếm ở giải pháp thứ nhất thì bạn chỉ cần nhập một lần vào ô I4
Công thức sẽ tràn ra bao nhiêu ô tùy ý [không cần điền]Meni Porat nói.
Vâng, jim, tôi đã thấy lời giải thích của Jon
Tôi có Office 365 nên tôi chưa hoàn toàn quen thuộc với tất cả những điều phức tạp của Mảng động mới
BTW, bạn nghĩ gì về giải pháp thứ hai của tôi?
Alex nói.
Cách dễ nhất để làm điều này là Power Query. Tạo một bảng với tập dữ liệu, tải nó vào PQ, nhóm giai đoạn bán hàng và đếm các hàng riêng biệt. Đó là nó. Khi dữ liệu bổ sung và nghệ danh mới được thêm vào, PQ sẽ trích xuất dữ liệu này và tự động tải các giá trị mới – sau khi bạn đã làm mới dữ liệu. Đơn giản và hiệu quả. hạnh phúc xuất sắc
Steven Morath nói.
Chuyển đổi dữ liệu thành bảng–> Công thức duy nhất để nhận các giai đoạn trong trang tính –> =COUNT[UNIQUE[FILTER[tblSales[Deal ID],tblSales[Sales Stage]=B4]]] để nhận số lượng giao dịch
Cordula nói.
Có vẻ rất phức tạp trong Excel. Giải pháp của tôi là gấu trúc
nhập gấu trúc dưới dạng pd
xl = 'đầu vào/Phân tích dữ liệu-Thách thức – 2. xlsx'
df = pd. read_excel[xl, sheet_name=’Dữ liệu’, bỏ qua=2, usecols=’A. C']
dữ liệu được nhóm = pdf. groupby[[‘Giai đoạn bán hàng’]]. agg[count=['ID thỏa thuận', 'lần duy nhất']]
grouped_data
Mike nói.
Sử dụng control + T và tạo bảng hết dữ liệu
Trong ô bên cạnh Win nhập
=COUNTIF[Bảng4[Giai đoạn bán hàng],H4]. Kéo xuống. Khi mới
dữ liệu được thêm vào thêm Đề xuất vào danh sách đếm. Kéo công thức xuống
Điều này sẽ đưa ra số lượng Thắng, Thua, v.v.
Anjana nói.
Tôi muốn xác định nhiều ô [Ví dụ:. chi phí quản lý nào] được liên kết với con số cụ thể [Ví dụ:. Tổng chi phí quản lý] trong một bảng/sổ công việc khác. Khó sử dụng tiền lệ Trace khi hơn 20 ô được liên kết từ nhiều vị trí. Có cách nào để xác định nhiều ô trong một trang tính/sách khác không?
Md. Rabiul Alam nói.
5 không phải là câu trả lời đúng cho "Thắng", câu trả lời đúng phải là 6. Các mã 1001,1003,1005,1007,1010, 1011 đang có giai đoạn “Trúng”
MikeC nói.
Thay đổi tập dữ liệu thành bảng. Xác định Tên giao dịch mới bằng với ID giao dịch của hàng trong bảng. Tôi đã gọi bảng của mình là Dữ liệu, vì vậy Deal =Data[Deal ID] là định nghĩa tên. Làm điều tương tự cho Giai đoạn bán hàng;
Nhập công thức sau vào ô I4
SUM[1/COUNTIF[Giao dịch,Giao dịch]*IF[Doanh số=H4,1,0]]
H4 là tài liệu tham khảo cho Win. Kéo công thức xuống I5 và I6. Khi bảng cập nhật, công thức sẽ cập nhật. Nếu có thêm Giai đoạn bán hàng được thêm vào, công thức cũng sẽ hoạt động
MikeC nói.
Về nhận xét cuối cùng, không thể có hàng bảng trống
Charlie nói.
Mới bắt gặp câu hỏi tuyệt vời của bạn - một năm sau. Mặc dù nó đã được trả lời rất tốt ở trên nhưng tôi chỉ muốn tham khảo một giải pháp mà tôi đã sử dụng trong nhiều năm và nó thực sự hiệu quả với tôi. Tôi mất khoảng 1 phút để tìm lời giải cho cả hai câu hỏi [số duy nhất/số khác biệt cũng như số bổ sung dữ liệu. Đây rồi
1. Chuyển dữ liệu thành bảng [Ctr + T]
2. Thêm bảng vào Mô hình dữ liệu [Tôi đã sử dụng menu trục nguồn]
3. Tạo bảng tổng hợp [nhấp vào PivotTable trong cửa sổ bảng tổng hợp]
4. Sử dụng thước đo Số lượng riêng biệt trên DealID
5. Và cuối cùng, sao chép 'dữ liệu mới' và thêm vào bảng và nhấn làm mới. Và đó là nó
6. Nếu cần các danh mục mới, chỉ cần thêm dữ liệu thô và lặp lại bước 5 ở trên
Tx, Charlie
Joe Altamura nói.
Tôi đã định dạng dữ liệu thành một bảng trước. Hãy cho tôi biết bạn nghĩ gì về điều này
https. //www. dropbox. com/s/5henvrebqc2eug4/Data-Analysis-Challenge. xlsx?dl=0
Phil Rock nói.
Vâng đây là một vấn đề thú vị. Không cần bàn ở đây. Trên tab Dữ liệu, trong ô I4, mặc dù nó cũng hoạt động trong tab Kết quả
{=TÓM TẮT[–[B$4. GIÁN TIẾP[“B”&MAX[[A. A””]*[ROW[A. A]]]]=H4],–[1/COUNTIF[A$4. GIÁN TIẾP[“A”&MAX[[A. A””]*[ROW[A. A]]]],A$4. GIÁN TIẾP[“A”&MAX[[A. A””]*[ROW[A. MỘT]]]]]]]}
Sẽ thực hiện thủ thuật cho tất cả các tiêu chí. “Prospect” phải được nhập thủ công vào ô H7 và công thức có thể được kéo đơn giản cho tất cả các mục ở bên trái từ I4 trở xuống. Bất kỳ mục bổ sung nào được tự động lập bảng theo tổng sản phẩm giới hạn ở cột cuối cùng chứa dữ liệu thực tế. Đây là một hàm mảng, vì vậy ctrl+shift+enter
Maurits nói.
Chuyển đổi dữ liệu thành bảng 'Dữ liệu'
Tạo một cột E mới có tên là “kép”
Đặt vào cột mới công thức =E5=E4 trả về “TRUE” hoặc “FALSE”
Ở I4 đặt công thức COUNTIFS[Data[Sales Stage];H4;Data[Double];FALSE]
Bart Castro nói.
Tôi còn khá mới sử dụng Excel nhưng tôi rất thích câu hỏi này
Để hoàn thành câu hỏi này, tôi bắt đầu bằng cách chuyển dữ liệu thành một bảng [ctrl+T] và đặt tên cho các cột “ID giao dịch” và “Giai đoạn bán hàng” [chỉ để dễ sử dụng]. Tiếp theo, tôi đã tạo một bảng động bằng hàm offset. Tôi đã kết hợp điều này với chức năng duy nhất để phân tách từng ID giao dịch =UNIQUE[OFFSET[Deal_ID_and_Sales_Stage,0,0]]. Sau đó, tôi đã tạo số lần Thắng/Thua/Triển vọng bằng cách sử dụng hàm đếm =COUNTIF[[G52#],”Thắng”] trong đó G52# là vị trí của bảng động, duy nhất của tôi
Như tôi đã nói, tôi còn khá mới với excel nên nếu có bất cứ điều gì tôi có thể làm hiệu quả hơn, vui lòng cho tôi biết
Stefan Versace nói.
Tôi chỉ sử dụng công thức
=SUMPRODUCT[–[UNQIUE[A. B]=H4]]
Cái nào, đối với kịch bản này, hoạt động tốt?