Bài tập excel phân tích dữ liệu

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

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

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

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?

Tôi có thể thực hành phân tích dữ liệu trong Excel ở đâu?

Những kỹ năng Excel nào được yêu cầu để phân tích dữ liệu?

Cách tốt nhất để thực hành Excel là gì?

5 kỹ năng Excel cơ bản là gì?