Как вычислить, сколько бактерий в каждой группе устойчивы к антибиотику

Я пытаюсь подсчитать случаи устойчивости к 1 или 1 + антибиотикам при определенных условиях. Вот пример того, как выглядит моя электронная таблица:

Введите описание изображения здесь

Для каждого препарата «1 «означает устойчивость, а» 0 » — чувствительность.

Если бы я хотел определить количество бактерий в группе А, которые устойчивы только к одному антибиотику, как бы я это сделал? Или если бы я хотел найти, сколько бактерий в группе А устойчивы к 1 или более антибиотикам?

Я боролся с этим некоторое время, поэтому, если кто-то может указать мне в правильном направлении, я был бы очень признателен.

В идеале мой выход будет выглядеть так

Введите описание изображения здесь

3 ответа

  1. Создайте новый столбец для «счетчика сопротивления» и используйте =COUNTIF(B2:D2,">=1")для ячейки E2 и заполните вниз. Затем можно отфильтровать таблицу по типу или количеству сопротивлений. Используется SUBTOTALдля подсчета отфильтрованных строк.

  2. Вот что я бы сделал:

    1) Создайте дополнительный столбец (с именем «Count») справа от вашей таблицы с количеством 1s в каждой строке бактерий. Формула: =COUNTIF(C2:E5, "1")

    2) Создайте другой столбец (с именем «Resistance»)справа от вашей таблицы с вложенным оператором IF. Формула: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Создайте сводную таблицу с этими данными. Поместите «Resistance «в поле Columns,» Type «в поле Rows и» Sum of Count » в поле Values.

    Это должно дать вам именно то, что вы хотите.

  3. Это Формулы массива. Вы должны использовать Ctrl-Shift-Enter для ввода этих формул, и Excel волшебным образом вставит фигурные скобки (вы не можете вставить их самостоятельно).

    Для Ровно 1 сопротивления, войдите в I2, поскольку у меня есть настройка в pic ниже :

    =SUM(($B:$B=$H2)*IF(MMULT($C:$E, TRANSPOSE(COLUMN($C

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    :$E

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    )^0))=1, 1, 0)) для typeA.

    Перетащите формулу вниз, чтобы получить тип B, как в my Setup.

    =SUM(($B:$B=$H3)*IF(MMULT($C:$E, TRANSPOSE(COLUMN($C

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    :$E

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    )^0))=1, 1, 0))

    Для сопротивления больше чем 1, в J2, используйте:

    =SUM(($B:$B=$H2)*IF(MMULT($C:$E, TRANSPOSE(COLUMN($C

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    :$E

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    )^0))>1, 1, 0)*(MMULT(IF($C:$E=9, 0, 1), TRANSPOSE(COLUMN($C

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    :$E

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    )^0))= COLUMNS($C

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    :$E

    These are array formulas. You MUST use Ctrl-Shift-Enter to enter these formulas, and Excel will magically insert curly braces (you cannot insert them yourself).

    For exactly 1 resistance, enter into I2 as I have the setup in the pic below :

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0)) for typeA.

    Drag the formula down to get type B as in my set up.

    =SUM(($B$2:$B$11=$H3)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))=1, 1, 0))

    For more than 1 resistance, in J2, use:

    =SUM(($B$2:$B$11=$H2)*IF(MMULT($C$2:$E$11, TRANSPOSE(COLUMN($C$1:$E$1)^0))>1, 1, 0)*(MMULT(IF($C$2:$E$11=9, 0, 1), TRANSPOSE(COLUMN($C$1:$E$1)^0))= COLUMNS($C$1:$E$1)))

    Again, drag the formula down to get typeB...

    enter image description here

    Note that there are only ever three cell ranges/areas which you need to input into the formulas (albeit in several spots).

    1. The data range for the type column

    2. The data area for the resistance test results

    3. The column headers for the drugs(should be the same width as the test results area).

    If you set up named ranges for these areas, then you could put the named range into the formulas, and never have to amend the formula range arguments when the data ranges change size. But that's another point...

    Edit for explanation:
    If you evaluate formula parts, you might see something like this(for the image I provided)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    True or false evaluates as 1 or 0 when multiplied.

    The effect of the '^0' is to turn each non-zero number in the resultant arrays into a '1'. This allows matrix multiplication to take place on the datarange to spit out column vectors.

    TRANSPOSE({3,4,5}^0) becomes: TRANSPOSE({1,1,1}), which then becomes this: {1;1;1}. Notice the difference between commas and semicolons - this means a 1x3 vector is transposed into a 3x1 vector. Then you can use that as the second parameter into matrix multiplication with a 10x3 matrix(thats the dataset in our case).

    The output of that MMULT is a 10x1 column vector representing some output.

    The first MMULT is used to test if the row Total of any bacteria is > 1. The second MMULT is used to see if all the row entries for any bacteria are <> 9.

    A similar thing is done for the case for Exactly One. The MMULT function is used once to determine if the row total for a bacterium is exactly 1.

    Create a new column for "Resistance Count" and use =COUNTIF(B2:D2,">=1") for cell E2 and fill down. Then you can filter the table by Type or Resistance Count. Use SUBTOTAL to count the filtered rows.

    Here's what I would do:

    1) Create an additional column (named "Count") to the right of your table with the count of 1s in each bacteria row. The formula is: =COUNTIF(C2:E5, "1")

    2) Create another column (named "Resistance")to the right of your table with a nested IF statement. The formula is: =IF(F2=1,"Resistant to 1",IF(F2=0,"Resistant to 0","Resistant to More than 1"))

    3) Create a Pivot Table with this Data. Put "Resistance" in the Columns Field, "Type" in the Rows Field, and "Sum of Count" in the Values field.

    This should give you exactly what you want.

    )))

    Снова перетащите формулу вниз, чтобы получить typeB…

    Введите описание изображения здесь

    Обратите внимание, что есть только три диапазона/области ячеек, которые необходимо ввести в формулы (хотя и в нескольких местах).

    1. Диапазон данных для столбца типа

    2. Область данных для результатов испытаний на сопротивление

    3. Заголовки столбцов для препаратов (должны быть той же ширины, что и область результатов теста).

    Если вы настроили именованные диапазоны для этих областей, то можно поместить именованный диапазон в формулы и никогда не изменять аргументы диапазона формул при изменении размера диапазонов данных. Но это другой момент…

    Отредактируйте для объяснения:
    Если вы оцениваете части формулы, вы можете увидеть что-то вроде этого(для изображения, которое я предоставил)
    =SUM(({TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE})*IF(MMULT({1,0,0;1,9,1;0,1,1;9,1,0;1,0,1;1,1,0;9,1,1;0,1,9;0,1,1;1,1,0},{1;1;1})>1, 1, 0)*(MMULT({1,1,1;1,0,1;1,1,1;0,1,1;1,1,1;1,1,1;0,1,1;1,1,0;1,1,1;1,1,1},{1;1;1})= 3))

    Значение True или false при умножении равно 1 или 0.

    Эффект «^0 «состоит в том, чтобы превратить каждое ненулевое число в результирующих массивах в «1». Это позволяет умножению матрицы происходить на datarange, чтобы выплюнуть векторы столбцов.

    TRANSPOSE({3,4,5}^0) становится:TRANSPOSE({1,1,1}), который затем становится этим: {1;1;1}. Обратите внимание на разницу между запятыми и точками с запятой — это означает, что вектор 1×3 транспонируется в вектор 3×1. Затем вы можете использовать это в качестве второго параметра в умножение матрицы с матрицей 10×3 (это набор данных в нашем случае).

    Выход этого MMULT представляет собой вектор столбца 10×1, представляющий некоторый выход.

    Первый MMULT используется для проверки, если общее число строк каких-либо бактерий > 1. Второй MMULT используется, чтобы увидеть, если все записи строки для каких-либо бактерий <> 9.

    Аналогичная вещь делается для случая ровно для одного. Функция MMULT используется один раз, чтобы определить, является ли сумма строк для бактерии точно 1.