Phân tích dữ liệu (3) - Excel cơ bản và nâng cao (2)

 Bài trước: Phân tích dữ liệu (2) - Excel cơ bản và nâng cao (1)

-----

II.2. Nhập dữ liệu và tính toán căn bản

II.2.1 Định dạng văn bản

Thực hiện được các định dạng trên dữ liệu:

- Tô đậm (bold), nghiêng (italics), gạch chân (underline)

- Định dạng viền (border)

- Đổ màu (fill)

- Phông chữ (font)

- Căn lề (alignment)

- Sao chép định dạng (format painter)

- Xóa các định dạng (clear formats)

II.2.2. Biên tập và xem dữ liệu

- Nhập nội dung cho ô dữ liệu

- Hiển thị nhiều dòng trong một ô dữ liệu

- Sao chép dữ liệu giữa các ô

- Sắp xếp, lọc dữ liệu

- Tìm kiếm và thay thế

- Lưu tập tin

- Chia cửa sổ dữ liệu thành các khung cố định (freeze panes) để dễ quan sát

II.2.3. Các hàm tính toán

- Các hàm trong Excel

- MAX, MIN, AVERAGE

- Sao chép công thức: để ý cách Excel thay đổi địa chỉ hàng và cột ở mỗi ô sao chép công thức. Phân biệt sự khác nhau của các loại tham chiếu trong một địa chỉ, ví dụ A1, $A$1, A$1, $A1.

- Hàm COUNT, COUNTA, COUNTIF,...v.v

- Hàm HLOOKUP, VLOOKUP

- Hàm xử lý ngày tháng, ví dụ DAY, MONTH, YEAR

- Hàm xử lý văn bản, ví dụ TRIM, RIGHT, LEFT, FIND, REPLACE

- Hàm IF, SUMIF

- Các lỗi thường gặp

Hàm SUMPRODUCT

Với bảng dữ liệu trên, để tính Tổng tiền, chúng ta sẽ chèn thêm cột Thành tiền, và dùng hàm SUM để tính tổng tiền. Xem hình minh họa.

Tuy nhiên, nếu dùng hàm SUMPRODUCT, thì chúng ta không cần phải thêm cột Thành tiền, và không cần hàm SUM.

Cú pháp của hàm SUMPRODUCT:

=SUMPRODUCT(E2:E11,F2:F11)

Hàm SUMPRODUCT sẽ lấy 1 phần tử của cột E nhân với 1 phần tử cùng hàng của cột F, rồi cộng các tích lại với nhau. Cụ thể: E2*F2 + E3*F3 + …+E11*F11 =  59,897,000.

Chúng ta cũng có thể tính Tổng tiền (hàng Nội địa), Tổng tiền (hàng Nhập khẩu). Ví dụ:

- Công thức tính Tổng tiền (hàng Nội địa): 

=SUMPRODUCT((D2:D11="Nội địa")*1,E2:E11,F2:F11). 

Ở hàm trên, điều kiện (D2:D11="Nội địa") có nghĩa là: chỉ các hàng có giá trị ở cột D là “Nội địa” mới được tính. Vì điều kiện trả về TRUE, FALSE, nên cần nhân với 1 để chuyển thành giá trị số.

- Công thức tính Tổng tiền (hàng Nhập khẩu): 

=SUMPRODUCT((D2:D11="Nhập khẩu")*1,E2:E11,F2:F11)

Hàm INDEX và MATCH

Hàm VLOOKUP chỉ dò tìm được dữ liệu về phía phải của giá trị tìm kiếm. Ví dụ:

Ở bảng dữ liệu trên, bạn có thể sử dụng hàm VLOOKUP để điền thông tin cho cột Tên hàng, dựa vào 2 ký tự đầu của Mã hàng. Tuy nhiên, nếu bạn muốn điền thông tin cho cột Kệ hàng thì không thể sử dụng hàm VLOOKUP. Lý do là hàm VLOOKUP chỉ dò tìm được các thông tin nằm ở phía phải của giá trị tìm kiếm (lookup value). Xem hình minh họa:

Giải pháp để điền thông tin cột Kệ hàng cho bảng dữ liệu là sử dụng hàm INDEX và MATCH.

Hàm INDEX

Hàm INDEX được sử dụng để lấy giá trị hoặc tham chiếu đến giá trị trong một bảng hoặc một vùng. Hiểu đơn giản là lấy giá trị tại vị trí giao của hàng và cột.

Cú pháp:

INDEX(array, row_num, [column_num])

Trong đó:

- array là bảng hoặc vùng chứa giá trị muốn lấy

- row_num: hàng chứa giá trị muốn lấy

- column_num (tùy chọn): chỉ số cột chứa giá trị muốn lấy, nếu không cung cấp chỉ số cột, hàm INDEX sẽ lấy giá trị ở cột đầu tiên

Ví dụ:

Muốn điền giá trị cho Kệ hàng ở bảng sau:

Chúng ta có thể dùng hàm INDEX để lấy các giá trị trong vùng A20:B24.

=INDEX(A20:B24,3,1) 

Tuy nhiên, cần dựa vào giá trị của Mã hàng để lựa chọn chỉ số hàng (row_num) trong hàm INDEX tùy theo Mã hàng.

Chúng ta sử dụng thêm hàm MATCH.

Hàm MATCH

Hàm MATCH được sử dụng để tìm vị trí tương đối của một giá trị trong một vùng.

Ví dụ: chúng ta cần lấy 2 ký tự bên trái của Mã đơn hàng (ví dụ GL-ND-29), xem Mã hàng này nằm ở hàng nào trong bảng phụ.

Kết quả trả về sẽ là 3.

Vậy là có thể lồng MATCH vào INDEX để tìm kệ hàng cho mỗi Mã đơn hàng.

=INDEX(A$20:B$24,MATCH(LEFT(B2,2),$B$20:$B$24,0),1)

Kết quả:

II.2.4. Xem và đọc thêm

[1] Udemy (tiếng Anh): 

https://www.udemy.com/course/useful-excel-for-beginners/learn/lecture/304308#overview

- Section 4: Formatting

- Section 5: Editing 

- Section 6: Saving 

- Section 7: Viewing

- Section 8: Calculations

[2] Youtube (tiếng Việt): 

- Clip 03. Các hàm tính tổng SUM SUMIF SUMIFs: https://www.youtube.com/watch?v=RXj9T-wSZWk&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=4

- Clips 04. Các hàm ĐẾM: https://www.youtube.com/watch?v=yHLGXACgTU0&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=5

- Clip 05. Hàm điều kiện IF: https://www.youtube.com/watch?v=_41-_Q6_LsE&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=6

- Clip 06. Hàm xử lý chuỗi:

https://www.youtube.com/watch?v=olnU9t2Dr4Y&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=7

- Clip 07. VLOOKUP, HLOOKUP:

https://www.youtube.com/watch?v=zlVq9bUpbyc&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=8

- Clip 08. Hàm INDEX, MATCH, SUMPRODUCT

https://www.youtube.com/watch?v=6ZqfZ5JFuTA&list=PLApuTT4CRzA9XAywWsReLBID1UYrPCwWB&index=9

II.2.5. Bài tập và thực hành

Bài 1. Làm các bài thực hành trong clip ở phần “Xem và đọc thêm”

Bài 2. Làm bài tập này để thực hành một số hàm cơ bản.

- Nhập bảng dữ liệu sau:

- Yêu cầu thực hiện:

1. Tên hàng: Sử dụng 2 ký tự đầu của Mã đơn hàng để Tra bảng tên hàng.

2. Loại hàng: Nếu ký tự 4 và 5 của Mã đơn hàng là "ND" thì là "Nội địa" còn lại là "Nhập khẩu"

3. Số lượng: là giá trị của 2 ký tự cuối trong Mã đơn hàng

4. Đơn giá: Sử dụng 2 ký tự đầu của Mã đơn hàng để Tra bảng đơn giá, tìm đúng đơn giá theo

từng loại hàng.

5. Giảm giá: nếu mua vào ngày đầu tháng thì giảm 5% giá trị đơn hàng.

6. Thành tiền: Số lượng * Đơn giá - Giảm giá

7. Lập bảng thống kê thành tiền theo loại hàng bán ra.

Bài 3. Làm lại ví dụ về SUMPRODUCT

Bài 4. Làm lại ví dụ về hàm INDEX và MATCH

II.2.6. Câu hỏi ôn tập

Câu 1. What keyboard operation creates an end of line and allows you to type text in multiple lines in a cell?

A. Alt + M

B. Shift + M

C. Alt + Enter

D. Ctrl + M

Câu 2. What is not a valid method of entering into edit a cell's content?

A. Press F2

B. Click inside the formula bar

C. Double click directly on the cell

D. Ctrl + E

Câu 3. What is the keyboard shortcut to "paste"?

A. Ctrl + A

B. Ctrl + P

C. Ctrl + V

D. Ctrl + E

Câu 4. What is the keyboard shortcut to "cut"?

A. Ctrl + X

B. Ctrl + E

C. Ctrl + C

D. Ctrl + A

Câu 5. What is the default file extension for workbooks you create from newer (2007 and later) versions of excel?

A. XL

B. XLSX

C. XLS

D. EXL

Câu 6. Which function will remove spaces in a cell except for single space between words?

A. TRIM

B. SPACE

C. WORD

D. REMOVE

—

Đáp án: 1(C), 2(D), 3(C), 4(A), 5(B), 6(A)  

-----

Cập nhật: 24/4/2024

Bài sau:

-----

Nhận: Dạy Phân tích dữ liệu tại Đà Lạt (nhắn tin)