RaoVat24h
Excel Office

Làm thế nào để sử dụng Excel: 12 kỹ thuật cho người sử dụng thành thạo

Advertisement

Làm thế nào để sử dụng Excel: 12 kỹ thuật cho người sử dụng thành thạo

Excel thì tuyệt đấy, nhưng cố gắng tự mày mò tìm cách sử dụng Excel cũng chỉ đưa bạn tới đây thôi, nó đâu phải chuyện trực giác chứ. Nhưng nếu bạn dùng các kỹ thuật và mẹo vặt trong hướng dẫn này, bạn sẽ có thể hoàn thành công việc nhanh hơn mà không bị căng thẳng nhiều.
Tìm hiểu cách áp dụng các công thức Excel, cách tính toán, lọc, thao tác dữ liệu, hiệu quả quy trình làm việc và hơn thế nữa. Ở đây có hàng tá kỹ thuật và tính năng Excel mà bạn cần biết.

Screencast

Trước khi bắt đầu, hãy download file worksheet Excel miễn phí này nhé. Có rất nhiều sheet trong các file, để thực tập từng cái trong số 12 kỹ thuật Excel. Hãy xem clip ghi lại thao tác trên màn hình (screencast) phía trên hoặc tìm hiểu qua phiên bản viết hướng dẫn từng bước bên dưới.

1. Kiến thức căn bản về viết một công thức

Mọi công thức bắt buộc phải bắt đầu bằng dấu bằng (=).  Nếu bạn không đánh dấu bằng (=) vào, Excel sẽ không tính toán đâu; nó sẽ chỉ hiển thị những gì bạn đánh vào thôi. Đó là cách Excel nói, “Ê, tính toán cái này đi chứ.”  Nghĩ về kết quả của công thức bạn viết như thứ sẽ nằm bên tay trái của dấu bằng (=) nếu bạn dùng bút chì và giấy.
Vậy bạn sẽ viết công thức vào đâu nào? Viết vào ô mà bạn muốn câu trả lời sẽ nằm ở đó. Tham khảo sheet ‘simple’ trong file Excel về bài tập này nhé.
Ví dụ: Thêm các giá trị của B2B3 và B4 và đặt tổng số trong B6
Trong B6, nhập công thức thế này:
= B2 + B3 + B4
Simple formula
Công thức đơn giản
Rồi bấm Enter để có kết quả. Nếu bạn không nhấn Enter (hoặc click vào dấu tích nho nhỏ ngay phía trên column A), chả có gì xảy ra đâu.
Simple Formula Entered
Công thức đơn giản đã được nhập vào
Phép trừ, phép nhân, phép chia và lũy thừa cũng hoạt động y chang như vậy. Trong hầu hết mọi phần, hãy sử dụng các ký hiệu bạn vẫn thường dùng hay tham khảo bảng dưới đây:
Phép toán Ký hiệu
Cộng +
Trừ
Nhân *
Chia /
Lũy thừa ^ (ví dụ: B5^2 là giá trị của B5 bình phương)
Lớn hơn >
Nhỏ hơn <
Lớn hơn hoặc bằng >=
Nhỏ hơn hoặc bằng <=
Nối &

2. Để thực hiện các tính toán phức tạp hơn, dùng các hàm

Nếu bạn muốn thực hiện các phép tính liên quan nhiều thứ hơn một tí so với công thức thông thường, chèn hàm vào công thức của bạn. Excel có hơn 400 hàm, thế nào bạn cũng tìm ra vài cái mình cần. Có hàm dành riêng cho kinh doanh, thống kê, tài chính, ngày và giờ, văn bản, thông tin tài liệu và nhiều thứ khác.
Cú pháp của tất cả các hàm là:
= TÊN HÀM(...)
Bạn luôn bắt đầu với dấu bằng (=) nếu hàm nằm ở đầu công thức, sau đó tới tên hàm, rồi tới một cặp ngoặc đơn. Thường sẽ có gì đó trong ngoặc đơn, nhưng không phải lúc nào cũng cần.
Ví dụ: Tìm tổng và trung bình cộng của một cột số. Tham khảo sheet ‘hàm’ trong file Excel miễn phí về bài tập này.
Trong B10, nhập hàm:
=SUM(B2:B8)
Ở B11, nhập hàm:
=AVERAGE(B2:B8)
Sum and Average
Tính tổng và trung bình
Nhấn Enter để có kết quả.
Sum and average result
Kết quả tổng và trung bình

3. Để Copy & Paste nhanh chóng và hiệu quả, dùng AutoFill

Để xem, bạn viết một công thức trên đầu một cột và muốn sử dụng lại công thức đó trên toàn cột (hoặc bạn có một công thức ở ô bắt đầu một dòng và muốn sử dụng lại công thức đó trên toàn dòng). Thay vì copy & paste thủ công, hãy dùng chức năng AutoFill để làm và công thức sẽ tự điều chỉnh chính nó. Bạn cũng có thể dùng AutoFill để nhập tháng hoặc ngày trong tuần hoàn toàn tự động.
Để dùng AutoFill, nhìn vào cái chấm bé xíu góc dưới bên phải của ô hiện tại. (Nếu bạn chọn nhiều ô cùng lúc, chúng sẽ có cùng một chấm.) Khi bạn rê con trỏ chuột lên chấm này, đầu chuột sẽ biến thành một dấu +. Sau đó kéo dấu + này xuống hết cột hoặc hết dòng.
Ví dụ nhé: làm một phép nhân đơn giản ở D2, sau đó AutoFill xuống tới D8. Tham khảo sheet “autofill” trong file excel miễn phí cho bài tập này.
Cứ viết công thức đầu tiên như bạn thường làm.
Autofill formula
Công thức tự điền
Nhập công thức vào. Nếu cần, click ngược vào lại D2. Rê con trỏ chuột trên chấm nhỏ góc dưới bên phải của ô, và con trỏ chuột sẽ biến thành dấu +.
Crosshair
Chuột trở thành một dấu cộng
Bạn có thể kéo dấu + xuống hết colum D, hoặc đơn giản hơn là cứ nhấp đôi chuột. Cột sẽ nhận công thức chính xác và kết quả chính xác trên toàn cột.
Double-click to select the column
Nhấp đôi chuột để chọn cả cột

4. Lọc và sắp xếp dữ liệu.

Bạn có thể sử dụng Excel như một cơ sở dữ liệu nho nhỏ, sắp xếp các dòng dữ liệu theo thứ tự chữ cái hoặc số, và lọc ra đúng thông tin bạn muốn thôi. Chức năng này hoạt động tốt nhất nếu các cột có một tiêu đề, như Tên, Họ, đại loại vậy.
Điều quan trọng nhất để nhớ là đừng có chọn một cột trước khi sắp xếp. Điều đó sẽ sắp xếp cột này độc lập hoàn toàn với phần dữ liệu còn lại, đó có lẽ không phải thứ bạn muốn. Khi bạn click vào một cột, Excel sẽ đủ thông minh để biết chúng ta đang làm gì.
Về chuyện lọc dữ liệu thì, Excel có những công cụ tự động tuyệt vời.
Ví dụ: sắp xếp một danh sách theo tên, thành phố hay một dữ liệu nào khác, theo thứ tự tăng hoặc giảm. Tham khảo sheet ‘filter and sort’ trong file excel miễn phí cho bài tập này.
Simple sort
Các sắp xếp đơn giản trong Excel
Chọn bất kỳ ô nào trong cột Last Name. Trên tab Home của Ribbon, nhấp vào Sort and Filter, rồi chọn A to Z hoặc Z to A. Thử điều này với bất kỳ cột nào khác.
Bạn có thể cũng đã nhận ra, ta còn có thể lọc thông qua cùng một bảng menu xổ xuống.
Ví dụ: giờ thì lọc danh sách để chỉ hiển thị các địa chỉ từ Alaska và California.
Nhấp chuột vào bất kỳ đâu trong vùng dữ liệu và chọn Filter từ menu Sort and Filter.
Simple Filter
Bộ lọc đơn giản
Khi bạn thao tác xong, bạn sẽ thấy trên tiêu đồ các cột xuất hiện mũi tên chỉ xuống. Giờ thì:
  • Nhấp vào vào mũi tên chỉ xuốngtrong cột State.
  • Rồi nhấp vào ô Select all để xóa hết các hộp chọn về bang.
  • Chỉ chọn lại AK và CA, rồi nhấp OK.
Filter box
Hộp thoại lọc
Giờ thì danh sách chỉ hiện thị các dòng của 2 bang này thôi. Nếu bạn muốn, sắp xếp lại theo bang từ A tới Z. Điều đó sẽ giúp nhóm lại tất cả các dòng của bang Alaska phía trên bang California (được bôi đậm trong ảnh chụp màn hình bên dưới).
Nếu bạn muốn xóa bộ lọc, nhấp vào mũi tên xổ xuống trên cột State lần nữa và chọn Clear Filter.
Clear Filter
Xoá bộ lọc
Bạn có thể xóa các mũi tên xổ xống như cách bạn bật chúng vậy: click vào menu Sort and Filter và chọn Filter, một lần nữa.

5. Xóa dòng trùng nội dung.

Nếu bạn có rất là nhiều dữ liệu, đặc biệt khi nó được trích xuất từ đâu đó như là một biểu mẫu web chẳng hạn, bạn có thể gặp những dòng trùng nội dung. Excel làm rất tuyệt trong việc xóa mấy dòng trùng này, và cho bạn quyền quyết định rốt cuộc 2 dòng giống nhau cỡ nào mới được tính là trùng.
Ví dụ: dùng thông tin địa chỉ để quyết định những dòng nào trùng nhau. Tham khảo sheet ‘remove duplicate’ trong file Excel miễn phí cho bài tập này.
Khi bạn kéo màn hình xuống file ví dụ này, chúng ta thấy có 3 hàng giống nhau mang tên Samantha Carter: mọi dữ liệu trong các cột đều giống nhau, vì vậy chúng ta biết đây là cùng một người được liệt kê 3 lần. Cũng có 2 dòng tên Sharon Valeri, nhưng tất cả các thông tin khác không giống, vì vậy ta biết đây là người khác nhau, nhưng trùng tên.
Select both Sam and Sharon
Chọn cả Sam và Sharon
Đảm bảo là bạn nhấp chuột vào vùng dữ liệu, đâu cũng được. Sau đó trong tabData của ribbon, nhấp chuột Remove Duplicates.
Remove Duplicates dialog box
Hộp thoại Remove Duplicates
Cứ chọn mọi hộp chọn. Bằng cách này, Excel sẽ chỉ xem các hàng là trùng nếu tất cả nội dung trên cột đều giống nhau.
Nhấp vào OK. Máy báo có 2 thông tin trùng đã được xóa, như chúng ta dự kiến. Khi ta kéo xuống, ta sẽ thấy 2 trong số 3 ghi chép về Samantha Carter đã bị xóa, và cả 2 ghi chép về Valeri vẫn còn nguyên.
Duplicates removed
Trùng lặp bị loại bỏ

6. Chia dữ liệu trong một cột sang nhiều cột

Nếu bạn có một cột dữ liệu cần chia thành nhiều cột, dùng lệnh Text to Columns. Chỉ cần chú ý là nó không hoàn hảo lắm đâu, máy vi tính không đọc được tâm trí của bạn mà, cho nên có thể bạn sẽ phải cắt tỉa thủ công lại đôi chút.
Ví dụ: chia một cột chứa tên đầy đủ thành 2 cột riêng biệt cho họ và tên. Tham khảo sheet ‘splitting’ trong file Excel miễn phí cho bài tập này.
Ở đây chúng ta cũng có dữ liệu tương tự như ví dụ trước. Lưu ý rằng những người có tên trong danh sách có 3 đoạn tên lận, cho nên chúng ta sẽ cần 2 cột trống.
Đầu tiên, chèn 2 cột trống vào bên phải cột A:
Đặt con trỏ chuột trên tiêu đề của cột B, để nó trở thành một mũi tên chỉ xuốngKéo sang phải để cột B và C cùng được chọn.
Selecting Excel columns
Chọn các cột Excel
Nhấp chuột phải và chọn Insert từ menu popup. Vì bạn đang chọn tới 2 cột lận, bạn sẽ chèn ngay được 2 cột trống.
Chọn tất cả tên trong cột A. (Cách chọn nhanh này, nhấp chuột vào tên đầu tiên ở ô A3, rồi nhấn Ctrl + Shift + Mũi tên xuống. Rồi kéo lên trên đầu bảng tính)
Trên tab Data, chọn Text to Columns. Chọn Delimited, rồi bấm Next.
Text to Tabs columns
Các cột Text to Tabs
Chọn Space làm dấu phân cách duy nhất. Nhiều dấu phân cách kề bên nhau sẽ được xem như là một, đây là chức năng mặc định, cho nên cứ kệ nó thôi. Kéo xuống dọc theo danh sách để thấy những cái tên sẽ được dàn ra thành 3 cột.
Text to Tabs columns span
Span các cột Text to Tabs
Nhấp chuột Next, rồi nhấp chuột Finish. Giờ thì cột A chỉ còn tên thôi, cột B sẽ có họ, còn cột C sẽ có phần tên thứ 3 bị tràn ra ngoài.
Split names
Tách tên
Bạn có thể điều chỉnh thủ công phần tên thứ 3 này, rồi xóa cột C đi, nó nên được để trống.

7. Nhập nhiều cột thành một

Excel cũng khá thành thạo trong chuyện làm ngược lại nữa: nếu bạn có nhiều cột và muốn nhập chúng thành một. Có vài cách để làm điều này, nhưng tôi sẽ chỉ cho bạn cái đơn giản nhất, là dùng một công thức. Tham khảo sheet ‘joining’ của file Excel miễn phí cho bài tập này.
Như bạn làm phía trên, chèn một cột mới vào sau cột B. Đặt con trỏ chuột trên phần tiêu đề của cột C để con trỏ chuột biến thành mũi tên hướng xuống.
Joining
Chèn một cột mới
…rồi chọn Insert từ menu hiện ra. Đặt tên cột mới là Full Name.
Nhấp chuột vào ô C3 trên đầu cột mới để nhập công thức đầu tiên. Để cho dễ hiểu hơn, chúng ta sẽ giả bộ nhập công thức sai đi chút xíu, rồi sửa nó lại.
Chúng ta muốn nối nội dung của cột, chứ không phải thêm số, vì vậy dấu hiệu cần dùng sẽ là ‘&’.  Vậy hãy viết công thức như sau:
= A3 & B3
Joining formula
Nối các công thức
Nhưng khi bạn nhập như vậy, phần họ và tên sẽ dính chum vào nhau, không có khoảng trống ở giữa. Vì vậy, xóa nó đi. Viết lại công thức, nhưng bây giờ chúng ta thêm một chi tiết khác:
= A3 & "" & B3
Đặt space bên trong ngoặc kép nghĩa là công thức sẽ bao gồm một khoảng trống theo đúng nghĩa đen.
Joining formula with space
Nối các công thức với khoảng trống
Nhấn Enter, rồi AutoFill xuống cả cột.
AutoFill down
Tự động điền xuống

8. Định dạng nhanh các con số và worksheets

Đây là vài cách định dạng nhanh con số (đô la, %, v..v…) và worksheets (màu, font chữ).
Tham khảo sheet ‘định dạng’ trong file Excel miễn phí cho bài tập này.
Các con số trong worksheet nên có dấu phẩy để chia số hàng ngàn, nhưng bạn không cần dấu thập phân. Bạn có thể muốn dùng ký hiệu $ cho dòng đầu tiên, dòng Total và cột Total. Ngoài ra, cột cuối cùng phải được định dạng thập phân nhưng nhìn phải như phần trăm.
Chọn dòng trên cùng của các con số qua cột Total.
Giữ phím Ctrl và chọn dòng Total ở phía cuối (dòng đầu và cuối giờ sẽ được chọn cùng lúc).
Formatting selection
Lựa chọn định dạng
Trên ribbon, nhấn nút Dollar sign hoặc nhấn Ctrl + Shift + $. Điều đó sẽ thêm vào dấu hiệu $, dấu phẩy và dấu thập phân. (Chúng ta sẽ xóa dấu thập phân chỉ trong tíc tắc, đừng lo).
Giờ thì chọn mọi con số ở giữa và nhấp chuột Comma style button hoặc nhấn Ctrl + Shift + !. Nó sẽ chèn dấu phẩy và dấu thập phân.
Để xóa tất cả dấu thập phân, chọn hết số trong sheet trừ cột Percent ở phía cuối và nhấp chuột nút Decrease Decimal 2 lần.
Formatting decrease decimal
Định dạng Excel  – giảm dấu phẩy thập phân
Giờ thì hãy áp dụng phần trăm.
Chọn mọi con số trong cột % of Total, rồi nhấp chuột ký hiệu hoặc bấm Ctrl + Shift + %.
Nếu bạn muốn, nhấp chuột nút Increase Decimal một hai lần gì đấy.
Applying percentages
Áp dụng phần trăm
Để định dạng một sheet, có 3 cách để làm:
  • Chọn tiêu đề của các cột và dòng, thay đổi màu sắc, font chữ v…v… từng cái một.
  • Chọn các tiêu đề cột và dòng, và sử dụng các style có sẵn.
  • Định dạng toàn bộ sheet như một bảng và để Excel áp dụng thiết kế.
Để cho nhanh thì, đây là phương pháp thứ 2 và thứ 3.
Chọn mọi tiêu đề của cột, rồi nhấp chuột Cell Styles trên ribbon. Chọn một style bạn thích.
Formatting cell styles
Các style ô định dạng cột Excel
Làm tương tự cho các tiêu đề dòng.
Excel header formatting cell styles
Các style ô định dạng tiêu đề Excel
Undo vài lần.
Giờ thì nhấp chuột Format as Table và chọn một thiết kế.
Nhấp chuột OK trong hộp thoại hiện ra. Chú là các cột có mũi tên xổ xuống để bạn lọc chúng đấy nhé.

9. Thao tác cột, dòng, Page Breaks và Worksheets

Thay đổi kích thước cột và dòng

Nếu một cột quá chật, bạn có thể thấy các dòng chữ bị cắt bớt hoặc các con số hiển thị dưới dạng #. Nếu một dòng quá ngắn, bạn có thể không đọc nổi nó nữa. Tham khảo sheet ‘rows & columns của file Excel miễn phí cho bài tập này.
Thay đổi độ rộng cột hay chiều cao dòng cơ bản được thực hiện giống nhau: đặt con trỏ chuột ngay cạnh phải hoặc cạnh đáy của dòng hay cột, để con trỏ chuột biến thành mũi tên 2 đầu, rồi kéo cho tới khi cột hoặc dòng đạt kích thước mong muốn. Hoặc bạn có thể nhấp đôi chuột để cột hoặc dòng tự điều chỉnh kích thước. (Điều này cũng giống như trong Windows File Explorer ở chế độ xem chi tiết.)
Windows File Explorer column width row height
Chiều cao dòng và độ rộng cột của cửa sổ Window File Explorer

Thao tác ngắt trang (Page Breaks)

Có 2 cách để tạo các ngắt trang:
  • Chèn thủ công, từ tab Page Layout
  • Dùng chế độ Preview Page Break
Để chèn page break thủ công, chọn tiêu đề dòng hoặc cột bạn muốn sang trang, rồi từ tab Page Layout của ribbon, nhấp chuột Breaks drop-down và chọn Insert Page Break.
Insert page break
Chèn ngắt trang
Để điều chỉnh các page breaks, dùng Page Break Preview
Nhấp chuột biểu tượng Page Break Preview ở góc dưới bên phải, sau đó kéo các đường. Các đường chấm chấm là page breaks tự động, và nếu bạn chèn page breaks, những đường này sẽ thành nét liền.
Adjust page breaks
Điều chỉnh các ngắt trang

Điều chỉnh các trang tính

Để chèn, xóa, di chuyển hay đổi tên trang tính, dùng các tab ở góc dưới bên trái màn hình.
Sách bài tập này chỉ có một sheet, gọi là Sheet1. Để đổi tên nó thì:
  • Nhấp đôi chuột vào tab.
  • Đánh một cái tên mới (như N.America chẳng hạn), rồi nhấn Enter.
Working with sheets
Làm việc với các sheet
Để chèn một sheet trống mới, nhấp chuộtvào dấu + hình tròn kế bên tab
Để sao chép sheet:
  • Kéo tab của sheet sang bên phải cho tới khi bạn thấy một tam giác chỉ xuống kế bên nó.
  • Nhấn Ctrl và bạn sẽ thấy một dấu + nhỏ ở đầu trỏ chuột.
Duplicating a sheet
Nhấp chuột vào hình tam giác
Thả chuột. Bạn sẽ thấy một sheet mới với tên tương tự và đuôi “(2)” đằng sau.
Your new sheet is created
Sheet mới của bạn được tạo ra
Bạn có thể kéo các sheet tới bất kỳ vị trí nào bạn muốn. Các sheet sẽ được đặt xuống nơi bạn thấy hình tam giác chỉ xuống.

10.Giữ các tiêu đề cột luôn hiển thị

Khi bạn có một sheet thiệt dài, rất dễ để một tiêu đề cột bị kéo đi mất tiêu khỏi tầm nhìn, nhưng có một chức năng để tránh điều đó. Khi bạn in một sheet dài, bạn muốn đảm bảo tiêu đề các cột xuất hiện trên đầu mỗi trang – và, ghi nhớ là, đừng có làm như vậy bằng cách copy paste nhé. Nó sẽ trở thành một cơn ác mộng thực thụ đó.
Tham khảo sheet ‘headers’ trong file Excel miễn phí cho bài tập này.

Giữ tiêu đề luôn hiển thị khi cuộn màn hình.

Trong sheet này, nhấp chuột vào ô A3, nằm ngay bên dưới dòng tiêu đề cột. Rồi trong tab View của ribbon, nhấp chuột Freeze Panes. Và chọn Free Panes từ phần xổ xuống.
Select Freeze Panes
Chọn Freeze Panes
Giờ thị bạn có thể cuộn lên cuộn xuống sheet thoải mái mà vẫn thấy dòng tiêu đề.
Bạn có thể giải khóa theo cách tương tự: nhấp chuột Freeze Panes, rồi chọn Unfreeze Panes từ thanh cuộn.
Tuy nhiên, nó không có tác dụng gì khi in cả. Để in tiêu đề cột trên tất cả các trang:
  • Vào tab Page Layout của ribbon và nhấp chuột Print Titles.
  • Nhấp chuột bên trong Rows to Repeat ở hộp Top.
  • Trên trang tính, chọn tiêu đề of các dòng 1 và 2. Nó sẽ nhập $1:$2 vào hộp
Select the headers
Chọn các tiêu đề
Để xem nó có hiệu quả không, tới Print Preview: File > Print hoặc nhấn Ctrl + P.
Nhấp chuột các tab của trang phía dưới để cuộn qua các trang và kiểm tra các tiêu đề.
Scroll through the headers
Cuộn qua các tiêu đề

11. Hàm IF: tạo giá trị ô dựa trên một điều kiện

Excel có vài hàm có thể đưa ra quyết định hiển thị giá trị nào. Chúng toàn dựa cả vào hàm IF. Một khi bạn đã quen thuộc với nó, sẽ dễ hơn cho bạn sử dụng các hàm phức tạp hơn.
Hàm IF có 3 đối số
  • Điều kiện đang được kiểm tra.
  • Ô sẽ hiển thị giá trị nào nếu điều kiện đó là đúng.
  • Ô sẽ hiển thị giá trị nào nếu điều kiện đó là sai.
Khi một hàm có nhiều đối số, bạn phân cách chúng ra bằng dấu phẩy. Vì vậy cú pháp như sau:
= IF (điều kiện kiểm tra, giá trị nếu điều kiện đúng, giá trị nếu điều kiện sai)
Tham khảo sheet ‘functions’ trong file Excel miễn phí cho bài tập này.
Ví dụ: bạn có một danh sách đơn hàng được đặt từ nhiều người ở khắp các bang. Nếu một đơn hàng tới từ New Jersey, áp dụng thuế bán hàng 7%. Nếu một đơn hàng đến từ bất kỳ đâu khác, không áp dụng thuế.
Ở đầu của cột Tax (H3), nhập công thức thế này:
= IF(C3="nj", G3 *.07, "")
Nghĩa là:
  • Điều kiện kiểm tra: ô trong cột Statecó chứa giá trị “nj” không? (Lưu ý là nó không phân biệt chữ hoa chữ thường đâu nhé và vì nó là một giá trị dạng text, nó phải được đóng ngoặc kép vào)
  • Nếu điều kiện là đúng, ô sẽ có giá trị Sale x 7%.
  • Nếu điều kiện là sai, ô sẽ không có giá trì nào hết (một bộ ngoặc kép trống).
IF Funcation results
Các kết quả của hàm IF
AutoFill xuống tới dòng cuối. Hầu hết các ô sẽ trống, và những ô không bỏ trống sẽ hiển thị giá trị thuế bán hàng vì dòng có giá trị NJ trong cột State, như thể hiện bên dưới.
If sheet Autofill

12. SUMIF và AVERAGEIF: phép cộng và lấy giá trị trung bình dựa trên một điều kiện

Tương tự hàm IF, hàm SUMIF và AVERAGEIF sẽ tìm giá trị tổng và giá trị trung bình trong phạm vi các ô khi tồn tại một giá trị hay điều kiện nào đó.
Các hàm này cũng có 3 đối số:
  • Chúng ta đang tìm điều kiện trong vùng nào?
  • Điều kiện được kiểm tra là gì?
  • Trong các dòng mà giá trị đó tồn tại, chúng ta bắt đầu lấy tổng hay trung bình trong vùng nào?
Cú pháp của 2 hàm này giống nhau – tên thì khác đó, nhưng các đối số là như nhau:
= SUMIF (vùng tìm kiếm, giá trị lựa chọn, vùng cộng)
= AVARAGEIF (vùng tìm kiếm, giá trị lựa chọn, vùng cộng)
Tham khảo sheet ‘SUMIF’ trong file Excel miễn phí cho bài tập này.
Ví dụ ha: tìm tổng của tất cả mọi đơn hàng tới California, và giá trị đơn hàng trung bình của Kona.

Làm SUMIF trước nhé.

Ở ô cuối cột Saleô G203, nhập công thức như sau để tìm tổng các đơn hàng tới California:
=sumif(C3:C201,"ca",G3:G201)
Nghĩa là:
  • Tìm kiếm trong cột C.
  • Trong cột C, chọn ra những dòng có chứa “ca”.
  • Ở cùng dòng tìm thấy giá trị “ca”, cộng giá trị cột G vào số tổng.
Add the value of column G
Thêm giá trị của cột

Giờ làm tới AVERAGEIF

Dùng kỹ thuật tương tự để tìm ra giá trị trung bình của mọi đơn hàng của Kona.
Tới ô G204 và nhập công thức này:
=averageif(D3:D201,"kona",G3:G201)
Nghĩa là:
  • Tìm kiếm trong cột D.
  • Trong cột D, chọn ra những dòng có chứa “kona”.
  • Ở cùng dòng tìm thấy giá trị “kona” thêm giá trị cột G vào để tính trung bình.
Average the column
Tính giá trị trung bình của cột
Kết quả sẽ như sau:
Final result
Kết quả cuối cùng

Dùng các kỹ thuật Excel này

Học cách dùng Excel cũng đáng để nỗ lực. Với các kỹ thuật và mẹo vặt này, bạn có thể áp dụng nhiều tính năng mạnh mẽ, tiết kiệm thời gian hơn vào bảng tính của chính mình. Thực tập các bài học ở trên, sau đó tiến tới kết hợp những kỹ thuật này vào quy trình công việc của bạn.

?DienDan.Edu.Vn cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn. https://diendan.edu.vn/

Rate this post

DienDan.Edu.Vn

DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.
DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.
Cám ơn.

Đăng bình luận

(+84) (901) 369.468