Jak używać funkcji MINIFS w Excelu

Polskie Angielski
MIN MINIFS

In this article, we will explore the function MINIFS in Microsoft Excel and its counterpart in Google Sheets, MIN.JEŻELI. These functions are used to find the minimum value in a dataset based on one or more conditions.

Syntax and Examples

The syntax for MINIFS in Microsoft Excel is as follows:

=MINIFS(range, criteria_range1, criteria1, [criteria_range2, criteria2, ...])

Example of usage:

A B
15 Books
20 Electronics
5 Books
=MINIFS(A1:A3, B1:B3, "Books")

The result is 5, as the lowest number among entries categorized as „Books”.

The syntax for MIN.JEŻELI in Google Sheets is similar:

=MIN.JEŻELI(criteria_range, criterion, range)

Using this formula in Google Sheets for the same data table:

=MIN.JEŻELI(B1:B3, "Books", A1:A3)

Here, the result is also 5, calculated in a similar manner.

Practical Applications

  • Task 1: A company is looking for the lowest price of parts that need to be purchased. They have a list of parts IDs with corresponding prices.
  • Task 2: A teacher wants to find the lowest grade of students who passed a test, i.e., any grade above 1.

Task 1 – Minimum Price for a Specific Part

Consider a two-column example where the first represents part IDs (1, 2, 3…), and the second their prices:

Part ID Price
1 150
2 100
1 80
3 120

The formula to find the lowest price for part ID 1:

=MINIFS(B2:B5, A2:A5, 1)

The result is 80, the lowest price for part ID 1.

Task 2 – Lowest Passing Grade

Assume we have a table of student grades:

Student Grade
Anna 2
Bob 5
Cece 1
David 3

To find the lowest grade above 1:

=MINIFS(B2:B5, B2:B5, ">1")

The result is 2, which is the lowest grade higher than 1.

In summary, the functions MINIFS and MIN.JEŻELI are extremely useful in data analysis when it is necessary to identify minimal values that meet specified criteria.

Więcej informacji: https://support.microsoft.com/pl-pl/office/min-warunków-funkcja-6ca1ddaa-079b-4e74-80cc-72eef32e6599

Inne funkcje
Oblicza odchylenie standardowe na podstawie całej populacji
Oblicza odchylenie standardowe na podstawie całej populacji, z uwzględnieniem liczb, teksów i wartości logicznych
Szacuje odchylenie standardowe na podstawie próbki
Szacuje odchylenie standardowe na podstawie próbki, z uwzględnieniem liczb, tekstów i wartości logicznych
Zwraca błąd standardowy przewidzianej wartości y dla każdej wartości x w regresji
Zwraca funkcję skumulowanego rozkładu beta