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:
II.2.4. Xem và đọc thêm
II.2.5. Bài tập và thực hành
II.2.6. Câu hỏi ôn tập
-----
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)
