Cách tạo Pivot Table và các vấn đề liên quan

[tieuthubuongbinh]

Vui lòng cho mình hỏi 1 số câu hỏi liên quan đến pivot:

1- Có cách nào copy label (từ dòng trên xuống dòng dưới) thay vì dùng merge label trong option?
2- Mình dùng paste special để lấy nguyên bảng pivot ra 1 sheet khác nên không tự update dữ liệu được, vậy có cách nào lấy nguyên bảng pivot ra 1 sheet khác và khi pivot thay đổi dữ liệu thì sheet này sẽ tự động update giá trị theo?
3- Mình có xem qua hàm getpivotdata nhưng vẫn chưa biết cách làm, nhưng theo mình nghĩ có phải hàm này chỉ dùng để lấy dữ liệu tổng là chủ yếu? (ý mình là có thể dùng hàm này để giải quyết câu hỏi số 2 của mình không?

Anh chị nào pro về pivot, xin chỉ giáo....

 

[ptm0412]

1. Không có cách, trừ khi paste special value như câu 2
2. Paste bình thường sẽ được cập nhật
3. GetPivotData không nhất thiết phải gõ hàm, chỉ cần từ 1 ô ngoài bảng, gõ dấu =, dùng chuột chọn vào 1 ô trong bảng Pivot, nhấn enter.

 

[tieuthubuongbinh]

1. Không có cách, trừ khi paste special value như câu 2
2. Paste bình thường sẽ được cập nhật
3. GetPivotData không nhất thiết phải gõ hàm, chỉ cần từ 1 ô ngoài bảng, gõ dấu =, dùng chuột chọn vào 1 ô trong bảng Pivot, nhấn enter.

cám ơn bạn nhiều nhưng số 2: nếu paste bình thường thì :

- nếu ctrl all & paste: nó vẫn bị hiểu là pivot report nên khống khác sheet pivot
- nếu paste 1 phần nào đó trong bảng: nó hiện ra giá trị chứ không phải là hàm (nên cũng không tự update số được)

nhưng mình thử cách dùng getpivotdata (chịu khó làm từng ô) thì data sẽ tự chạy như ý mình, nhưng như vậy thì thủ công quá vì nếu bảng dữ liệu dài thì không thể làm cách này được. bạn có cao kiến gì xin chỉ giúp nhe, thanks!!!!
vd: =GETPIVOTDATA("Jan",Pivot!$A$3,"Activity","","Account","7065") : mình có thể thay "jan" , "activity", "account" thành 1 ô trong excel được không?

 

[ptm0412]

bạn xem lại trả lời câu 3.

mình có thể thay "jan" , "activity", "account" thành 1 ô trong excel được không

tất nhiên là được, tôi nghĩ bạn đang chỉ muốn trích xuất 1 phần của Pivot vào 1 mẫu báo cáo có sẵn, nếu bạn vẫn không làm được, gửi file lên tôi xem có giúp gì được không.

 

[tieuthubuongbinh]

mình làm theo cách bạn chỉ (câu 3) thì nó hiện ra công thức như vậy

anyway, bạn giúp mình cho file đính kèm nhe. Sheet mình cần làm là summary (vì từ đây mình sẽ đưa thêm dữ liệu năm cũ vào để vẽ chart so sánh, nhưng vì sheet data chưa được finalize (có thể sẽ bị chỉnh sửa số liệu) nên mình muốn các sheet link với nhau để khỏi phải thao tác lại

cám ơn bạn trước nhe

hix.... mình không biết cách attach file lên đây, chỉ mình với

 

[Nguyễn Xuân Sơn]

Xin chào 2 MrOkeBab;Mrptm0412,và diễn đàn
Tôi có file PV rất mong hướng dẫn cách tìm nhanh
có file kèm .Cám ơn nhiều

Vấn đề bạn Buttri đưa ra chắc nhiều bạn gặp phải: Đó là rất mất thời gian khi tìm một thông tin trên trường ( cột ) của bảng PV nếu nó có hàng ngàn dòng như file kèm theo bài của bạn Buttri, hàng ngày tôi cũng mất gian với vấn đề này.
vậy có bạn nào có cách tìm nhanh ( có thể gõ từ khóa hoặc gì đó .... ) để đỡ mất thời gian được không?
Rất mong xin được giúp đỡ. Xin cảm ơn các bạn!
Xin ( mạn phép ) gửi lại file của bạn buttri dưới đây

 

[kieuhaminh]

Mình có file này:
Sheet Database mình đã connect với một Table trong SQL. Bảng dữ liệu này thay đổi sau 15 phút (15 phút lại có thêm dữ liệu - tần số quét để ghi dữ liệu là 15 phút)
Sheet 500kV Parameter & 220kV Parameter là mình đã dùng Pivot table tạo ra được định dạng bảng trình bày theo ý mình mong muốn.
Mình có mấy câu hỏi mong các bạn chỉ giúp:
1. Trong Data range properties sao mình không chọn được Refresh Data 15 minute một lần mà toàn phải Click vào chữ Refresh? Có cách nào để làm Refresh tự động không
2. Trong hai Sheet 500kV Parameter & 220kV Parameter mình đã mất công trình bày như vậy rồi nhưng mỗi lần Refresh thì nó lại mất định dạng đó, mình lại phải căn chỉnh lại. Vậy có cách nào để không bị như vậy không?
3. Mình muốn giám sát dữ liệu trong từng cột. Nghĩa là khi có dữ liệu mới (sau mỗi lần Refresh) nếu giá trị mới đó mà lớn hơn một giá trị định trước thì sẽ phát ra âm thanh để cảnh báo chẳng hạn. Bạn nào có thể giúp mình được không?
Mình gửi file đính kèm theo, rất mong được sự giúp đỡ của các bạn. Thanks!
(Xin lỗi vì dung lượng file lớn hơn cho phép nên mình up tạm lên Mediafire và có link gửi kèm theo. Các bạn down từ đó hộ mình nhé)
http://www.mediafire.com/?efjmsmnpjem

 

[ptm0412]

Bạn tieuthubuongbinh xem file đính kèm
Vì bạn đang hỏi về Pivot nên trả lời bằng Pivot, thực ra có thể dùng công thức SumProduct ở bảng dưới.
Côngthức GetPivotTable() có thể thay tất cả tham chiếu row fields bằng cells, trừ field tính toán trong vùng Data.

 

[ptm0412]

Trả lời bạn Kieuhaminh:

1. Pivot không tự refresh, ăn gian bằng cách lợi dụng cái sự kiện refresh của sheet Database:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("500kV Parameter").PivotTables("PivotTable2").PivotCache.Refresh
Sheets("220kV Parameter").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

2. Vào Table Property của Pivot Table, bỏ chọn mục Auto Table Format. Lý do: Dòng số 8 (đã dấu)có dữ liệu quá dài, 1 trong những điều Auto Format làm là Auto Column Width

3. Dùng Conditional Formatting, đánh dấu ô có giá trị lớn hơn 1 số cho trước.

 

[anen_2512]

Pivot table thì mình biết làm cách đây khá lâu. Nhưng có 1 điểm mình không biết khắc phục hay PVT chỉ có như vậy thôi nữa. Mình ví dụ nhé: Giống như bảng của bạn tigertiger nhưng nếu thêm 1 vài dữ liệu ở sh DuLieu thì bên bảng Pivot table vẫn không đổi mà giữ nguyên như cũ. Bạn nào biết rành hơn về điều này chỉ mình với nhé. Đa tạ

 

[cadafi]

Pivot table thì mình biết làm cách đây khá lâu. Nhưng có 1 điểm mình không biết khắc phục hay PVT chỉ có như vậy thôi nữa. Mình ví dụ nhé: Giống như bảng của bạn tigertiger nhưng nếu thêm 1 vài dữ liệu ở sh DuLieu thì bên bảng Pivot table vẫn không đổi mà giữ nguyên như cũ. Bạn nào biết rành hơn về điều này chỉ mình với nhé. Đa tạ

Để giả quyết vấn đế này, Bạn hãy tạo Name động (dùng Offset) để excel tự điều chỉnh vùng data của Pivotable. Sau khi cập nhật thêm dòng/dữ liệu vào vùng data, bạn quay lại bảng Pivot, click chuột phải và chọn Refresh data!

 

[tieuthubuongbinh]

Bạn tieuthubuongbinh xem file đính kèm
Vì bạn đang hỏi về Pivot nên trả lời bằng Pivot, thực ra có thể dùng công thức SumProduct ở bảng dưới.
Côngthức GetPivotTable() có thể thay tất cả tham chiếu row fields bằng cells, trừ field tính toán trong vùng Data.

Cám ơn PTM nhiều lắm, mình đã dùng được hàm getpivotdata như bạn chỉ trong file rồi, nhưng sao khi mình copy sheet đó thành 1 worksheet khác thì lại bị báo #N/A toàn bộ?

còn về sumproduct, mình đã thử và vẫn dùng được mà không cần qua bước pivot, rất giúp ích cho mình, cám ơn PTM 1 lần nữa

 

[ptm0412]

khi mình copy sheet đó thành 1 worksheet khác thì lại bị báo #N/A toàn bộ?

Không rõ nguyên nhân, nhưng bạn có thể sửa lỗi hàng loạt bằng cách tô chọn vùng lỗi, nhấn F2, sau đó nhấn Ctrl + Enter.

 

[tunghexe]

Em đang bí khi thống kê lại số liệu bán của công ty. Mọi người có thể trả lời giùm em gấp được không ah?
Em thống kê lượng hàng xuất của mỗi chi tiết sản phẩm theo tuần. Ví dụ sản phẩm A gồm các chi tiết A1, A2, A3...
sản phẩm B gồm các chi tiết B1, B2, B3...
Với mỗi sản phẩm, em đã dùng PivotTable để thống kê. Tuy nhiên, khi thống kê lại trong 1 file tổng hợp thì lại gặp vấn đề: có những sản phẩm không được xuất trong vòng nhiều tuần. Ví dụ như sản phẩm A trong tuần thứ 25, 30, 34 không được xuất, tức là sẽ không hiện tuần thứ 25, 30 và 34 trên PivotTable thống kê của A.
Vậy em phải làm thế nào để trên file tổng hợp, ở tuần thứ 25, 30, 34 tương ứng với các chi tiết của A sẽ là số 0.
Em không thể copy paste được vì file quá lớn, trên 10.000 dòng. Xin mọi người ra tay chỉ giáo giúp em. Xin cảm ơn mọi người rất nhiều

 

[kieuhaminh]

1. Pivot không tự refresh, ăn gian bằng cách lợi dụng cái sự kiện refresh của sheet Database:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("500kV Parameter").PivotTables("PivotTable2").PivotCache.Refresh
Sheets("220kV Parameter").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

2. Vào Table Property của Pivot Table, bỏ chọn mục Auto Table Format. Lý do: Dòng số 8 (đã dấu)có dữ liệu quá dài, 1 trong những điều Auto Format làm là Auto Column Width

3. Dùng Conditional Formatting, đánh dấu ô có giá trị lớn hơn 1 số cho trước.

Cám ơn bạn PTM rất nhiều! Tuy nhiên ở câu hỏi thứ 3 tớ muốn tạo ra cái cảnh báo bằng âm thanh chứ không chỉ đơn thuần theo kiểu Conditional Formatting.
Đây là đoạn Code khi giá trị của một cell bất kỳ nào đó thay đổi mà lớn hơn giá trị đặt (577) thì Excel sẽ phát ra file âm thanh là "200.wav". Tuy nhiên tớ muốn sửa lại sao cho khi giá trị của một cell trong mỗi cột khác nhau khi thay đổi thỏa mãn điều kiện đặt thì sẽ phát các file âm thanh khác nhau. Như vậy có được không?

Private Sub Worksheet_Change(ByVal Target As Range)
  Application.EnableEvents = False
    With Target
      If .Value > 577 Then
         PlaySoundFile "C:\Windows\Media\220.wav"
      End If
    End With
  Application.EnableEvents = True
End Sub

Ah, còn một điều nữa mình muốn hỏi là:
Khi mình cho đoạn code Refresh vào Sheet Database để "ăn gian" thì đúng là tự đông Refresh được hai Sheet kia. Nhưng hai Sheet đó lại báo lỗi đoạn Code phát âm thanh. "Run-time Error '13' Type mis match" Debug thì chỉ vào dòng code: If .Value > 577 Then
Có cách nào sửa lỗi này không?
Mình thực ra không phải chuyên về lập trình, thế nên có gì mong được sự chỉ dẫn của các bạn. Thanks!

 

[ptm0412]

Phát ra âm thanh làm gì nếu không xác định được những ô chứa giá trị vượt? Chỉ nên dùng âm thanh phụ trợ cho conditional formatting.

Nếu vẫn muốn, thì để code vào sự kiện activate của từng sheet.

Nếu muốn tách từng cột thì viết code khác nhau cho mỗi cột, nhưng không dựa vào target mà dựa vào range. Range không được chứa ô có tiêu đề vì tiêu đề là text, gây lỗi type mismatch.

 

[xinhtuoi142]

Em đang nghiên cứu cách tạo Pivot table em có gửi file mà em tạo thử ra đây để mọi người giúp. Em muốn sau khi em sử dụng Pivot table thì cái bảng kết quả sẽ hiện ra mã hàng , thành tiền, và tổng số lượng.TRong cái bảng em tạo kết quả đã được như mong muốn nhưng mỗi mã hàng nó lại hiện ra thành hai dòng ( nhu ở trong file gửi kem`). Mọi người làm ơn chỉ hộ em làm sao để nó chỉ hiện ra 1dòng mà bao gồm cả hai thông tin là thành tiền và số lượng. cảm ơn mọi người rất nhiều.

 

[viendo]

Em đang nghiên cứu cách tạo Pivot table em có gửi file mà em tạo thử ra đây để mọi người giúp. Em muốn sau khi em sử dụng Pivot table thì cái bảng kết quả sẽ hiện ra mã hàng , thành tiền, và tổng số lượng.TRong cái bảng em tạo kết quả đã được như mong muốn nhưng mỗi mã hàng nó lại hiện ra thành hai dòng ( nhu ở trong file gửi kem`). Mọi người làm ơn chỉ hộ em làm sao để nó chỉ hiện ra 1dòng mà bao gồm cả hai thông tin là thành tiền và số lượng. cảm ơn mọi người rất nhiều.

Bạn chọn vào 1cell trong dòng bạn muốn ẩn. Nhấn chuột phải và chọn Hide.

 

[xinhtuoi142]

Em đang bí khi thống kê lại số liệu bán của công ty. Mọi người có thể trả lời giùm em gấp được không ah?
Em thống kê lượng hàng xuất của mỗi chi tiết sản phẩm theo tuần. Ví dụ sản phẩm A gồm các chi tiết A1, A2, A3...
sản phẩm B gồm các chi tiết B1, B2, B3...
Với mỗi sản phẩm, em đã dùng PivotTable để thống kê. Tuy nhiên, khi thống kê lại trong 1 file tổng hợp thì lại gặp vấn đề: có những sản phẩm không được xuất trong vòng nhiều tuần. Ví dụ như sản phẩm A trong tuần thứ 25, 30, 34 không được xuất, tức là sẽ không hiện tuần thứ 25, 30 và 34 trên PivotTable thống kê của A.
Vậy em phải làm thế nào để trên file tổng hợp, ở tuần thứ 25, 30, 34 tương ứng với các chi tiết của A sẽ là số 0.
Em không thể copy paste được vì file quá lớn, trên 10.000 dòng. Xin mọi người ra tay chỉ giáo giúp em. Xin cảm ơn mọi người rất nhiều


MỌI NGƯỜI GIÚP ĐỠ HỘ EM VÀ BẠN TUNGHEXE VẤN ĐỀ NÀY VỚI

 

[viendo]

Vậy em phải làm thế nào để trên file tổng hợp, ở tuần thứ 25, 30, 34 tương ứng với các chi tiết của A sẽ là số 0.

Pivot Table không hiển thị vì trong Database không có dữ liệu. Để giải quyết theo ý bạn thì bạn chèn thêm 1 dòng vào database của tuần đó với số lượng là 0. Khi đó trong PivotTable sẽ hiển thị là 0.