11 Chuyên đề luyện tập về hàm Excel - Phần 7

Xin chào mừng các bạn đã đến với bài giải Excel số 4 trong series này. Hôm nay thì chúng ta có một bảng theo dõi cho thuê bằng đĩa và bây giờ thì chúng ta sẽ phải đi thực hiện những yêu cầu từ số 1 cho đến số 6. Và các bạn có thể để ý yêu cầu đầu tiên là tính số tuần và số ngày cho thuê bằng đĩa đối với từng loại khách hàng ở đây. Thì chúng ta có thể nhìn vào cột F là tổng số ngày thuê. Bởi vì ở đây ngày thuê và ngày trả đã được định dạng dưới cái dạng là ngày tháng rồi. Thế nên tổng số ngày thuê chúng ta có thể đánh giá một cách rất là dễ dàng bằng cách là lấy ngày trả trừ đi ngày bắt đầu thuê. Ở trong trường hợp này chúng ta sẽ lấy E4 trừ đi D4 rồi bấm nút Enter trên bàn phím. Thì các bạn có thể lặp lại công thức này cho các dòng còn lại và nhận được kết quả đúng ở đây là tổng số ngày thuê. Bây giờ để tính được số tuần thì chúng ta sẽ làm như thế nào? Để tính được số tuần thì các bạn biết là một tuần có 7 ngày. Vậy nên là chúng ta chỉ cần lấy tổng số ngày chia cho 7 là ra số tuần. Chúng ta sẽ lấy OF4 chia cho 7 và chúng ta sẽ lặp lại công thức này xuống phía dưới. Nhưng bây giờ giả sử là số tuần của chúng ta chúng ta không muốn một số lẻ như thế này thì chúng ta sẽ làm như thế nào? Thì cái cách làm ở đây là chúng ta sẽ sửa cái công thức ở OG4 đi. Chúng ta sẽ bấm chuột chọn vào OG4, sau đó chúng ta sẽ bấm phím F2 để chúng ta có thể sửa được công thức. Công thức ở đây chúng ta sẽ sử dụng hàm INT như thế này để chúng ta chỉ lấy cái phần đằng trước dấu phẩy của số tuần mà thôi. Thì như các bạn đã thấy thì bây giờ thì 12 ngày có nghĩa là một tuần và còn dư ra mấy ngày đó. Thì cái ngày dư thì chúng ta sẽ tính như thế nào? Thì cái ngày dư chính là cái phép chia của tổng số ngày mà chia cho 7. Để lấy cái phần dư của một phép chia ở trong Excel thì các bạn sẽ nhớ đến hàm MODE. Ở trong OH4 chúng ta sẽ viết vào là MODE, sau đó chúng ta mở ngoặc ra. Đầu tiên là NUMBER, chính là cái số ngày thuê. Và DIVISOR có nghĩa là số 7, tức là 7 ngày ở đây. Bấm Enter. Thì các bạn có thể nhìn thấy số ngày dư ra khi mà lấy 12 chia cho 7, chính là 5, chính là số ngày dư ở trong trường hợp này. Kéo xuống phía dưới và chúng ta đã có thể hoàn thành được cái yêu cầu đầu tiên là tính số tuần, số ngày dư và tổng số ngày thuê của các cái bạn này. Yêu cầu số 2 là chúng ta phải xác định thể loại băng được thuê theo mã hàng ở bảng tính. Bây giờ thì chúng ta có thể đọc một chút cái yêu cầu này, có nghĩa là căn cứ vào ký tự cuối cùng của mã hàng L là phim lẻ, B là phim bộ. Vậy thì đề bài này chưa được rõ ràng lắm, thì trong trường hợp các bạn đi thi các bạn có thể hỏi lại nếu mà đề bài chưa rõ ràng. Nhưng mà trong trường hợp này thì chúng ta biết được rằng là đối với cái mã khách hàng ở cột C, thì cái ký tự đầu tiên thì nó sẽ chỉ cái thể loại ở đây. Và chúng ta có một cái bảng để tra cứu thể loại từ A16 cho đến B20. Và khi mà chúng ta tra cứu như vậy, dựa trên cái ký tự đầu tiên thì chúng ta sẽ cần lấy ra ký tự đầu tiên từ bên tay trái sang bảng hàm left. Và để tra cứu trong bảng này thì chúng ta sẽ nhớ đến công thức VLOOKUP. Và đầu tiên thì chúng ta sẽ viết như sau. Chúng ta sẽ viết từng phần một để cho các bạn dễ tìm hiểu. Bây giờ thì cần hàm left. Và bởi vì chúng ta sẽ biết hàm left ở đây. Cái mã hàng chúng ta lấy ở ô C4,1 cái. Ở đây thì các bạn có thể nhận thấy được rằng là cái tham số thứ 2 của hàm left là number of characters. Nó nằm ở trong một vuông, có nghĩa là không có cũng được. Trong trường hợp mà chúng ta không kể ra là sẽ lấy bao nhiêu ký tự từ bên trái sang, thì hàm left sẽ hiểu là chúng ta sẽ muốn lấy một ký tự duy nhất thôi. Vậy nên trong trường hợp này chúng ta chỉ lấy một ký tự duy nhất, nên chúng ta có thể đóng hoặc luôn lại. Bấm ENTER và chúng ta sẽ lặp lại công thức này cho toàn bộ các cột khác. Và bây giờ chúng ta có thể thấy được đây là cái ký tự đầu tiên của mã khách hàng chúng ta đã lấy ra được. Và với cái thể loại này thì chúng ta sẽ phải dóng vào cái bảng ở phía dưới này để chúng ta xem xem là các cái thể loại tương ứng là gì. Ví dụ như là C thì là cải lương, N thì là ca nhạc, v.v. Bây giờ thì chúng ta lại quay lại công thức ở OI4. Chúng ta sẽ làm như sau. Chúng ta sẽ sử dụng VLOOKUP với cái giá trị này. Thế nên là chúng ta lại bấm phím F2 để chúng ta có thể sửa được công thức. Và bây giờ chúng ta sẽ bấm VLOOKUP. Mở quạt ra. Cái giá trị LOOKUP chúng ta đã có rồi. Chính là LEFT của C4. Và cái bảng mà chúng ta sẽ tra cứu chính là bảng từ A16 cho đến B20. Và ở đây chúng ta lưu ý một điều. Là chúng ta phải khóa cái bảng này lại bằng cách là bấm phím F4. Các bạn có thể thấy được là khi mà chúng ta khóa cái tham chiếu này lại thì trước những cái chỉ số cột và chỉ số dòng nó đã xuất hiện cái ký tự đô la rồi. Và cái cột mà chúng ta muốn lấy lại ở trong bảng này chính là cột số 2. Thế nên chúng ta cho số 2 vào đây. Và tiếp theo là RANGE LOOKUP. Chúng ta sẽ có thể chọn TRUE hoặc là FALSE. Nhưng trong trường hợp này chúng ta phải chọn FALSE. Vì chúng ta muốn tra cứu một cách chính xác. Chúng ta chọn FALSE. Hoặc là bạn nào có thể điền vào đây thay vì FALSE là số 0 cũng được. Không có vấn đề gì. Đóng quốc lại. Bấm ENTER. Thì các bạn có thể nhìn thấy chúng ta hoàn toàn có thể điền tương tự cho các cái thể loại. Cho các cái băng địa khác. Vậy thì đó là thể loại. Bây giờ đến loại là phim lẻ hay là phim bộ. Thì các bạn nhìn vào cái yêu cầu số 3 ở đây. Căn cứ vào ký tự cuối cùng của mã khách thì chúng ta sẽ biết được phim đó là phim lẻ hay là phim bộ. Thế nên là chúng ta sẽ quan sát một chút phim lẻ hay là phim bộ nào. Ok. Bây giờ thì dựa vào cái ký tự cuối cùng ở phía bên tay phải. Thì chúng ta sẽ nhớ đến hàm RIGHT. Và để kiểm tra làm từng bước thì chúng ta sẽ hàm... Chúng ta sẽ dùng hàm RIGHT trước. Ở đây. Chúng ta sẽ biết vào RIGHT. Bấm vào mã khách hàng. Và tương tự như hàm LEFT. Nếu chúng ta không điền cái number of characters là một cái tham số không bắt buộc. Thì hàm RIGHT chỉ lấy một ký tự thôi. Và chúng ta có thể lặp lại cho các cái ô còn lại, cho các cái dòng còn lại. Thì ở đây chúng ta có thể nhìn thấy B thì là phim bộ. L thì là phim lẻ. Vậy nên chúng ta có thể viết cái loại này như sau. Nếu mà IF m hoặc ra RIGHT của C4 bằng B. B lớn đi. Thì có nghĩa là phim bộ. Chúng ta sẽ trả lại cái giá trị ở trong cái bảng thống kê này. Chúng ta sẽ không viết lại. Có nghĩa là phim bộ này. Chúng ta sẽ bấm phim F4 ở đây. Thì chúng ta có thể lặp lại công thức này cho các ô còn lại. Còn nếu mà không phải là phim bộ thì là trường hợp phim lẻ. Thế nên là chúng ta phải một cái là chúng ta có thể sử dụng cái VALUE IF FALSE ở đây rồi. Bấm phim lẻ này. Sau đó lại tiếp tục bấm F4. Và đóng gọi lại bấm ENTER. Thì các bạn có thể nhìn thấy ở trong cái cột G. Có nghĩa là loại phim bộ hay là phim lẻ. Chúng ta cũng có thể điền được một cách rất là dễ dàng. Bây giờ thì chúng ta sẽ phải tính theo đơn giá tuần. Chúng ta sẽ tính theo đơn giá tuần là dựa trên cái thể loại của chúng ta. Ở trong cái bảng phía dưới này. Từ B16 cho đến D20. Để làm điều đầu này chúng ta lại sử dụng cái công thức là VLOOKUP. Để tính đơn giá tuần thì chúng ta sẽ hành dấu bằng này. VLOOKUP này. Đơn vị lookup ở đây chính là thể loại. Bấm thể loại này. Sau đó là chọn bảng. Bảng của chúng ta kéo dài từ B16 cho đến D20. Chúng ta phải khóa bảng này lại. Bấm dấu phẩy một cái. COLUMN INDEX. Chúng ta sẽ lấy lại cái giá trị nào trong ba cái cột này. Ở đây chúng ta cần đơn giá tuần. Thế nên là 1. 2. 3. Chúng ta sẽ điền số 3 vào đây. Sau đó. Chúng ta sẽ điền FALSE ở cuối cùng. Bấm ENTER. Đó chính là cái đơn giá tuần mà chúng ta đã tính ra được. Nhập liệu lại. OK. Và tiếp theo là đến đơn giá ngày.