Hướng dẫn tạo Menu liên kết tới các sheet bằng hàm Hyperlink và cách sử dụng công thức đếm số lần xuất hiện trong Table
Làm thế nào để tạo MENU liên kết tới các sheet bằng hàm Hyperlink và Cách sử dụng công thức đếm số lần xuất hiện trong Table. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.
Mục lục nội dung
Hướng dẫn tạo Menu liên kết tới các sheet bằng hàm Hyperlink và cách sử dụng công thức đếm số lần xuất hiện trong Table
1. Cách sử dụng công thức đếm số lần xuất hiện trong Table
Công thức đếm số lần xuất hiện trong Table
Hàm chúng ta sử dụng là hàm COUNTIF:
- Range: Vùng đếm ở đây cố định điểm đầu A2, bắt đầu vùng đếm sẽ là từ ô A2
- Criteria: Điều kiện đếm là nội dung ô A2
Khi filldown (sao chép công thức xuống phía dưới) chúng ta sẽ lấy được kết quả là số lần xuất hiện của các nội dung tên tương ứng theo cột A.
Đây là cách làm thông thường với đối tượng RANGE
Vậy với đối tượng TABLE thì sao?
Sử dụng là hàm COUNTIF
Đây là vấn đề rất hay xảy ra khi chúng ta sử dụng hàm COUNTIF trong đối tượng TABLE:
- Ban đầu công thức hoạt động đúng từ dòng 2 tới dòng 4 (Các công thức tại H2, H3, H4)
- Khi thêm tiếp dữ liệu vào table ở các dòng 5, 6, 7 thì hàm COUNTIF tự động tạo ra ở các ô H5, H6, H7 nhưng không chính xác.
- Nguyên nhân là bởi khi thêm nội dung vào Table thì tọa độ của điểm cuối trong Table sẽ tự động thay đổi giới hạn. Chính vì thế công thức mới tự động tạo ra đã không đúng nữa.
Cách khắc phục việc này như thế nào? Hay cụ thể hơn là sử dụng hàm COUNTIF như thế nào để đảm bảo kết quả vẫn đúng khi thêm giá trị mới trong Table, chúng ta cùng tìm hiểu cách làm như sau:
Công thức =COUNTIF([@[Họ tên]]:INDEX([Họ tên],1),[@[Họ tên]])
[@[Họ tên]] được hiểu là 1 giá trị cụ thể tại dòng đó. Nội dung này được lấy khi click chuột vào cột Họ tên, dòng ngang hàng với ô đặt công thức.
[Họ tên] được hiểu là toàn bộ cột Họ tên trong table. Nội dung này được lấy bằng cách chọn toàn bộ vùng nội dung cột Họ tên.
Hàm Index([Họ tên],1) là lấy giá trị đầu tiên trong table (điểm bắt đầu)
vùng [@[Họ tên]]:INDEX([Họ tên],1) chính là tính từ điểm bắt đầu tới dòng hiện tại trong vùng đếm => Tránh việc tự động xác định tới dòng cuối của table
=> Toàn bộ công thức được hiểu là: Đếm giá trị ở dòng hiện tại trong table nằm trong cột Họ tên, tính từ dòng bắt đầu tới dòng hiện tại.
Công thức này về bản chất là tương tự với công thức COUNTIF của range, nhưng cách diễn đạt trong Table và trong Range lại có sự khác nhau, để ra được kết quả giống nhau. Đây là một sự chú ý quan trọng khi chúng ta làm việc với Table trong excel.
2. Hướng dẫn cách tạo MENU liên kết tới các sheet bằng hàm Hyperlink
Không phải lúc nào chúng ta cũng quen với việc tập hợp tất cả dữ liệu trong cùng 1 sheet, mà nhiều khi chúng ta có thói quen tổ chức dữ liệu trên nhiều sheet có cấu trúc tương tự nhau, chỉ khác nhau về Tên đối tượng. Chúng ta có ví dụ như sau:
Công ty A có 10 khách hàng, và nhân viên của công ty thực hiện việc theo dõi bán hàng cho 10 khách hàng này tại 10 sheet có cấu trúc giống nhau trong 1 workbook:
Tạo MENU liên kết tới các sheet
Tuy nhiên việc tạo Hyperlink khá thủ công và mất thời gian. Do đó chúng ta có thể kết hợp việc lấy tên Sheet đã tìm hiểu ở bài học trước kết hợp với hàm Hyperlink trong Excel.
Bước 1: Tạo danh sách các Sheet có trong Workbook
Kết quả thu được như sau (Vùng A1:B11)
Tạo danh sách Sheet có trong Workbook
Bước 2: Tạo liên kết với hàm Hyperlink
Cấu trúc hàm Hyperlink:
=HYPERLINK(link_location, [friendly_name])
- link_location là địa chỉ cần liên kết tới.
- friendly_name là tên người dùng sử dụng cho dễ gọi, dễ nhớ
Chúng ta biết rằng Excel thể hiện tham chiếu tới 1 vị trí ở sheet khác như sau:
Tên Sheet + Dấu chấm than + Tọa độ
Chú ý: Tên Sheet phải đặt trong dấu nháy đơn để có thể bao gồm cả những tên sheet có sử dụng dấu cách.
Bởi vì tên các Sheet đã lấy được ở cột B, do đó chúng ta có thể tạo đối tượng cho link_location như sau:
- Thành phần 1: Dấu # và dấu ‘ đặt cạnh nhau và đặt trong dấu nháy kép
- Thành phần 2: Tên sheet là kết quả ở cột B
- Thành phần 3: Dấu nháy đơn và dấu chấm than, tọa độ A1 được đặt cạnh nhau và đặt trong dấu nháy kép
=> Ba thành phần trên được nối với nhau bởi dấu &
Friendly_name có thể sử dụng trực tiếp tên Sheet hoặc tên chúng ta tự đặt ra cho từng sheet để dễ gọi
Như vậy với công thức Hyperlink chúng ta có thể tạo liên kết tới từng Sheet trong Workbook một cách dễ dàng, điều này đặc biệt có ích khi làm việc trên những workbook có nhiều sheet, có tên Sheet dài, khó sử dụng thanh SheetTab.
Như vậy bài viết đã hướng dẫn cho bạn cách tạo MENU liên kết tới các sheet bằng hàm Hyperlink và cách sử dụng công thức đếm số lần xuất hiện trong Table. Chỉ cần một số thao tác cơ bản, bạn đã có thể tạo manu liên kết một cách nhanh chóng rồi. Chúc các bạn thực hiện thao tác thành công!
Tham khảo thêm
- doc Hướng dẫn vẽ biểu đồ đường và biểu đồ tròn trong Excel mà bạn nên biết
- doc Cách dùng hàm AVERAGE, MAX, MIN
- doc Cách sử dụng hàm Sum và hàm SUMIF trong Excel
- doc Hướng dẫn hàm tính tiền tích lũy và khấu hao tài sản trong Excel
- doc Hướng dẫn cách tạo chú thích cho ô và thay đổi màu cho ô theo giá trị trong Excel
- doc Hướng dẫn cách gộp ô và cố định hàng, cốt trong Excel
- doc Hướng dẫn cách gộp nhiều Sheet vào một Sheet và cách xuất dữ liệu ra file Excel mới
- doc Hướng dẫn vẽ biểu đồ thanh/ cột chồng trong Excel một cách nhanh chóng nhất
- doc Hướng dẫn thay đổi cột kết quả và cách lọc giá trị trong Pivottable của Excel MacBook
- doc Hướng dẫn xóa cột hoặc dòng Grand ToTal và cách để làm mới một Pivottable trong Excel
- doc Hướng dẫn thủ thuật làm mới một Pivot Table trong Excel một cách nhanh chóng nhất
- doc Hướng dẫn cách cố định các đối tượng với hàng, cột và viết hàm tìm dòng cuối cùng có dữ liệu trong VBA
- doc Hướng dẫn cách đánh số thứ tự ngày tháng bỏ qua các ngày cuối tuần và sử dụng Flash Fill để tách họ tên trong Excel
- doc Hướng dẫn cách xoá bỏ giá trị trùng lặp với Power Query và các cách để Transpose trong Excel
- doc Giới thiệu cách sử dụng Data Validation List với nguồn là Table và cách loại bỏ dấu chấm trong số điện thoại
- doc Hướng dẫn lập sổ nhật ký chung và sổ cái bằng hàm “HeoSQL”
- doc Hướng dẫn sử dụng hàm VLOOKUP trả về mảng giá trị và sử dụng hàm VLOOKUP lồng VLOOKUP trong Excel
- doc Hướng dẫn đếm số lượng giá trị duy nhất trong 1 vùng và cách tách rời từng ký tự một ra khỏi chuỗi
- doc Hướng dẫn lấy ra danh sách tên các Sheet và tạo phím tắt đếm số lượng Sheet ẩn, hiện trong Excel
- doc Hướng dẫn cách lấy dữ liệu từ 1 File Excel đang đóng và Cách lấy lại File Excel do bị tắt đột ngột
- doc Phương pháp để Protect và UnProtect nhiều Sheet cùng 1 lúc trong Excel một cách nhanh chóng
- jpg Hướng dẫn dùng INDEX và MATCH trong Excel một cách nhanh chóng nhất
- doc Hướng dẫn sửa lỗi không chọn được Visual Basic, Macro trong thẻ Developer và cách gộp các file CSV vào trong một file chung nhanh chóng
- doc Hướng dẫn chi tiết cách sử dụng Quick Access Toolbar trong Excel
- doc Hướng dẫn thay đổi vùng in tự động và viết tắt họ tên trên Excel bằng VBA
- doc Hướng dẫn lấy tên Worksheet đang làm việc một cách nhanh chóng nhất
- doc Hướng dẫn cách tạo Combobox trong Excel một cách nhanh chóng nhất
- doc Hướng dẫn tạo danh sách không có giá trị trùng trong combobox
- doc Hướng dẫn tra cứu thời gian và tìm giá trị gần đúng nhất bằng VBA
- doc Cách dùng hàm SUMIFS lập báo cáo tổng hợp một cách nhanh chóng nhất
- doc Hướng dẫn cách tính trung bình của một số theo từng lần và sử dụng hàm Subtotal để đánh số thứ tự kết quả lọc Autofilter
- doc Hướng dẫn lập bảng kế hoạch thanh toán hóa đơn trong Excel nhanh chóng nhất
- doc Hướng dẫn cách sử dụng Regular Expression và cách tra cứu thời gian bằng VBA
- doc VBA là gì? VBA trong Excel giúp ích gì cho công việc của bạn?
- doc Vẽ và phân tích biểu đồ xu hướng với Data Analysis trong Excel
- doc Hướng dẫn cách sử dụng hàm PMT tính số tiền thanh toán hàng kỳ cho khoản vay và cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong Excel
- doc Hướng dẫn chọn nội dung xuất hiện nhiều nhất trong danh sách và sử dụng vòng tính lặp trong Excel