Các bản hack cuộc sống trong Excel dành cho những người liên quan đến báo cáo và xử lý dữ liệu

2024 Tác giả: Malcolm Clapton | [email protected]. Sửa đổi lần cuối: 2024-01-13 03:24

Trong bài đăng này, Renat Shagabutdinov, Trợ lý Tổng giám đốc của Mann, Ivanov và Ferber Publishing House, chia sẻ một số thủ thuật Excel life thú vị. Những mẹo này sẽ hữu ích cho bất kỳ ai liên quan đến các báo cáo, xử lý dữ liệu và tạo bản trình bày khác nhau.

Bài viết này chứa các kỹ thuật đơn giản để đơn giản hóa công việc của bạn trong Excel. Chúng đặc biệt hữu ích cho những người làm báo cáo quản lý, chuẩn bị các báo cáo phân tích khác nhau dựa trên tải xuống từ 1C và các báo cáo khác, trình bày biểu mẫu và sơ đồ từ chúng để quản lý. Tôi không giả vờ là người mới tuyệt đối - bằng hình thức này hay hình thức khác, những kỹ thuật này có thể đã được thảo luận trên các diễn đàn hoặc được đề cập trong các bài báo.

Các lựa chọn thay thế đơn giản cho VLOOKUP và HLOOKUP, nếu các giá trị mong muốn không nằm trong cột đầu tiên của bảng: LOOKUP, INDEX + SEARCH

Các hàm VLOOKUP và HLOOKUP chỉ hoạt động nếu các giá trị mong muốn nằm trong cột hoặc hàng đầu tiên của bảng mà từ đó bạn định lấy dữ liệu.

Nếu không, có hai tùy chọn:

1. Sử dụng hàm LOOKUP.

   Nó có cú pháp sau: LOOKUP (lookup_value; lookup_vector; result_vector). Nhưng để nó hoạt động chính xác, các giá trị của phạm vi view_vector phải được sắp xếp theo thứ tự tăng dần:

   

2. Sử dụng kết hợp các hàm MATCH và INDEX.

   Hàm MATCH trả về số thứ tự của một phần tử trong mảng (với sự trợ giúp của nó, bạn có thể tìm phần tử được tìm kiếm ở hàng nào trong bảng) và hàm INDEX trả về một phần tử mảng với một số nhất định (chúng ta sẽ tìm hiểu bằng cách sử dụng hàm MATCH).

   

   Cú pháp hàm:

   • TÌM KIẾM (search_value; search_array; match_type) - đối với trường hợp của chúng tôi, chúng tôi cần một loại đối sánh "khớp chính xác", nó tương ứng với số 0.

   • INDEX (array; line_number; [column_number]). Trong trường hợp này, bạn không cần chỉ định số cột, vì mảng bao gồm một hàng.

Cách nhanh chóng điền vào các ô trống trong danh sách

Nhiệm vụ là điền vào các ô trong cột có giá trị ở trên cùng (sao cho chủ đề nằm trong mỗi hàng của bảng và không chỉ ở hàng đầu tiên của khối sách về chủ đề):

Chọn cột "Chủ đề", nhấp vào dải băng trong nhóm "Trang chủ", nút "Tìm và Chọn" → "Chọn một nhóm ô" → "Các ô trống" và bắt đầu nhập công thức (nghĩa là đặt một dấu bằng ký) và tham chiếu đến ô ở trên cùng, chỉ bằng cách nhấp vào mũi tên lên trên bàn phím của bạn. Sau đó nhấn Ctrl + Enter. Sau đó, bạn có thể lưu dữ liệu đã nhận dưới dạng giá trị, vì các công thức không còn cần thiết nữa:

Cách tìm lỗi trong công thức

Tính toán một phần riêng biệt của công thức

Để hiểu một công thức phức tạp (trong đó các hàm khác được sử dụng làm đối số của hàm, tức là một số hàm được lồng trong các hàm khác) hoặc để tìm nguồn gốc của lỗi trong đó, bạn thường cần tính toán một phần của nó. Có hai cách dễ dàng:

1. Để tính toán một phần của công thức ngay trong thanh công thức, hãy chọn phần đó và nhấn F9:

   

   Trong ví dụ này, đã xảy ra sự cố với hàm TÌM KIẾM - các đối số đã được hoán đổi trong nó. Điều quan trọng cần nhớ là nếu bạn không hủy phép tính một phần của hàm và nhấn Enter, thì phần được tính sẽ vẫn là một số.

2. Bấm vào nút Tính Công thức trong nhóm Công thức trên ruy-băng:

   

   Trong cửa sổ xuất hiện, bạn có thể tính toán từng bước công thức và xác định lỗi xảy ra ở giai đoạn nào và trong hàm nào (nếu có):

   

Làm thế nào để xác định những gì một công thức phụ thuộc vào hoặc tham chiếu đến

Để xác định ô nào phụ thuộc vào công thức, trong nhóm Công thức trên ruy-băng, hãy bấm vào nút Ảnh hưởng đến Ô:

Các mũi tên xuất hiện để cho biết kết quả tính toán phụ thuộc vào điều gì.

Nếu biểu tượng được đánh dấu màu đỏ trong hình được hiển thị, thì công thức phụ thuộc vào các ô trên các trang tính khác hoặc trong các sách khác:

Bằng cách nhấp vào nó, chúng tôi có thể thấy chính xác vị trí của các ô hoặc phạm vi ảnh hưởng:

Bên cạnh nút "Các ô ảnh hưởng" là nút "Các ô phụ thuộc", hoạt động theo cách tương tự: nó hiển thị các mũi tên từ ô hiện hoạt có công thức đến các ô phụ thuộc vào nó.

Nút "Xóa mũi tên", nằm trong cùng một khối, cho phép bạn xóa các mũi tên đến các ô ảnh hưởng, các mũi tên đến các ô phụ thuộc hoặc cả hai loại mũi tên cùng một lúc:

Cách tìm tổng (số, trung bình) của các giá trị ô từ nhiều trang tính

Giả sử bạn có một số trang tính cùng loại với dữ liệu mà bạn muốn thêm, đếm hoặc xử lý theo một số cách khác:

Để thực hiện việc này, trong ô mà bạn muốn xem kết quả, hãy nhập một công thức chuẩn, ví dụ: SUM (SUM), và chỉ định tên của trang tính đầu tiên và cuối cùng từ danh sách các trang tính mà bạn cần xử lý. đối số, được phân tách bằng dấu hai chấm:

Bạn sẽ nhận được tổng các ô có địa chỉ B3 từ các trang tính "Data1", "Data2", "Data3":

Địa chỉ này hoạt động cho các trang tính nằm nhất quán … Cú pháp như sau: = FUNCTION (first_list: last_list! Range tham chiếu).

Cách tự động tạo các cụm từ mẫu

Sử dụng các nguyên tắc cơ bản làm việc với văn bản trong Excel và một vài hàm đơn giản, bạn có thể chuẩn bị các cụm từ mẫu cho báo cáo. Một số nguyên tắc làm việc với văn bản:

• Chúng tôi nối văn bản bằng cách sử dụng dấu & (bạn có thể thay thế nó bằng hàm CONCATENATE, nhưng điều đó không có ý nghĩa nhiều).
• Văn bản luôn được viết trong dấu ngoặc kép, tham chiếu đến các ô có văn bản luôn không có.
• Để nhận ký tự dịch vụ "dấu ngoặc kép", hãy sử dụng hàm CHAR với đối số 32.

Ví dụ về cách tạo cụm từ mẫu bằng công thức:

Kết quả:

Trong trường hợp này, ngoài chức năng CHAR (để hiển thị báo giá), chức năng IF được sử dụng, cho phép bạn thay đổi văn bản tùy thuộc vào việc có xu hướng bán hàng tích cực hay không và chức năng TEXT, cho phép bạn hiển thị số ở bất kỳ định dạng nào. Cú pháp của nó được mô tả dưới đây:

TEXT (giá trị; định dạng)

Định dạng được chỉ định trong dấu ngoặc kép, giống như khi bạn nhập định dạng tùy chỉnh trong cửa sổ Định dạng Ô.

Các văn bản phức tạp hơn cũng có thể được tự động hóa. Trong thực tế của tôi, có sự tự động hóa các nhận xét dài, nhưng thường xuyên cho báo cáo quản lý ở định dạng “INDICATOR giảm / tăng XX so với kế hoạch, chủ yếu do sự tăng trưởng / suy giảm của FACTOR1 xuống XX, tăng trưởng / suy giảm của FACTOR2 bởi YY …”với một danh sách các yếu tố thay đổi. Nếu bạn viết những bình luận như vậy thường xuyên và quá trình viết chúng có thể được thuật toán hóa, bạn nên phân vân một lần để tạo ra một công thức hoặc một macro sẽ giúp bạn tiết kiệm ít nhất một số công việc.

Cách lưu trữ dữ liệu trong mỗi ô sau khi ghép

Khi bạn hợp nhất các ô, chỉ một giá trị được giữ lại. Excel cảnh báo về điều này khi cố gắng hợp nhất các ô:

Theo đó, nếu bạn có một công thức tùy thuộc vào từng ô, nó sẽ ngừng hoạt động sau khi kết hợp chúng (lỗi # N / A ở dòng 3-4 của ví dụ):

Để hợp nhất các ô mà vẫn bảo toàn dữ liệu trong mỗi ô (có lẽ bạn có một công thức như trong ví dụ trừu tượng này; có thể bạn muốn hợp nhất các ô, nhưng giữ tất cả dữ liệu cho tương lai hoặc cố ý ẩn nó đi), hãy hợp nhất bất kỳ ô nào trên trang tính, chọn chúng, sau đó sử dụng lệnh Format Painter để chuyển định dạng sang các ô mà bạn cần kết hợp:

Cách tạo tổng hợp từ nhiều nguồn dữ liệu

Nếu bạn cần tạo một tổng hợp từ nhiều nguồn dữ liệu cùng một lúc, bạn sẽ phải thêm "PivotTable và Trình hướng dẫn Biểu đồ" vào dải băng hoặc bảng điều khiển truy cập nhanh, có tùy chọn như vậy.

Bạn có thể thực hiện việc này như sau: "Tệp" → "Tùy chọn" → "Thanh công cụ truy cập nhanh" → "Tất cả lệnh" → "PivotTable và Trình hướng dẫn biểu đồ" → "Thêm":

Sau đó, một biểu tượng tương ứng sẽ xuất hiện trên ruy-băng, nhấp vào biểu tượng đó gọi cùng một trình hướng dẫn:

Khi bạn nhấp vào nó, một hộp thoại xuất hiện:

Trong đó, bạn cần chọn mục "Trong một số phạm vi hợp nhất" và nhấp vào "Tiếp theo". Trong bước tiếp theo, bạn có thể chọn "Tạo trường một trang" hoặc "Tạo trường trang". Nếu bạn muốn đặt tên cho từng nguồn dữ liệu một cách độc lập, hãy chọn mục thứ hai:

Trong cửa sổ tiếp theo, hãy thêm tất cả các phạm vi trên cơ sở mà trục xoay sẽ được tạo và đặt tên cho chúng:

Sau đó, trong hộp thoại cuối cùng, chỉ định nơi báo cáo bảng tổng hợp sẽ được đặt - trên trang tính hiện có hoặc trang tính mới:

Báo cáo bảng tổng hợp đã sẵn sàng. Trong bộ lọc "Trang 1", bạn chỉ có thể chọn một trong các nguồn dữ liệu, nếu cần:

Cách tính số lần xuất hiện của văn bản A trong văn bản B ("Biểu giá MTS SuperMTS" - hai lần xuất hiện của chữ viết tắt MTS)

Trong ví dụ này, cột A chứa một số dòng văn bản và nhiệm vụ của chúng ta là tìm xem mỗi dòng chứa văn bản tìm kiếm nằm trong ô E1:

Để giải quyết vấn đề này, bạn có thể sử dụng một công thức phức tạp bao gồm các hàm sau:

1. DLSTR (LEN) - tính độ dài của văn bản, đối số duy nhất là văn bản. Ví dụ: DLSTR ("máy") = 6.
2. SUBSTITUTE - thay thế một văn bản cụ thể trong một chuỗi văn bản bằng một văn bản khác. Cú pháp: SUBSTITUTE (text; old_text; new_text). Ví dụ: SUBSTITUTE (“car”; “auto”; “”) = “mobile”.
3. UPPER - thay thế tất cả các ký tự trong một chuỗi bằng chữ hoa. Đối số duy nhất là văn bản. Ví dụ: UPPER (“machine”) = “CAR”. Chúng tôi cần chức năng này để thực hiện các tìm kiếm không phân biệt chữ hoa chữ thường. Rốt cuộc, UPPER ("xe") = UPPER ("Máy")

Để tìm sự xuất hiện của một chuỗi văn bản nhất định trong một chuỗi khác, bạn cần xóa tất cả các lần xuất hiện của nó trong chuỗi gốc và so sánh độ dài của chuỗi kết quả với chuỗi ban đầu:

DLSTR (“Siêu MTS thuế quan”) - DLSTR (“Siêu thuế quan”) = 6

Và sau đó chia sự khác biệt này cho độ dài của chuỗi mà chúng tôi đang tìm kiếm:

6 / DLSTR (“MTS”) = 2

Chính xác là hai lần dòng "MTS" được bao gồm trong bản gốc.

Vẫn là viết thuật toán này bằng ngôn ngữ công thức (chúng ta hãy biểu thị bằng "văn bản" văn bản mà chúng tôi đang tìm kiếm các lần xuất hiện và bằng "tìm kiếm" - một văn bản có số lần xuất hiện mà chúng tôi quan tâm):

= (DLSTR (văn bản) -LSTR (SUBSTITUTE (LÊN (văn bản); LÊN (tìm kiếm), ""))) / DLSTR (tìm kiếm)

Trong ví dụ của chúng tôi, công thức trông giống như sau:

= (DLSTR (A2) -LSTR (SUBSTITUTE (LÊN (A2), LÊN ($ E $ 1), “”))) / DLSTR ($ E $ 1)