Excel Life hack cho những người liên quan đến báo cáo và xử lý dữ liệu

<

Trong bài đăng này, Renat Shagabutdinov, trợ lý của tổng giám đốc của nhà xuất bản Mann, Ivanov và Ferber, chia sẻ các cuộc sống tuyệt vời trên Excel. Những mẹo này sẽ hữu ích cho bất kỳ ai tham gia vào báo cáo, xử lý dữ liệu và tạo bản trình bày khác nhau.

Renat không phải là lần đầu tiên một tác giả khách trên Pc-Article. Trước đó chúng tôi đã xuất bản tài liệu tuyệt vời từ anh ấy về cách lập kế hoạch đào tạo: sách chính và tài nguyên trực tuyến, cũng như thuật toán từng bước để tạo kế hoạch đào tạo.

Bài viết này chứa các thủ thuật đơn giản để làm việc trong Excel dễ dàng hơn. Chúng đặc biệt hữu ích cho những người tham gia báo cáo quản lý, chuẩn bị nhiều báo cáo phân tích dựa trên các bản tải xuống 1C và các báo cáo khác, các hình thức trình bày và biểu đồ cho họ để quản lý. Tôi không giả vờ là một người mới tuyệt đối - dưới hình thức này hay hình thức khác, những phương pháp này có lẽ đã được thảo luận trong 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 CDF và CGP nếu các giá trị bạn đang tìm kiếm không nằm trong cột đầu tiên của bảng: XEM, INDEX + MATCH

Các hàm VLOOKUP và HLOOKUP chỉ hoạt động nếu các giá trị bạn đang tìm kiếm nằm trong cột hoặc hàng đầu tiên của bảng mà bạn dự định nhận dữ liệu.

Trong các trường hợp khác, có hai lựa chọn:

  1. Sử dụng chức năng LOOKUP.
    Nó có cú pháp sau: VIEW ( looking_value; view_vector; result_vector ). Nhưng để hoạt động chính xác, điều cần thiết là các giá trị của phạm vi vector_view được sắp xếp theo thứ tự tăng dần:
  2. Sử dụng kết hợp MATCH và INDEX.
    Hàm MATCH trả về số thứ tự của phần tử trong mảng (với sự trợ giúp của bạn, bạn có thể tìm thấy hàng nào của bảng mà phần tử bạn đang tìm) và hàm INDEX trả về phần tử mảng với số đã chỉ định (chúng ta sẽ tìm ra bằng cách sử dụng hàm MATCH). Cú pháp của các hàm:
    • MATCH ( giá trị tìm kiếm; mảng tìm kiếm; loại đối sánh ) - đối với trường hợp của chúng tôi, chúng tôi cần loại ánh xạ chính xác phù hợp với bản đồ, nó tương ứng với chữ số 0.
    • INDEX ( mảng; line_number; [cột_number] ). Trong trường hợp này, số cột là không bắt buộc, vì mảng bao gồm một hàng.

Làm thế nào để 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 với các giá trị ở trên (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 của Đối tượng trực tuyến, nhấp vào nút Tìm và tô sáng nút trên nhóm Trang chủ và chọn nhóm ô → Các ô trống và bắt đầu nhập công thức (nghĩa là đặt dấu bằng) và chỉ vào ô từ trên cùng bằng cách nhấn mũi tên lên trên bàn phím. Sau đó, nhấn Ctrl + Enter. Sau đó, bạn có thể lưu dữ liệu dưới dạng giá trị, vì các công thức không còn cần thiết:

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ố hàm, nghĩa là, một số hàm được lồng trong các hàm khác) hoặc để tìm lỗi trong nó, thường cần phải tính phần của nó. Có hai cách dễ dàng:

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


    Trong ví dụ này, có một vấn đề với hàm SEARCH - các đối số được trộn lẫn trong đó. Điều quan trọng cần nhớ là nếu bạn không hủy tính toán của phần hàm và nhấn Enter, thì phần được tính sẽ vẫn là một số.

  2. Nhấp vào nút Công thức tính toán của nhóm trong nhóm Công thức tính toán trên nhóm ruy băng:

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

Làm thế nào để xác định những gì công thức phụ thuộc vào hoặc đề cập đến

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

Mũi tên xuất hiện chỉ ra kết quả của phép tính phụ thuộc vào.

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

Nhấp vào nó, chúng ta sẽ 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 "Ảnh hưởng đến các ô" 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 loại bỏ mũi tên để ảnh hưởng đến 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 giá trị tổng (số lượng, trung bình) của các ô từ một số trang tính

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

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

Bạn sẽ nhận được tổng số các ô có địa chỉ B3 từ các tờ dữ liệu của Data Data1,, Data Data2,, Data Data3:

Địa chỉ này hoạt động cho các tờ được sắp xếp trong loạt . Cú pháp như sau: = FUNCTION ( first_list: last_list! Liên kết đến phạm vi ).

Cách tự động xây dựng 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 chức năng đơ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 hợp nhất văn bản với dấu & (bạn có thể thay thế nó bằng hàm CONCATENATE, nhưng điều này không có nhiều ý nghĩa).
  • Văn bản luôn được viết trong dấu ngoặc kép, tham chiếu ô với văn bản luôn luôn không có.
  • Để lấy "dấu ngoặc kép" của ký tự dịch vụ, chúng tôi sử dụng hàm CHAR với đối số 32.

Một ví dụ về việc 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ị dấu ngoặc kép), chức năng IF (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 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:

Văn bản ( giá trị; định dạng )

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

Bạn có thể tự động hóa các văn bản phức tạp hơn. Trong thực tế của tôi, đã có sự tự động hóa từ lâu, nhưng các nhận xét thường xuyên về báo cáo quản lý theo định dạng INDICIC đã giảm / tăng XX so với kế hoạch chủ yếu do sự tăng / giảm của FACTOR1 lên XX, tăng / giảm FACTOR2 của 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 - nó có giá trị 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 dữ liệu trong mỗi ô sau khi hợp nhất

Khi hợp nhất các ô, chỉ có một giá trị được lưu. 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 phụ thuộc vào từng ô, nó sẽ ngừng hoạt động sau khi hợp nhất chúng (lỗi # Ấn Độ / hàng trong các ví dụ 3-4):

Để hợp nhất các ô và đồng thời lưu 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ó lẽ bạn muốn hợp nhất các ô, nhưng lưu tất cả dữ liệu cho tương lai hoặc ẩn chúng một cách có chủ ý), hợp nhất bất kỳ ô nào trên trang tính, chọn chúng và sau đó sử dụng lệnh "Định dạng mẫu" để chuyển định dạng sang các ô mà bạn cần hợp nhất:

Làm thế nào để xây dựng một bản tóm tắt của một số dữ liệu sv

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

Bạn có thể thực hiện việc này như sau: Tập tin Tập tin và Bộ chỉnh sửa Truy cập nhanh Bảng → Truy cập nhanh Bảng điều khiển → → Tất cả các lệnh Lệnh → Bảng Pivot và Biểu đồ Thuật sĩ Phù → → Thêm 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 đó gọi chính chủ đó:

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

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

Trong cửa sổ tiếp theo, thêm tất cả các phạm vi mà bản tóm tắt sẽ được xây dựng và đặt tên cho chúng:

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

Báo cáo bảng trụ đã sẵn sàng. Trong bộ lọc Trang 1, bạn chỉ có thể chọn một trong các 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 thuế MTS SuperMTS" - hai lần xuất hiện của chữ viết tắt MTS)

Trong ví dụ này, có một số dòng văn bản trong cột A và nhiệm vụ của chúng tôi là tìm ra bao nhiêu lần trong mỗi dòng văn bản được yêu cầu được tìm thấy, 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 toán độ dài của văn bản, đối số duy nhất là văn bản. Ví dụ: DLSTR (máy máy trực tuyến) = 6.
  2. SUBSTITUTE - thay thế một văn bản nhất định trong một dòng văn bản bằng một văn bản khác. Cú pháp: SUB ( văn bản; star lòng; new lòng ). Ví dụ: SUB (Tải về xe ô tô;
  3. UPPER (UPPER) - thay thế tất cả các ký tự trong chuỗi thành chữ hoa. Đối số duy nhất là văn bản. Ví dụ: UPPER (máy máy trực tiếp) = máy MÁY. Chúng ta sẽ cần chức năng này để thực hiện tìm kiếm không phân biệt chữ hoa chữ thường. Sau tất cả, UPPER (máy Machine trực tiếp) = UPPER (máy Machine trực tiếp)

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

DLSTR (Thuế MTS Super MTS Thuế quan) - DLSTR (Đá siêu thuế siêu tốc) = 6

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

6 / DLSTR (Nhật Bản MTS)) = 2

Đó là dòng MTS phạm lỗi trong hai lần.

Vẫn còn phải viết thuật toán này bằng ngôn ngữ của các công thức (chúng tôi biểu thị bằng văn bản của Cameron, văn bản mà chúng tôi đang tìm kiếm sự xuất hiện, và điều mong muốn là một điều mà chúng tôi quan tâm):

= (DLSTR ( văn bản ) -DLSTR (FIT (PRESP ( văn bản ); LỢI NHUẬN ( bắt buộc ); ""))) / DLSTR ( bắt buộc )

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

= (DLSTR (A2) -DLSTR (FIT (PROPISN (A2); PROPISN ($ E $ 1); mật))) / DLSTR ($ E $ 1)

<

Bài ViếT Phổ BiếN