macro excel để tính trung bình trên bảng tổng hợp ở dạng động

macro excel để tính trung bình trên bảng tổng hợp ở dạng động

chào mọi người

Tôi rất mới nâng cao macro, ai đó có thể giúp tạo macro để tính trung bình.
tôi có một danh sách các tên khu vực như được xác định bên dưới trong trang tính được gọi là Danh sách, số lượng luôn có thể tăng theo từng tháng
QTR
10Q1
10Q2
10Q3
10Q4
11Q1
11Q2
11Q3
11Q4

trên trang tính khác chỉ có dữ liệu và tên trang tính là Dữ liệu và trang tính này chứa bốn cột
Vùng, Tiểu vùng,QTR, Doanh thu

Tôi đã xây dựng một trục dựa trên bảng dữ liệu, bây giờ yêu cầu là macro phải đi đến bảng danh sách lấy qtr mới nhất (giá trị sẽ được nhập vào phạm vi bảng trụ (A4)) và từ qtr mới nhất/hiện tại thì macro sẽ lấy 3 qtr lùi lại và tính toán như hình bên dưới và phản ánh tương tự ở trục quay.

giả sử là vào quý 11
11Q3=11Q3+11Q2+11Q1+10Q4/4
11Q2=11Q2+11Q3+11Q1+10Q4/4
11Q1=11Q1+10Q2+10Q1+10Q3/4
phép tính sẽ thay đổi khi chúng ta tự động bước vào quý mới





Trả lời:

Điều này có thể được thực hiện bằng các công thức:

1.) Tìm kiếm QTR trong Danh sách trang tính và lấy số hàng:

A1 =MATCH(A4,Danh sách!A:A,0)

2.) Lấy từng giá trị từ trang Dữ liệu ở vị trí này và vị trí trước đó rồi thực hiện phép tính:

=INDEX(Dữ liệu!C:C,A1)+INDEX(Dữ liệu!C:C,A1-1)+INDEX(Dữ liệu!C:C,A1-2)+INDEX(Dữ liệu!C:C,A1-3) /4


hoặc

=SUM(OFFSET(Dữ liệu!C1,A1-3,0,3),OFFSET(Dữ liệu!C1,A1-4,0,1)/4)

Andreas.



Trả lời:

Cảm ơn vì những điều trên nhưng điều này chưa giải quyết được vấn đề của tôi.

Bảng dữ liệu của tôi sẽ như hình dưới đây

Vùng đất Tiểu vùng QTR Doanh thu
AAA QAZ 10Q1 1.000
AAA WSX 10Q1 2.000
EE EDC 10Q1 3.000
EE EDC 10Q2 4.000
HHH WSX 10Q3 5.000
SSS TGB 10Q4 6.000
QQQ YHN 10Q4 7.000
YYY UJM 11Q1 8.000
TTT người Bắc Âu 11Q1 9.000
UUU QW 11Q2 10.000
III phòng cấp cứu 11Q2 11.000
PP EDC 11Q3 12.000
UY RTY 11Q4 13.000

Dựa trên bảng trên, tôi đã xây dựng một trục và bây giờ tôi cần trường tính toán trục của mình để thêm qtr và tính tổng giá trị như hiển thị bên dưới

Định dạng xoay:

Nhãn hàng: vùng, tiểu vùng

Nhãn cột : QTR

Giá trị : Doanh thu

trong nhãn Cột (phải dựa trên trường tính toán), tiêu đề là

11Q3,11Q2,11Q1......

11Q3=11Q3+11Q2+11Q1+10Q4/4
11Q2=11Q2+11Q3+11Q1+10Q4/4
11Q1=11Q1+10Q2+10Q1+10Q3/4
Sau mỗi tháng, QTR sẽ chuyển sang QTR tiếp theo và trục quay sẽ tính toán dựa trên QTR hiện tại + 3 QTR trước đó và ngược lại theo cách tương tự

Tôi cần mọi thứ trong macro. Nếu bạn có giải pháp nào tốt hơn thì cho tôi biết



Trả lời:

Tôi không hiểu bạn muốn đạt được điều gì.

Tôi có thể xem bảng dữ liệu của bạn ở trên và nếu tôi sử dụng công thức của mình trên bảng này:

F1 =MATCH("11Q3",C:C,0)
F2 =INDEX(D:D,F1)+INDEX(D:D,F1-1)+INDEX(D:D,F1-2)+INDEX(D:D,F1-3)/4

Tôi nhận được kết quả: 35,25

Và vì không có giá trị nào khác trong bảng của bạn nên kết quả có đúng với "11Q3"... hay không?

Andreas.



Trả lời:

Tôi có đoạn mã dưới đây, điều này có thể khiến bạn hiểu

Kiểm tra phụ()
a = Trang tính("Pivot").Range("A1").Giá trị
Trang tính("Danh sách").Chọn
Cells.Find(what:=a, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
Sai, SearchFormat:=False).Kích hoạt
'lastrow = Phạm vi("A65000").End(xlUp).Select

a = ActiveCell
b = ActiveCell.Offset(-1)
c = ActiveCell.Offset(-2)
d = ActiveCell.Offset(-3)
e = 4

AVG = "(" & a & "+" & b & "+" & c & "+" & d & ")" & "/" & e
Hộp tin nhắn (AVG)


Trang tính("Xoay").Chọn
Phạm vi ("c4"). Chọn

ActiveSheet.PivotTables("PivotTable1").PivotFields("QTR").CalculateItems.Add Name:="AVGQTR11", Công thức:=AVG


Kết thúc phụ

Ở đây biến AVG không hoạt động, bạn có thể giúp tôi không

và tôi cũng muốn chạy cái này trong vòng lặp



Trả lời:

Được rồi, tôi hiểu, nhưng IMHO bạn không thể làm những việc như vậy vì 11Q3 không có trường nào trong bảng tổng hợp.

IMHO bạn chỉ có thể thực hiện phép tính với các công thức mà tôi đã hiển thị và đặt chúng vào một số ô bên dưới bảng tổng hợp của bạn.

Nhưng có lẽ tôi đã nhầm, tôi không phải là chuyên gia về bảng tổng hợp.

Andreas.

Tái bút: Đây là phiên bản cải tiến của mã của bạn:

Kiểm tra phụ()
Dim R Như Phạm Vi
Làm mờ AVG dưới dạng chuỗi
Đặt R = Trang tính("Danh sách"). Cột("C").Find(Trang tính("Pivot").Range("A1"), _
LookIn:=xlValues, LookAt:=xlWhole)
Nếu R không là gì thì
MsgBox "Không tìm thấy"
Thoát phụ
Kết thúc nếu
AVG = "=("
Đối với mỗi R trong phạm vi (R.Offset(-3, 0), R)
AVG = AVG & R & "+"
Kế tiếp
AVG = Left$(AVG, Len(AVG) - 1) & ")/4"
MsgBox AVG
Trang tính("Pivot").PivotTables("PivotTable1").PivotFields("QTR").CalculateItems.Add _
Tên:="AVGQTR11", Công thức:=AVG
Kết thúc phụ



Trả lời:

cảm ơn để tôi thử ..

11Q3 là ví dụ, có nghĩa là qtr sẽ thay đổi sau mỗi 4 tháng giống như khi chúng ta bước sang năm 2012, tháng 1 năm 2012 của tôi sẽ là 12Q1.

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