Phân tích ABC trong Excel

Một trong những phương thức quản lý và hậu cần chính là phân tích ABC. Với nó, bạn có thể phân loại các tài nguyên của doanh nghiệp, hàng hóa, khách hàng, v.v. Theo mức độ quan trọng. Đồng thời, theo mức độ quan trọng, mỗi đơn vị trên được gán một trong ba loại: Chương trình A, B hoặc C. Excel có trong các công cụ hành lý giúp dễ dàng thực hiện loại phân tích này. Hãy tìm ra cách sử dụng chúng, và phân tích abc là gì.

Sử dụng Phân tích ABC.

Phân tích ABC là một loại nâng cao và thích nghi với các điều kiện hiện đại đối với nguyên tắc Pareto. Theo phương pháp ứng xử của nó, tất cả các yếu tố của phân tích được chia thành ba loại theo mức độ quan trọng:

• Loại A - các yếu tố có sự kết hợp của hơn 80% trọng lượng riêng;
• Loại B - các yếu tố có sự kết hợp dao động từ 5% đến 15% trọng lượng riêng;
• Loại C - Các yếu tố còn lại, tổng trọng lượng của đó là 5% và trọng lượng riêng đặc biệt.

Các công ty riêng biệt Sử dụng các kỹ thuật nâng cao hơn và chia các mục không có 3, nhưng bởi 4 hoặc 5 nhóm, nhưng chúng tôi sẽ dựa vào sơ đồ phân tích ABC cổ điển.

Phương pháp 1: Phân tích với Sắp xếp

Phân tích Excel ABC được thực hiện bằng cách sử dụng Sắp xếp. Tất cả các mục được sắp xếp từ nhờn đến ít hơn. Sau đó, chia sẻ tích lũy của từng yếu tố được tính toán, trên cơ sở mà nó được gán một danh mục nhất định. Hãy tìm hiểu cách phương pháp được chỉ định được sử dụng trong thực tế.

Chúng tôi có một bảng với một danh sách các sản phẩm mà công ty bán, và số doanh thu tương ứng từ việc bán của họ trong một khoảng thời gian nhất định. Ở dưới cùng của bảng, kết quả của doanh thu nói chung trên tất cả các tên của hàng hóa. Có một nhiệm vụ sử dụng phân tích ABC, phá vỡ những hàng hóa này thành các nhóm theo tầm quan trọng của họ đối với doanh nghiệp.

1. Chúng tôi tô sáng một bảng với con trỏ bằng cách đóng nút chuột trái, ngoại trừ nắp và chuỗi cuối cùng. Chuyển đến tab "Dữ liệu". Chúng tôi nhấp vào nút "sắp xếp", nằm trong thanh công cụ "sắp xếp và lọc" trên băng.

   

   Bạn cũng có thể làm khác đi. Chúng tôi phân bổ phạm vi bảng trên, sau đó di chuyển đến tab "Home" và nhấp vào nút "sắp xếp và lọc" nằm trong hộp công cụ chỉnh sửa trên băng. Danh sách được kích hoạt trong đó vị trí "sắp xếp tùy chỉnh" được kích hoạt.



2. Khi áp dụng bất kỳ hành động nào ở trên, cửa sổ sắp xếp sẽ khởi chạy. Chúng tôi tìm kiếm để "dữ liệu của tôi chứa tham số tiêu đề" được đặt thành dấu kiểm. Trong trường hợp vắng mặt, cài đặt.

   Trong trường "Cột", chỉ định tên của cột trong đó dữ liệu doanh thu được chứa.

   Trong trường "sắp xếp", bạn cần chỉ định, về tiêu chí cụ thể nào sẽ được sắp xếp. Để lại cài đặt đặt trước - "Giá trị".

   Trong trường "Order", thể hiện vị trí "giảm dần".

   Sau khi sản phẩm của các cài đặt được chỉ định, nhấp vào nút "OK" ở dưới cùng của cửa sổ.



3. Sau khi thực hiện hành động được chỉ định, tất cả các mục đã được sắp xếp theo doanh thu từ nhiều hơn đến nhỏ hơn.



4. Bây giờ chúng ta nên tính toán tỷ lệ của từng yếu tố cho một kết quả chung. Tạo một cột bổ sung cho các mục đích này, mà chúng ta gọi là "chia sẻ trọng lượng". Trong ô đầu tiên của cột này, chúng tôi đặt dấu "=", sau đó bạn chỉ định một liên kết đến ô trong đó số tiền doanh thu từ việc triển khai sản phẩm có liên quan. Tiếp theo, đặt dấu hiệu của bộ phận ("/"). Sau đó, chúng tôi chỉ ra tọa độ của tế bào, trong đó chứa tổng số tiền bán hàng hóa trong toàn doanh nghiệp.

   Với thực tế là công thức được chỉ định, chúng tôi sẽ sao chép vào các ô khác của cột "Chia sẻ" bằng dấu điền, địa chỉ của liên kết đến phần tử chứa số lượng doanh thu cuối cùng cho doanh nghiệp, chúng tôi cần sửa. Để làm điều này, làm cho một liên kết tuyệt đối. Chọn tọa độ của ô được chỉ định trong công thức và nhấn phím F4. Trước khi tọa độ, như chúng ta thấy, một dấu hiệu đô la xuất hiện, chỉ ra rằng liên kết đã trở nên tuyệt đối. Cần lưu ý rằng việc tham chiếu đến số lượng doanh thu đầu tiên trong danh sách hàng hóa (sản phẩm 3) nên duy trì tương đối.

   Sau đó, để thực hiện các tính toán, nhấn nút ENTER.



5. Như chúng ta thấy, tỷ lệ doanh thu từ sản phẩm đầu tiên được chỉ định trong danh sách xuất hiện trong ô mục tiêu. Để sao chép công thức trong phạm vi bên dưới, chúng tôi đặt con trỏ đến góc dưới bên phải của ô. Sự chuyển đổi của nó trong đánh dấu điền, có một cái nhìn của một cây thánh giá nhỏ. Chúng tôi nhấp vào nút chuột trái và kéo điểm đánh dấu xuống xuống cuối cột.



6. Như bạn có thể thấy, toàn bộ cột chứa đầy dữ liệu mô tả tỷ lệ doanh thu từ việc triển khai từng sản phẩm. Nhưng giá trị của trọng lực cụ thể được hiển thị ở định dạng số và chúng ta cần chuyển đổi nó thành tỷ lệ phần trăm. Để thực hiện việc này, hãy nhấn mạnh nội dung của cột "Trọng lượng cụ thể". Sau đó, chúng tôi chuyển sang tab "Home". Trên băng trong nhóm Cài đặt nhóm, có một trường hiển thị định dạng dữ liệu. Theo mặc định, nếu bạn không tạo ra các thao tác bổ sung, định dạng "chung" phải được cài đặt ở đó. Nhấp vào biểu tượng dưới dạng một hình tam giác nằm ở bên phải của trường này. Trong danh sách các định dạng, chọn vị trí "Tỷ lệ phần trăm".



7. Như chúng ta thấy, tất cả các giá trị cột đã được chuyển đổi thành các giá trị phần trăm. Vì nó phải được, 100% được chỉ định trong chuỗi "tổng". Tỷ lệ hàng hóa được dự kiến ​​trong cột từ lớn hơn đến nhỏ hơn.



8. Bây giờ chúng ta nên tạo một cột trong đó chia sẻ tích lũy với kết quả ngày càng tăng sẽ được hiển thị. Đó là, trong mỗi dòng đến trọng lượng cụ thể riêng lẻ của một sản phẩm cụ thể sẽ được thêm tỷ lệ của tất cả các sản phẩm được đặt trong danh sách trên. Đối với hàng hóa đầu tiên trong danh sách (sản phẩm 3), tỷ lệ cá nhân và chia sẻ tích lũy sẽ bằng nhau, nhưng tất cả các chỉ báo riêng lẻ sẽ cần phải thêm phần tích lũy của phần tử trước của danh sách.

   Vì vậy, trong dòng đầu tiên, chúng tôi được chuyển đến cột "Chia sẻ" Tích lũy "chỉ báo của cột" cụ thể ".



9. Tiếp theo, đặt con trỏ đến ô thứ hai của cột "Chia sẻ" tích lũy. Ở đây chúng ta phải áp dụng công thức. Chúng tôi đặt dấu "bằng" và gấp nội dung của ô "tỷ lệ" của cùng một dòng và nội dung của ô "chia sẻ" tích lũy từ chuỗi ở trên. Tất cả các tài liệu tham khảo đều được bảo lưu, nghĩa là, chúng tôi không tạo ra bất kỳ thao tác nào với họ. Sau đó, hãy thực hiện nhấp chuột vào nút Enter để hiển thị kết quả cuối cùng.



10. Bây giờ bạn cần sao chép công thức này vào các ô của cột này, được đặt bên dưới. Để làm điều này, chúng tôi sử dụng điểm đánh dấu điền mà chúng tôi đã dùng đến khi sao chép công thức vào cột chia sẻ. Đồng thời, chuỗi "Tổng" không cần thiết, vì kết quả tích lũy là 100% sẽ được hiển thị trên sản phẩm cuối cùng từ danh sách. Như bạn có thể thấy, tất cả các yếu tố của cột của chúng tôi sau đó đã được điền.



11. Sau đó, tạo một cột "Nhóm". Chúng tôi sẽ cần nhóm hàng hóa theo loại A, B và C theo tỷ lệ tích lũy được chỉ định. Như chúng ta nhớ, tất cả các yếu tố được phân phối bởi các nhóm theo sơ đồ sau:
    • A - Lên đến 80%;
    • B - 15% sau đây;
    • C - 5% còn lại.

    Do đó, tất cả hàng hóa tích lũy cổ phần của trọng lượng cụ thể trong đó nằm trong biên giới đến 80%, chúng tôi chỉ định loại A. Hàng hóa có trọng lượng cụ thể tích lũy từ 80% đến 95% được gán cho loại B. Phần còn lại Nhóm hàng hóa có giá trị hơn 95% trọng lượng riêng được tích lũy mà chúng tôi chỉ định một loại C.



12. Đối với sự rõ ràng, bạn có thể điền vào các nhóm được chỉ định với các màu khác nhau. Nhưng nó đang theo ý muốn.

Do đó, chúng tôi đã phá vỡ các yếu tố về mức độ quan trọng, sử dụng phân tích ABC. Khi sử dụng một số kỹ thuật khác, như đã đề cập ở trên, nó được sử dụng để đột nhập vào một số lượng lớn hơn các nhóm, nhưng nguyên tắc phá vỡ trong trường hợp này vẫn gần như không thay đổi.

Bài học: Sắp xếp và lọc trong Excel

Phương pháp 2: Sử dụng một công thức phức tạp

Tất nhiên, việc sử dụng sắp xếp là cách phổ biến nhất để tiến hành phân tích ABC trong Excele. Nhưng trong một số trường hợp, cần phải thực hiện phân tích này mà không cần sắp xếp lại các dòng ở vị trí trong bảng nguồn. Trong trường hợp này, một công thức phức tạp sẽ đến giải cứu. Ví dụ: chúng tôi sẽ sử dụng cùng một bảng nguồn như trong trường hợp đầu tiên.

1. Chúng tôi thêm vào bảng nguồn chứa tên của hàng hóa và doanh thu từ việc bán từng cái, cột "nhóm". Như bạn có thể thấy, trong trường hợp này, chúng tôi không thể thêm các cột với việc tính toán các phân số riêng và tích lũy.



2. Chúng tôi sản xuất phân bổ di động đầu tiên trong cột nhóm, sau đó bạn thực hiện nhấp chuột vào nút "Chèn Chèn" nằm gần hàng Công thức.



3. Hasters kích hoạt các chức năng. Chúng tôi chuyển sang danh mục "Liên kết và mảng". Chọn chức năng "Lựa chọn". Chúng tôi nhấp vào nút "OK".



4. Cửa sổ đối số trò chơi được kích hoạt. Cú pháp được trình bày như sau:

   = Lựa chọn (Number_intex; Value1; Value2; ...)

   Nhiệm vụ của tính năng này là rút một trong các giá trị được chỉ định, tùy thuộc vào số chỉ mục. Số lượng giá trị có thể đạt tới 254, nhưng chúng ta sẽ chỉ cần ba tên tương ứng với các loại phân tích ABC: a, b, c. Chúng ta có thể nhập ngay lập tức "A" trong trường "B" trong "B "Trường, trường" value3 "-" C ".



5. Nhưng với số lượng "số chỉ mục" đối số sẽ phải được thắt chặt hoàn toàn bằng cách xây dựng một số toán tử bổ sung. Cài đặt con trỏ trong trường "Số chỉ mục". Tiếp theo, nhấp vào biểu tượng với một khung nhìn của tam giác, ở bên trái nút "Chèn Chèn chức năng". Một danh sách các nhà khai thác mới sử dụng mở ra. Chúng tôi cần một chức năng của tìm kiếm. Vì nó không có trong danh sách, sau đó chúng tôi nhấp vào dòng chữ "Các chức năng khác ...".



6. Cửa sổ của cửa sổ Wizard Hàm được bắt đầu lại. Một lần nữa, đi đến danh mục "Liên kết và mảng". Chúng tôi tìm thấy vị trí của "bảng tìm kiếm", phân bổ nó và nhấp vào nút "OK".



7. Các đối số của các đối số của nhà điều hành tìm kiếm mở ra. Cú pháp của nó có hình thức sau:

   = Bảng tìm kiếm (search_name; xem__massive; type_station)

   Mục đích của chức năng này là xác định số vị trí của mục được chỉ định. Đó là, chỉ là những gì chúng ta cần cho trường "Số chỉ mục" có sự lựa chọn.

   Trong trường "Danh sách Array", bạn có thể ngay lập tức yêu cầu biểu thức sau:

   {0: 0,8: 0,95}

   Nó phải là chính xác trong dấu ngoặc nhọn, như một công thức mảng. Không khó để đoán rằng những con số này (0; 0,8; 0,95) biểu thị các ranh giới của cổ phần tích lũy giữa các nhóm.

   Trường "Loại so sánh" không bắt buộc và trong trường hợp này, chúng tôi sẽ không điền vào nó.

   Trong trường "giá trị thứ hai", đặt con trỏ. Tiếp theo một lần nữa thông qua biểu tượng được mô tả ở trên dưới dạng một hình tam giác, chúng ta chuyển sang trình hướng dẫn của các chức năng.



8. Lần này trong Trình hướng dẫn chức năng, chúng tôi thực hiện chuyển sang danh mục "toán học". Chọn tên "Im lặng" và nhấn nút "OK".



9. Cửa sổ đối số chức năng sẽ được khởi chạy. Các toán tử được chỉ định tóm tắt các ô đáp ứng điều kiện xác định. Cú pháp của nó là:

   = Im lặng (phạm vi; tiêu chí; phạm vi_use)

   Trong trường "Phạm vi", nhập địa chỉ của cột "Doanh thu". Đối với các mục đích này, chúng tôi đặt con trỏ trong trường, sau đó bằng cách tạo clip của nút chuột trái, chọn tất cả các ô của cột tương ứng, không bao gồm giá trị "Tổng số". Như bạn có thể thấy, địa chỉ ngay lập tức xuất hiện trong trường. Ngoài ra, chúng ta cần thực hiện liên kết này tuyệt đối. Để làm điều này, tạo ra sự phân bổ của nó và nhấp vào phím F4. Địa chỉ được phát hành bởi Dấu hiệu Dollar.

   Trong trường "TIÊU CHUẨN", chúng ta cần đặt một điều kiện. Nhập biểu thức sau:

   ">"&

   Sau đó, ngay sau khi chúng tôi nhập địa chỉ của ô đầu tiên của cột "Doanh thu". Chúng tôi thực hiện tọa độ theo chiều ngang trong địa chỉ này tuyệt đối, thêm dấu hiệu Dollar từ bàn phím trước chữ cái. Tọa độ dọc Rời khỏi người thân, nghĩa là, không nên có dấu hiệu trước chữ số.

   Sau đó, chúng tôi không nhấn nút "OK" và nhấp vào tên của chức năng tìm kiếm trong chuỗi công thức.



10. Sau đó, chúng tôi quay lại cửa sổ đối số của bảng tìm kiếm. Như chúng ta thấy, trong trường "có nghĩa sương mù", dữ liệu được đặt bởi người vận hành là im lặng. Nhưng đó không phải là tất cả. Chuyển đến trường này và đã thêm vào dữ liệu khả dụng để thêm dấu "+" mà không có dấu ngoặc kép. Sau đó, chúng tôi giới thiệu địa chỉ của ô đầu tiên của cột "Doanh thu". Và một lần nữa chúng ta làm tọa độ theo chiều ngang với tuyệt đối, và theo chiều dọc để lại tương đối.

    Tiếp theo, chúng tôi lấy tất cả nội dung của trường "giá trị mong muốn" trong ngoặc, sau đó chúng tôi đặt dấu hiệu của bộ phận ("/"). Sau đó, một lần nữa thông qua biểu tượng tam giác, đi đến chức năng chọn chức năng.



11. Giống như lần cuối cùng trong Trình hướng dẫn chức năng đang chạy, chúng tôi đang tìm kiếm toán tử cần thiết trong danh mục "toán học". Lần này chức năng mong muốn được gọi là "tổng". Chúng tôi nhấn mạnh nó và nhấn nút "OK".



12. Cửa sổ đối số người vận hành mở ra. Mục đích chính của nó là tổng kết dữ liệu trong các ô. Cú pháp của nhà điều hành này khá đơn giản:

    = Tổng (số1; số2; ...)

    Đối với mục đích của chúng tôi, chỉ cần các trường "số1". Chúng tôi giới thiệu vào nó tọa độ của cột "Doanh thu", loại bỏ ô chứa kết quả. Chúng tôi đã thực hiện một thao tác như vậy trong trường "phạm vi" của hàm. Như trong thời gian đó, tọa độ phạm vi làm tuyệt đối, làm nổi bật chúng và nhấn phím F4.

    Sau đó, nhấp vào phím "OK" ở dưới cùng của cửa sổ.



13. Như bạn có thể thấy, phức tạp của các hàm đã nhập đã tính toán và phát hành kết quả trong ô đầu tiên của cột "nhóm". Sản phẩm đầu tiên được chỉ định một nhóm "A". Công thức đầy đủ được áp dụng bởi chúng tôi để tính toán này như sau:

    = Lựa chọn (bảng tìm kiếm ((im lặng ($ B $ 2: $ B $ 27; ">" & $ b2) + $ b2) / Số tiền ($ B $ 2: $ B $ 27); {0: 0 , 95}); "a"; "b"; "C")

    Nhưng, tất nhiên, trong mỗi trường hợp, tọa độ trong công thức này sẽ khác nhau. Do đó, nó không thể được coi là phổ quát. Nhưng, sử dụng hướng dẫn được đưa ra ở trên, bạn có thể chèn tọa độ của bất kỳ bảng nào và áp dụng thành công phương thức này trong mọi tình huống.



14. Tuy nhiên, đây không phải là tất cả. Chúng tôi chỉ tính cho hàng đầu tiên của bảng. Để điền đầy đủ cột "Nhóm", bạn cần sao chép công thức này trong phạm vi bên dưới (loại bỏ ô của chuỗi "tổng") bằng cách sử dụng điểm đánh dấu, như chúng ta đã thực hiện nhiều lần. Sau khi dữ liệu được thực hiện, phân tích ABC có thể được xem xét thực hiện.

Như chúng ta thấy, các kết quả thu được bằng cách sử dụng tùy chọn bằng cách sử dụng công thức phức tạp không khác với các kết quả mà chúng ta đã được thực hiện bằng cách sắp xếp. Tất cả hàng hóa được gán cùng một danh mục, chỉ cùng một lúc các hàng không thay đổi vị trí ban đầu của họ.

Bài học: Thạc sĩ các chức năng trong Excele

Chương trình Excel có thể làm giảm đáng kể phân tích ABC cho người dùng. Điều này đạt được bằng cách sử dụng một công cụ như sắp xếp. Sau đó, trọng lực riêng, tích lũy cổ phần và thực tế, phân vùng thành các nhóm được tính toán. Trong trường hợp thay đổi vị trí ban đầu của các hàng trong bảng không được phép, bạn có thể áp dụng phương thức bằng công thức phức tạp.