Thủ Thuật Excel: Thiết lập các công thức nâng cao (phần 1)

Excel là một chương trình linh hoạt có nhiều công dụng, từ việc có chức năng như một cuốn sổ dùng để tính toán, cho đến một hệ thống cơ sở dữ liệu phẳng, một công cụ để giải phương trình, một chương trình tính toán tuyệt vời. Tuy nhiên, với đa số những người sử dụng là những nhà kinh doanh...
Nội dung trích xuất từ tài liệu:
Thủ Thuật Excel: Thiết lập các công thức nâng cao (phần 1) Học Excel -Thủ Thuật ExcelThiết lập các công thức nâng cao (phần 1)Hướng dẫn thiết lập các công thức nâng cao (phần 1):Excel là một chương trình linh hoạt có nhiều công dụng, từ việc có chức năngnhư một cuốn sổ dùng để tính toán, cho đến một hệ thống cơ sở dữ liệuphẳng, một công cụ để giải phương trình, một chương trình tính toán tuyệtvời. Tuy nhiên, với đa số những người sử dụng là những nhà kinh doanh, sởtrường của Excel là xây dựng những mô hình cho phép định lượng nhữngkhía cạnh cụ thể về công việc kinh doanh. Khung sườn của mô hình kinhdoanh được tạo từ những cụm dữ liệu được nhập vào hoặc được sao chép vàocác bảng tính. Nhưng nhân tố quyết định của mô hình và động lực chính đằngsau nó là tập hợp các công thức để tổng kết số liệu, trả lời cho những câu hỏivà đưa ra những dự đoán.Ở chương, “Thiết lập các công thức đơn giản phần 1 và phần 2“, bạn đã thấyrằng chỉ với một dấu bằng khiêm tốn cùng với một tập hợp các toán tử vàtoán hạng, bạn có thể tạo nên những công thức mạnh mẽ, hữu dụng. NhưngExcel còn có nhiều thủ thuật khác và những kỹ thuật này cho phép tạo ra các công thức mạnh mẽ hơn, có thể đưa những mô hình kinh doanh của bạn lên tầm cao hơn. Những nội dung chính trong chương này: Làm việc với các mảng Sử dụng sự lặp lại và các tham chiếu tuần hoàn Hợp nhất dữ liệu trên nhiều trang bảng tính Áp dụng các quy tắc hiệu lực hóa liệu (data-validation) vào các ô Sử dụng các điều khiển hộp thoại trong một bảng tính 4.1. Làm việc với các mảng Khi bạn làm việc với một dãy các ô, dường như bạn đang làm việc với một thứ duy nhất. Trong thực tế, Excel xem dãy này như một “đơn vị số” riêng biệt. Điều này hơi trái với chủ đề của phần này: mảng (array). Mảng là một nhóm ô hoặc giá trị mà Excel xem như là một đơn vị. Ví dụ, trong một dãy được cấu hình dưới dạng một mảng, Excel không còn xử lý các ô một cách riêng lẻ nữa. Thay vào đó, nó làm việc với tất cả các ô trong cùng một lúc, nghĩa là cho phép bạn làm những điều như áp dụng một công thức vào mỗi ô trong dãy chỉ với một thao tác. Bạn có thể tạo các mảng bằng cách chạy một hàm mà kết quả trả về là một mảng (ví dụ như hàm LOGEST() hoặc bằng cách nhập một công thức mảng (array formula), là một công thức đơn nhưng sử dụng các mảng làm đối số hoặc nhập các kết quả của nó trong nhiều ô. 4.1.1. Sử dụng các công thức mảngSau đây là một ví dụ đơn giản minh họa công thức mảng làm việc như thếnào. Trong bảng tính Expenses ở hình 4.1, các ô tính tổng 2008 BUDGETtính toán bằng cách sử dụng từng công thức riêng biệt trong mỗi ô, như sauđây:January 2008 BUDGET: =C11*$C$3February 2008 BUDGET: =D11*$C$3March 2008 BUDGET: =E11*$C$3Bạn có thể thay thế cả 3 công thức ấy bằng một công thức mảng đơn giản,theo từng bước sau đây:1. Chọn dãy ô mà bạn muốn dùng công thức mảng. Ví dụ trong bảng tính2008 BUDGET, bạn chọn dãy ô C13:E13.2. Gõ công thức, và ở những nơi mà bạn thường nhập một tham chiếu đếnmột ô, hãy nhập vào đó tham chiếu đến dãy bao gồm những ô mà bạn muốndùng để tính toán. Đừng (tôi nhắc lại, đừng) nhấn Enter sau khi nhập xongcông thức. Trong ví dụ này, bạn nhập công thức sau:= C11:E11*$C$33. Để nhập một công thức ở dạng một công thức mảng,nhấn Ctrl+Shift+Enter.Các ô 2008 BUDGET (C13, D13 và E13) bây giờ đều có chung một côngthức:{=C11:E11*$C$3}Figure 4.1 (hình 4.1)Nói cách khác, bạn có thể nhập một công thức vào 3 ô khác nhau chỉ với mộtthao tác. Điều này có thể giúp bạn tiết kiệm được một lượng thời gian đángkể so với việc nhập cùng một công thức vào nhiều ô khác nhau.Chú ý rằng công thức trên được đặt trong một cặp dấu ngoặc {}. Điều nàycho ta biết đó một công thức mảng. (Khi bạn nhập một công thức mảng, bạnkhông bao giờ tự tay nhập cặp dấu này, mà Excel sẽ tự thêm nó vào, sau khibạn nhấn Ctrl+Shift+Enter. (Nói thêm, khi bạn nhấn F2 để sửa công thức,bạn sẽ thấy cặp dấu {} tự động biến mất; và nhớ rằng, sau khi sửa công thức,bạn phải nhấn Ctrl+Shift+Enter chứ không phải nhấn Enter như bìnhthường.)NOTE: Như đã nói ở trên, bởi vì Excel xem mảng như một đơn vị, nên bạnkhông thể di chuyển hay xóa một thành phần nào trong một mảng. Nếu bạncần phải làm việc với một mảng, bạn phải chọn toàn bộ mảng. Nếu bạn muốnthay đổi kích thước của một mảng, bạn chọn nó, kích hoạt thanh công thức(formula bar), rồi nhấn Ctrl+Enter để chuyển công thức trong đó trở thànhcông thức bình thường. Bây giờ bạn có thể chọn lại một dãy ô nhỏ hơn (hoặclớn hơn) và nhập lại công thức mảng.Ghi chú thêm: Bạn có thể chọn nhanh một mảng bằng cách kích hoạt một ôbất kỳ trong mảng đó và nhấn Ctrl+/ (dấu gạch chéo)■ Tìm hiểu các công thức mảngĐể hiểu Excel xử lý một mảng như thế nào, bạn cần ghi nhớ rằng Excel luônluôn thiết lập một sự tương ứng giữa các ô trong mảng và các ô của bất kỳdãy nào mà bạn đã nhập vào công thức mảng.Trong ví ...