học excel nâng cao

Video phía dẫn

Tải tệp tin thực hành thực tế bên trên phía trên.

Bạn đang xem: học excel nâng cao

Mẹo và mẹo nhỏ nâng lên - Kết thích hợp từ khá nhiều tệp tin lại trở nên 1 file

Để gộp nhiều tệp tin lại cùng nhau, chúng ta cần phải có quá trình sau:

Bước 1: Đưa toàn bộ những tệp tin lại vô và một folder như hình dưới:

hình-gom-file-vao-thu-muc

Bước 2: Mở tệp tin Excel rỗng tuếch rời khỏi, bên trên phía trên chúng ta vô Data => Get Data => Chọn From File => From Folder

hình quá trình vô thư mục

Bước 3: Truy cập vô folder chứa chấp những file nhằm gộp và nhấn Open nhằm banh.

hình truy vấn vô thư mục

Bước 4: Tiếp theo dõi, chúng ta lựa chọn vô Combie and tranform data 

kích lựa chọn combie and transform data

Bước 5: Hiển thị vỏ hộp thoại Combie Files. Tại phía trên, chúng ta lựa chọn vô Sheet1 là Sheet chứa chấp tài liệu gộp và nhấn OK

hình lựa chọn sheet gộp file

Bước 6: Hiển thị skin Power Query Editor

giao diện power query

Bước 7: Tại phía trên, bạn phải tiến hành những thao tác sau:

- Để xóa cột bị thừa, chúng ta lựa chọn vô cột bại, nhấn con chuột nên và lựa chọn Remove
- Để xóa loại bị quá như Null: quý khách hàng lựa chọn vô cột bất kì chứa chấp null => tích lựa chọn vô cỗ thanh lọc ở kề bên cột hình hình ảnh cỗ lọc => quăng quật tích lựa chọn Null và nhấn OK.

hình hình ảnh tích lựa chọn null

- Để đưa title lên loại đầu, tớ vô thẻ Home => Use First Row as Headers
- Để xóa title bị quá từ những tệp tin hâu phương, chúng ta tích lựa chọn cỗ thanh lọc ở kề bên 1 cột bất kì => tích rắc tiêu đề => OK.

Bước 8: Sau Khi triển khai xong hoàn thành, chúng ta vô thẻ Home => Close & Load nhằm ra bên ngoài tệp tin Excel nhé!

Bước 9: Các tệp tin đã và đang được gộp trở nên một tệp tin hoàn hảo.

hình hình ảnh những tệp tin sau thời điểm vẫn gộp file

Lưu ý: Để cập nhật tệp tin mới mẻ thêm vô thư mục, bên trên tệp tin vẫn gộp chúng ta lựa chọn 1 dù bất kì, nhấn con chuột nên => Refresh.

Xem thêm

Mẹo và mẹo nhỏ nâng lên - Sử dụng Text đồ sộ Column

 Là dụng cụ thường xuyên tách những tài liệu trở nên cột riêng rẽ được chỉ định và hướng dẫn rõ ràng. 

Ví dụ: Trong ví dụ sau đây, bản thân cần thiết tách số liệu vô và một cột thanh lịch nhiều cột riêng không liên quan gì đến nhau.

hình hình ảnh tài liệu text đồ sộ column

Bước 1: quý khách hàng bôi thâm vùng tài liệu cần thiết tách, đôi khi kích con chuột vô Tab Data => Text đồ sộ Column

hình hình ảnh kích lựa chọn text đồ sộ column

Bước 2: Hiển thị rời khỏi vỏ hộp thoại Convert Text đồ sộ Column Wizard, chúng ta lựa chọn vô Delimited và nhấn Next

hình hình ảnh vỏ hộp thoại convert text đồ sộ column

Bước 3: Tiếp theo dõi lựa chọn vô dù Other, gõ dấu "," vô dù hoặc lựa chọn Comma.

Nếu ký tự động ngăn cơ hội là ký tự động không giống, chúng ta lựa chọn tùy lựa chọn tương tự động với ký tự động đó:

  • Tab: ngăn cách nhau chừng vì như thế phím tab.
  • Semicolon: ngăn cách nhau chừng vì như thế vết chấm phẩy.
  • Comma: ngăn cách nhau chừng vì như thế vết phẩy.
  • Space: ngăn cách nhau chừng vì như thế vết cơ hội.
  • Other: ký tự động không giống, nếu như chúng ta lựa chọn lựa chọn này chúng ta cần thiết nhập ký tự động không giống vô dù vuông ở kề bên.

và nhấn Next

hình hình ảnh lựa chọn vết tách

Bước 4: Tiếp theo dõi chúng ta lựa chọn vô General và nhấn Finish

Bạn sẽ tiến hành thành quả như mặt mũi dưới:

hình hình ảnh sau thời điểm tách cột dữ liệu

Mẹo và mẹo nhỏ nâng lên - Xóa độ quý hiếm bị trùng

Ví dụ: Ta tiến hành xóa tài liệu bị trùng vô bảng bên dưới đây:

hình hình ảnh dữ liệu

Bước 1: Bôi thâm vùng tài liệu, tiếp sau đó vô thẻ Data => remove duplicate

hinh-anh-chon-remove-duplicated

Bước 2: Chọn cột chúng ta muốn xóa độ quý hiếm bị trùng, tiếp sau đó nhấn OK.

Dữ liệu sau thời điểm xóa độ quý hiếm bị trùng

bảng tài liệu sau thời điểm xóa bị trùng

Mẹo và mẹo nhỏ nâng lên - Tự động điền dữ liệu

Ví dụ: Mình tiếp tục dùng mẹo nhỏ này nhằm tính Điểm trung bình nhanh chóng vô bảng tài liệu bên dưới đây:

hình hình ảnh tài liệu autofill

Bạn di con cái con chuột cho tới địa điểm cuối dù Điểm tầm của học viên thứ nhất, sinh ra vết nằm trong. quý khách hàng lưu giữ con cái trỏ con chuột và kéo xuống cho tới học viên ở đầu cuối.

hình sau khi sử dụng autofill

Mẹo và mẹo nhỏ nâng lên - Sử dụng Flash Fill

Flash Fill là dụng cụ giúp cho bạn tiến hành những thao tác tái diễn những thao tác tuy nhiên chúng ta đã từng một cơ hội nhanh gọn.

Ví dụ: Mình tiếp tục vận dụng Flash Fill mang lại bảng tài liệu bên dưới đây:

hình hình ảnh tài liệu flash fill

Ở bảng tài liệu loại nhất, nhằm tách bọn họ thương hiệu rời khỏi 2 cột riêng rẽ biệt. quý khách hàng thực hiện như sau:

Tại Họ thương hiệu thứ nhất, chúng ta ghi chép bọn họ rời khỏi vô cột Họ. Để hiển thị những bọn họ những dù sau, chúng ta nhấn tổng hợp phím CTRL + E

hình tài liệu cột họ

Tương tự động, ở cột thương hiệu. quý khách hàng cũng gõ thương hiệu người loại nhất rời khỏi và nhấn CTRL + E nhằm hiện nay thị những thương hiệu sau.

hình hình ảnh hiển thị tên

Tương tự động bảng loại nhị chúng ta cũng thực hiện tương tự động vậy.

hình hình ảnh vì như thế loại hai

Lưu ý: Trường thích hợp ở cột mon vì như thế thời hạn loại thứ nhất ngày và mon trùng nhau. Vì vậy, các bạn sẽ ghi chép tiếp mon thứ hai và nhấn CTRL + E.

Mẹo và mẹo nhỏ nâng lên - Sử dụng Custom Lists

Là dụng cụ nhằm bố trí những tài liệu vô bảng tính.

Ví dụ: Mình tiếp tục áp sử dụng dụng cụ này nhằm hiển thị và bố trí tài liệu mặt mũi dưới:

hình tài liệu custiom list

Để dùng dụng cụ này, chúng ta vô File => Options => Advances => Chọn vô mục General => click vô Edit Custom Lists...

hình hình ảnh thiết lập dụng cụ custom list

Hiển thị vỏ hộp thoại Custom Lists, ở mục Custom lists chúng ta lựa chọn NEW LIST, mục Import list from cells chúng ta bôi thâm vùng dữ liệu

hình hình ảnh vỏ hộp thoại custom list

Tiếp theo dõi, lựa chọn Import và nhấn Ok nhằm triển khai xong.

Sau Khi vẫn thiết lập dụng cụ Custom Lists xong, chúng ta lựa chọn 1 dù bất kì và gõ bọn họ thương hiệu bất kì vô vùng tài liệu.

Tiếp theo dõi, dịch chuyển con cái con chuột cho tới cuối dù mới mẻ gõ bọn họ thương hiệu bất kì, hiển thị vết nằm trong và chúng ta kéo xuống nhằm hiển thị những thương hiệu không giống.

hình hiển thị list dữ liệu

Mẹo và mẹo nhỏ nâng lên - Sử dụng Freeze Panes

Đây là dụng cụ hùn cố định và thắt chặt cột loại theo dõi đòi hỏi.

Ví dụ: Mình tiếp tục dùng dụng cụ này mang lại tài liệu mặt mũi dưới:

dữ liệu freeze panes

Để cố lăm le loại xài đề, chúng ta thực hiện như sau:

Đầu tiên, chúng ta lựa chọn loại bên dưới loại title, tiếp sau đó vô thẻ View => Freeze Panes

hình cố định và thắt chặt loại xài đề

Để cố lăm le cột đầu tiên, chúng ta thực hiện như sau:

Đầu tiên, lựa chọn cột phía bên phải cột thứ nhất, tiếp sau đó vô thẻ View => Freeze Panes

hình cố định và thắt chặt cột

Để cố lăm le cả loại title và cột đầu tiên, chúng ta lựa chọn vô dù uỷ thác thân thiện loại sau title và cột phía bên phải cột thứ nhất. Trong ví dụ là dù B4, tiếp sau đó vô View, lựa chọn Freeze Panes.

hình cố định và thắt chặt cột và dòng

Mẹo và mẹo nhỏ nâng lên - New Window and Arrange

Đây là dụng cụ, hoàn toàn có thể mở đối chiếu 2 hoặc nhiều tệp tin bên trên và một lúc.

Để coi tuy vậy song 2 trang tính và một lúc:

Tại trang tính thứ nhất, chúng ta lựa chọn vô View => Arrange All. Tiếp theo dõi, lựa chọn Vertical vô vỏ hộp thoại Arrange Windows, tiếp sau đó nhấn OK.

hình hình ảnh đối chiếu 2 file

Mẹo và mẹo nhỏ nâng lên - Tables

Tables là một đối tượng người dùng được gọi là được chấp nhận chúng ta quản lý và vận hành nội dung phía bên trong một cơ hội song lập với phần còn sót lại của tài liệu trang tính.

Ví dụ: Mình tiếp tục tiến hành tạo nên bảng vô vùng tài liệu bên dưới đây:

hình hình ảnh tài liệu table

Các bước thực hiện:

Bước 1: Chọn một dù bất kì vô vùng tài liệu.

Bước 2: Vào thẻ Insert => Table. Hiển thị vỏ hộp thoại Create Table chứa chấp phạm vi vùng tài liệu.

hình quá trình tạo nên bảng table

Bước 3: Nhấn Ok để tạo nên bảng tài liệu.

hình hình ảnh tables

Mẹo và mẹo nhỏ nâng lên - Data Analysis 

Là dụng cụ chyên phân tích tài liệu theo không ít dạng một cơ hội nhanh gọn và đúng chuẩn .

Ví dụ: Mình tiếp tục vận dụng vô nhằm phân tách và đo đếm tài liệu sau:

hình tài liệu data analyze

Đầu tiên, vô thẻ Home => Analyze Data. Hiển thị rời khỏi những dạng phân tách theo dõi dữ liệu

các dạng phân tích

Tiếp theo dõi, các bạn sẽ lựa chọn 1 dạng thích hợp theo dõi đòi hỏi và chọn Insert Chart ở bên dưới dạng phân tách bại.

chọn dạng thích hợp mang lại dữ liệu

Mẹo và mẹo nhỏ nâng lên - Loại tài liệu nhiều mẫu mã thức

Là dụng cụ chuyển thay đổi tài liệu này thanh lịch nhiều hình thức tài liệu khác phong phú và đa dạng.

Ví dụ: Mình tiếp tục vận dụng dụng cụ này nhằm gửi thay tên công ty lớn thanh lịch loại dạng CP vô tài liệu bên dưới đây:

hình hình ảnh thương hiệu công ty

Các bước thực hiện:

Bước 1: Tạo bảng mang lại vùng tài liệu bằng phương pháp lựa chọn 1 dù bất kì vô vùng tài liệu, tiếp sau đó nhấn Ctrl + T nhằm hiển thị bảng Create Table, bôi thâm vùng phạm vi tài liệu và nhấn Ok muốn tạo bảng.

Bước 2: Bôi thâm vùng tài liệu chứa chấp thương hiệu công ty lớn. Sau bại, vô thẻ Data => Stock 

hình quá trình quy đổi dữ liệu

Bước 3: Bảng tài liệu sau thời điểm được quy đổi thanh lịch CP hội chứng khoáng 

hình hình ảnh đem phiếu hội chứng khoán

Bây giờ, nên lựa chọn dù đem loại tài liệu. Các ‘Add Column‘ tiếp tục xuất hiện nay.

hình add column

Hiển thị rời khỏi những dạng tài liệu. Tại phía trên, chúng ta cũng có thể lựa chọn đem dạng theo dõi như đòi hỏi nhé!

Ví dụ: quý khách hàng quan hoài cho tới việc lựa chọn ra nguyên tố ra quyết định cần thiết nhất là Giá. quý khách hàng tiếp tục lựa chọn vô Price

hình hiển thị giá chỉ dữ liệu

Mẹo và mẹo nhỏ nâng lên - Tạo Drop list (danh sách thả xuống)

Để tạo nên list tài liệu thả xuống vô Excel, bản thân tiếp tục dùng dụng cụ Data Validation.

Ví dụ: Mình ham muốn tạo nên drop list phụ thuộc tài liệu sau:

hình tài liệu ham muốn drop list

Các bước thực hiện: 

Bước 1: Chọn một dù rỗng tuếch bất kì nhằm chứa chấp list tài liệu thả xuống.

Bước 2: Vào thẻ Data => Data Validation. Hộp thoại Data Validation hiện nay ra

hình hình ảnh tạo nên list thả xuống

Bước 3: Tại mục Allow, lựa chọn vô List. Mục Source, bôi thâm vùng tài liệu ham muốn hiển thị list list. Và nhấn OK.

hình hình ảnh tạo nên tùy lựa chọn vô data validation

Bước 4: Danh sách tài liệu Họ và Tên sau thời điểm vẫn tạo

hình hình ảnh list list bọn họ và tên

Mẹo và mẹo nhỏ nâng lên - Tạo thông tin dù xác thực dữ liệu

Ví dụ: Tạo thông tin lượng nhập vô nên to hơn 0 vô tài liệu sau:

dữ liệu thông báo

Các bước thực hiện:

Bước 1: Bôi thâm những loại vô cột lượng.

Bước 2: Vào thẻ Data => Data Validation. Hiển thị hộp thoại Validation, chúng ta vô mục Input Message

hình hình ảnh vô mục input message

Bước 3: Tích lựa chọn vô dù Show input message when cell is selected. Mục Tilte, chúng ta ghi title thông tin. Mục Input message, chúng ta ghi nội dung thông báo

hình tạo nên thông tin vô data validation

Bước 4: Nhấn Ok nhằm triển khai xong hiện thông báo.

hình hiển thị thông báo

Mẹo và mẹo nhỏ nâng lên - Hàm đối chiếu logic (Hàm IF)

Là hàm được chấp nhận chúng ta tiến hành phép tính đối chiếu logic thân thiện một độ quý hiếm với độ quý hiếm bạn muốn. (Xem cụ thể về hàm)

Ví dụ: Mình đem bảng tài liệu mặt mũi dưới:

dữ liệu hàm if

Yêu cầu: Tính đơn giá chỉ sản phẩm, biết rằng: Nếu sản phẩm nằm trong group X thì giá chỉ là 2000đ, còn sót lại là giá chỉ 2500.

Để xử lý tài liệu, bên trên dù E4 bản thân gõ công thức sau: =IF(C4="X",2000,2500)

hình luật lệ tính hàm if

Mẹo và mẹo nhỏ nâng lên - Hàm kiểm điểm tài liệu đem ĐK (Hàm Countifs)

Ví dụ: Đếm những ĐK được đòi hỏi kể từ tài liệu sau:

hình tài liệu countifs

Đếm những thành phầm đem số chi phí bên trên 300: =COUNTIFS($D$3:$D$13,">300")

hình kiểm điểm thành phầm ĐK 1

Xem thêm: câu nói hay trong phim nhà bà nữ

Đếm những thành phầm sữa đem số chi phí bên trên 300: =COUNTIFS($C$3:$C$13,"Milk",$D$3:$D$13,">300")

hình kiểm điểm thành phầm ĐK 2

Đếm những thành phầm sữa của siêu thị MiniMart đem số chi phí bên trên 300: =COUNTIFS($C$3:$C$13,"Milk",$B$3:$B$13,"MiniMart",$D$3:$D$13,">300")

hình kiểm điểm thành phầm ĐK 3

Mẹo và mẹo nhỏ nâng lên - Hàm tính tổng tài liệu đem ĐK (Hàm Sumifs)

Ví dụ: Mình ham muốn tính tổng Số chi phí vô vùng tài liệu bên dưới đây:

hình tài liệu tổng

Để tính tổng chi phí mua sắm ở siêu thị FoodMart, chúng ta gõ công thức sau: =SUMIFS($E$4:$E$14,$C$4:$C$14,"FoodMart")

hình tài liệu tổng

Mẹo và mẹo nhỏ nâng lên - Hàm dò thám mò mẫm tài liệu theo dõi đối tượng người dùng (Hàm Vlookup)

Ví dụ: Mình đem bảng tài liệu sau:

hình tài liệu dò thám tìm

Yêu cầu: Dựa vô Họ và thương hiệu vẫn mang lại hãy dò thám mò mẫm Giới tính, Lý thuyết, Thực hành, Tổng điểm của những người bại phụ thuộc bảng tài liệu.

Để mò mẫm Giới tính, chúng ta gõ công thức sau: =VLOOKUP($H$4,B4:F10,2,0)

hình hình ảnh mò mẫm giới tính

Để mò mẫm lý thuyết, chúng ta cũng ghi chép công thức như mò mẫm nam nữ và địa điểm cột hiển thị là 3: =VLOOKUP($H$4,B4:F10,3,0)

hình hình ảnh điểm lý thuyết

Tương tự động chúng ta cũng mò mẫm điểm thực hành thực tế, tổng điểm như thế.

hình hình ảnh sử dụng hàm vlookup

Mẹo và mẹo nhỏ nâng lên - Hàm Index & Match

Index là hàm trả về độ quý hiếm của một thành phần mảng hoặc một mảng.

Match là hàm mò mẫm kiếm địa điểm của một tài liệu vô vùng tài liệu.

Ví dụ: Mình tiếp tục dùng nhị hàm bên trên vô ví dụ sau: 

dữ liệu hàm index match

Yêu cầu: Tìm Số lượng phụ thuộc ĐK sau:

hình hình ảnh ĐK index match

Để mò mẫm con số phụ thuộc ĐK bên trên, chúng ta gõ công thức sau: =INDEX($E$4:$E$12,MATCH(1,(I6=B4:B12)*(I7=C4:C12)*(I8=D4:D12),0),1)

hình hình ảnh tính số lượng

Mẹo và mẹo nhỏ nâng lên - Mảng động

Cho luật lệ tất cả chúng ta trả về nhiều độ quý hiếm từ 1 hàm duy nhất, bởi vậy trước đó tất cả chúng ta chỉ trả về một thành quả từ 1 hàm và vấn đề đó không thể trúng nữa nên đem 6 hàm mới mẻ cỗ thanh lọc hiển thị một trật tự bố trí và có một không hai : Filter, Randarray, Sequence, Sort, Sortby, Unique,…

  • Filter: được chấp nhận chúng ta thanh lọc độ quý hiếm dựa vào điều kiện
  • Sort: Sắp xếp những mảng theo dõi trật tự.
  • Unique: nhận list những độ quý hiếm duy nhất
  • Sortby: bố trí theo dõi tính năng ví dụ tăng cột này và rời cột không giống.
  • Sequence: được chấp nhận chúng ta tạo nên một list những số liên tục vô một mảng, ví dụ điển hình như một, 2, 3, 4
  • Randarray: tạo nên một phạm vi độ quý hiếm tình cờ.
  • Show Formulas: Hiển thị toàn bộ công thức và trang tính. 

Ví dụ: Cho tài liệu như sau:

dữ liệu mảng động

  • Sử dụng hàm Filter: 

Yêu cầu: Lọc rời khỏi những thành phầm 01 phụ thuộc tài liệu trên:

Bạn gõ như sau: =FILTER($A$2:$D$20,$C$2:$C$20="SP 01","")

Giải thích: $A$2:$D$20: Vùng dữ liệu
                  $C$2:$C$20: Cột tài liệu chứa chấp ĐK và ĐK vô đòi hỏi bên trên là SP 01.

hình hình ảnh hàm filter

  • Sử dụng hàm Sort: 

Yêu cầu: Hãy bố trí trật tự tăng dần dần theo dõi cột Sản phẩm:

Bạn gõ như sau: =SORT($A$3:$D$20,3,1)

Giải thích: $A$3:$D$20: Phạm vi vùng tài liệu.
                 3: Vị trí cột vô vùng tài liệu ham muốn bố trí.
                1: Sắp xếp tăng dần dần.  

hình dùng hàm sort

  • Sử dụng hàm Unique:

Yêu cầu: Hiển thị độ quý hiếm có một không hai vô cột Sản phẩm:

Bạn gõ như sau: =UNIQUE($C$3:$C$20,FALSE)

Giải thích: $C$3:$C$20: Phạm vi tài liệu vô cột Sản phẩm.
                  FALSE: Sắp xếp theo dõi sản phẩm.
            

hình sử dụng hàm unique

  • Sử dụng hàm Sortby:

Yêu cầu: Hiển thị tài liệu được bố trí theo dõi đội giá trị vô cột thành phầm và tiêu giảm giá trị vô cột Doanh thu.

Bạn gõ như sau: =SORTBY($A$3:$D$20,$C$3:$C$20,1,$D$3:$D$20,-1)

Giải thích: $A$3:$D$20: Phạm vi vùng tài liệu.
                 $C$3:$C$20: Phạm vi tài liệu của cột Sản phẩm.
                 1: Sắp xếp độ quý hiếm theo dõi tăng dần dần.
                 $D$3:$D$20: Phạm vi tài liệu vô cột Doanh thu.
                 -1: Sắp xếp độ quý hiếm theo dõi rời dần dần.

hình hình ảnh sử dụng sortby

  • Sử dụng hàm Sequence:

Yêu cầu: Tạo rời khỏi một mảng bao gồm 5 sản phẩm và 4 cột

Bạn gõ công thức sau: =SEQUENCE(5,4)

hình sử dụng hàm sequence

  • Sử dụng hàm Randarray: 

Yêu cầu: Tạo rời khỏi mảng độ quý hiếm vẹn toàn tình cờ bao gồm 5 sản phẩm, 3 cột, chính thức kể từ độ quý hiếm 1, kết đôn đốc là độ quý hiếm 100.

Bạn gõ công thức sau: =RANDARRAY(5,3,1,100,TRUE)

hình hình ảnh sử dụng hàm randarray

Mẹo và mẹo nhỏ nâng lên - Hiển thị công thức

Để ham muốn coi vô vùng tài liệu bại đem chứa chấp hàm hay là không hoặc công thức muốn tạo rời khỏi thành quả này là gì thì bạn phải nhằm chính sách coi công thức vì như thế quá trình như sau:

Đầu tiên, lựa chọn trang tính ham muốn hiển thị công thức. Tiếp theo dõi, vô thẻ Formulas => Chọn vô Show Formulas.

hình hiển thị công thức

Mẹo và mẹo nhỏ nâng lên - Sử dụng hàm Filter thanh lọc tài liệu.

(Xem cụ thể về hàm)

Ví dụ: Cho vùng tài liệu như sau:

dữ liệu dụng cụ filter

Yêu cầu: Lọc rời khỏi điểm số phần Thực hành to hơn 7.

Để thanh lọc rời khỏi điểm số theo dõi như đòi hỏi đề bài bác. quý khách hàng tiến hành quá trình sau:

Bước 1: quý khách hàng bôi thâm vùng tài liệu ham muốn thanh lọc. Tiếp theo dõi vô thẻ Data => Filter.

Bước 2: Chọn vô cỗ thanh lọc ở kề bên dù Thực hành.

hình lựa chọn cỗ lọc

Bước 3: Chọn vào Number Filters => Greater Than Or Equal To...

hình hình ảnh lựa chọn ĐK lọc

Bước 4: Hiển thị rời khỏi vỏ hộp thoại Custom AutoFilter. Tại phía trên, chúng ta gõ ĐK tuy nhiên đề đòi hỏi.

hình hình ảnh gõ ĐK yêu thương cầu

Bước 5: Nhấn Ok nhằm triển khai xong.

hình hình ảnh dùng dụng cụ filter

Mẹo và mẹo nhỏ nâng lên - Lọc tài liệu theo không ít ĐK (Hàm Advanced Filter).

(Xem tăng về hàm).

Ví dụ: Mình đem bảng tài liệu như mặt mũi dưới:

hình tài liệu advances

Yêu cầu: Dựa vô ĐK sau đây nhằm thanh lọc rời khỏi tài liệu theo dõi đòi hỏi.

điều khiếu nại lọc

Để thanh lọc tài liệu dựa vào nhiều ĐK đề mang lại như bên trên chúng ta thực hiện như sau:

Đầu tiên, vô thẻ Data => Advanced. Hiển thị vỏ hộp thoại Advanced Filter

Tiếp theo dõi, Hộp thoại Advanced Filter hiện rời khỏi. Tại phía trên, chúng ta tiến hành như sau:

Tích lựa chọn vô mục Copy đồ sộ another location, mục List range: quý khách hàng bôi thâm vùng tài liệu, mục Criteria range: quý khách hàng bôi thâm vùng chứa chấp ĐK tài liệu, mục Copy to: quý khách hàng lựa chọn dù hiển thị thành quả.

hình thao tác vỏ hộp thoại advances

Nhấn Ok để triển khai xong.

hình hình ảnh triển khai xong cỗ thanh lọc advances

Mẹo và mẹo nhỏ nâng lên - Sử dụng cỗ thanh lọc Slicer

Ví dụ: Mình tiếp tục sử dụng dụng cụ slicer nhằm thanh lọc tài liệu sau:

hình tài liệu slicer

Các bước thực hiện:

Bước 1: Chọn một dù bất kì vô bảng tài liệu.

Bước 2: Trong tab Table Design, lựa chọn vào Insert Slicer.

bước lựa chọn vô insert slicer

Bước 3: Chọn vô ngôi trường tài liệu ham muốn thanh lọc vô bảng Insert Slicer.

hình lựa chọn ngôi trường ham muốn lọc

Bước 4: Nhấn OK nhằm hiển thị slicer ngôi trường tài liệu vẫn lựa chọn.

hình slicer ngôi trường tài liệu ham muốn hiển thị

Bước 5: Chọn một hoặc nhiều nút vô vỏ hộp Slicer nhằm hiển thị tài liệu theo dõi như đòi hỏi.

hình lựa chọn nút slicer

Mẹo và mẹo nhỏ nâng lên - PivotTable

PivotTable hùn phân tách và tóm lược tài liệu nhằm chúng ta cũng có thể tổ hợp toàn bộ tài liệu theo dõi đòi hỏi. 

Ví dụ: Dùng PivotTable nhằm phân tách tài liệu sau:

hình tài liệu pivottable

Các bước tiến hành phân tách dữ liệu:

Bước 1: Chọn 1 dù bất kì vô bảng tài liệu.

Bước 2: Vào thẻ Insert => PivotTable. Hộp thoại PivotTable sinh ra.

Tại phía trên, mục Table/Range: chứa chấp vùng tài liệu mình muốn phân tách. Mục Choose where you want the pivotTable đồ sộ be placed: quý khách hàng tích lựa chọn New Worksheet: nhằm hiển thị thành quả thanh lịch sheet mới mẻ hoặc Existing Worksheet: hiển thị thành quả bên trên sheet chứa chấp tài liệu gốc và lựa chọn dù nhằm hiển thị thành quả bên trên mục Location.

thiết lập tùy lựa chọn pivot table

Bước 3: Bảng PivotTable sinh ra. Tại phía trên, ví dụ mình đang có nhu cầu muốn tính con số của môn học tập tuy nhiên học viên vẫn đăng kí.

hình hình ảnh hiển thị con số đăng kí môn

Mẹo và mẹo nhỏ nâng lên - Pivot Table Slicers & Timelines

Table Slicer: là dụng cụ nhằm thanh lọc tài liệu theo dõi một ngôi trường nào là bại vô bảng.

Timelines: là dụng cụ hùn thanh lọc tài liệu theo dõi đàng thời hạn.

Để dùng dụng cụ này, bản thân tiếp tục ví dụ bảng PivotTable ở ví dụ trên:

Đầu tiên, các bạn sẽ lựa chọn vô địa điểm bất kì vô bảng PivotTable nhằm hiển thị rời khỏi thẻ PivotTable Analyze.

Tiếp theo dõi, bên trên thẻ PivotTable Analyze, chúng ta lựa chọn vô Insert slicer và lựa chọn vô những ngôi trường tài liệu ham muốn thanh lọc vô bảng Insert Slicer.

hình hình ảnh lựa chọn ngôi trường hiển thị

Nhấn OK nhằm hiển thị slicer ngôi trường tài liệu vẫn lựa chọn.

trường tài liệu hiển thị slicer

Chọn một hoặc nhiều nút vô hộp Slicer nhằm hiển thị tài liệu theo dõi như đòi hỏi.

hình lựa chọn nút slicer pivottable

Tiếp theo dõi dụng cụ Timelines, chúng ta cũng lựa chọn vô Insert Timelines. Chọn vô ngôi trường thời hạn mình muốn thanh lọc.

hình lựa chọn ngôi trường thời gian

Và nhấn Ok. Sau bại, bản thân tiếp tục lựa chọn vô thời hạn ham muốn hiển thị tài liệu nhé!

hình hiển thị tài liệu timeline

Mẹo và mẹo nhỏ nâng lên - Hiển thị độ quý hiếm đo lường và tính toán.

Bạn ham muốn đánh giá những độ quý hiếm hiển thị những dạng luật lệ tính không giống nhau mang lại bảng tổ hợp. 

Ví dụ: mình đang có nhu cầu muốn hiển thị xác suất con số đăng kí môn học tập phụ thuộc bảng PivotTable bên trên. Cách chế biến như sau:

Đầu tiên, các bạn sẽ bôi thâm tài liệu vô cột Môn học

Tiếp theo dõi, nhấn con chuột nên, lựa chọn vô Show Values As và lựa chọn % of Grand Total.

hình lựa chọn hiển thị xác suất tổng

Hiển thị thành quả sau:

kết ngược hiển thị luật lệ tính

Mẹo và mẹo nhỏ nâng lên - Dữ liệu mối quan hệ (Relationships)

Đây là cơ hội giúp cho bạn nhóm nhiều tài liệu đem quan hệ với nhau trở nên một bảng. Từ bại, tiếp tục phân tách và tóm lược những độ quý hiếm vô bảng bại.

Ví dụ: Mình đem 2 bảng tài liệu như bên dưới.

hình tài liệu relationship

Để group 2 bảng tài liệu bên trên, chúng ta tiến hành quá trình như sau:

Bước 1: quý khách hàng lựa chọn 1 dù bất kì ở trên bảng tài liệu loại nhất. 

Bước 2: Vào thẻ Insert => PivotTable. Hộp thoại Create PivotTable sinh ra, các bạn sẽ thiết lập vùng tài liệu (giống chỉ dẫn trên) và tích lựa chọn Add this data đồ sộ the Data Model.

hình hình ảnh thiết lập bảng pivottable

Bước 3: Vào thẻ PivotTable Analyze, lựa chọn vô Relationships và lựa chọn New

Bước 4: Tại bảng Create Relationships. Tại mục Table, chúng ta lựa chọn thương hiệu bảng tài liệu loại nhất và lựa chọn thương hiệu bảng tài liệu loại nhị vô mục Relates Table. Hai bảng đều phải sở hữu công cộng mối quan hệ là cột ID.

hình hình ảnh tạo nên relationship

Bước 4: Nhấn Ok. Nếu link được thì tiếp tục hiện nay bảng sau:

bảng link manager

Bước 5: Nhấn Close nhằm triển khai xong.

Bước 6: Trong bảng PivotTable, lựa chọn mục All. Kéo ngôi trường SL phân phối ở Table 11 xuống dù Values và kéo ngôi trường ID ở Table 13 xuống dù Rows.

hình hình ảnh kéo ngôi trường nhị bảng

Mẹo và mẹo nhỏ nâng lên - Hàm DAX

Là hàm được chấp nhận ta tạo nên những luật lệ tính cực kỳ uy lực và linh hoạt vô bảng tổ hợp.

Ví dụ: Mình dùng bảng PivotTable được link nhị bảng bên trên.

Yêu cầu: Dùng chức năng DAX vô excel nhằm kiểm điểm độ quý hiếm khác lạ và có một không hai vô excel.

Bạn thực hiện như sau:

Bước 1: Chọn vô trường Table11 vô bảng PivotTable và nhấn con chuột nên, lựa chọn Add Measure.

hình lựa chọn add measure

Bước 2: Hiển thị rời khỏi vỏ hộp thoại Measure. Tại phía trên, chúng ta ghi thương hiệu measure vô dù Measure Name, xuống mục Formulas: quý khách hàng tiếp tục thiết lập công thức như sau: =DISTINCTCOUNT(Table11[ID])

Xem thêm: ảnh động vật

hình hình ảnh thiết lập measure

Bước 3: Measure bản thân thiết lập vẫn hiển thị vô Table11 ở bảng PivotTable. Tiếp theo dõi các bạn sẽ kéo Measure bại xuống bên dưới mục Values.

hình hình ảnh hiển thị mục measureVậy là tôi đã chỉ dẫn hoàn thành 30 mẹo và mẹo nhỏ nâng lên tiên phong hàng đầu vô Excel. Hi vọng các bạn sẽ tiến hành thành công xuất sắc. Nếu thấy nội dung bài viết tiện ích, vui vẻ lòng reviews nội dung bài viết mặt mũi dưới! Trân trọng.