Я пытаюсь вычислить сумму ячеек J10:M76 если B10:E76 = текстовое значение, но если ячейки R10:R76 не равны нулю (содержат значение), я НЕ хочу включать значение в J10:M76 ,
Я знаю, что это немного сложно, и это то, что я сейчас делаю, чтобы решить эту проблему, но должен быть лучший способ.
=SUM(SUMIF($B$10:$E$76,{"City of Tampa"},$J$10:$M$76))-J13
Если вы можете использовать формулу массива, это стандартный подход.
Я предполагаю, что вы хотите получить ответ в одной ячейке, поэтому не должно быть особых причин избегать формулы массива. (Распространенной причиной использования формул с несколькими ячейками является сложность изменения формулы и связанные с этим проблемы. Блок, занятый формулой массива, может вести себя как блок ячеек, объединенных в одну.)
(Это только долго, поэтому я могу разобрать шаги достаточно, чтобы вы не сходили с ума из-за мелочей, которые я должен был показать.)
Если это так, вот концепция:
Мало того, что конечная формула будет самой формулой массива, введенной с помощью Control-Shift-Enter ("CSE"), вы также будете формировать ее части в массивы.
Один массив будет содержать значения, которые вы (возможно) хотите суммировать (в данном случае B10:M76). Второй массив будет диапазоном с желаемым текстом, а третий массив будет диапазоном, тестируемым на нулевое / пустое / пустое условие.
Цель первого массива - ввести значения, которые нужно добавить в игру. Цели второго и третьего массивов идентичны: выполнять необходимые тесты и возвращать логические значения для каждого теста. Логическое значение? Вы заставите их вернуть значение ИСТИНА или ЛОЖЬ, в зависимости от того, успешно ли выполнены тесты.
Excel будет обрабатывать эти логические значения как 1 и 0, если вы выполняете математическую операцию над ними. Таким образом, счастливый текстовый столбец может выдать {ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ЛОЖЬ; ... и т.д. операция на результат. Часто вы будете делать это с одним массивом, умножая его на 1. Здесь вам нужен чуть более сложный путь к решению, и вместо ввода «* 1» вы просто будете использовать два других массива. Третий массив аналогичен тем, что вы тестируете ячейки для состояния null/blank/empty и получаете такой же массив массивов TRUE и FALSE.
Таким образом, первый массив помещает диапазон значений суммирования в массив, а второй и третий массивы являются ИСТИНАМИ и ЛОЖЬЮ результатов двух необходимых вам тестов. Если один из тестов не пройден, вы будете умножать результат другого теста на 0, что приведет к 0, чтобы умножить значение суммирования, которое соответствует. Таким образом, если какой-либо тест не пройден, значение суммирования этой строки будет умножено на 0 и приведет к добавлению 0 в функцию SUM(), вы оберните все это. Если оба теста пройдут успешно, значение суммирования этой строки будет умножено на 1 и в конечном итоге окажется в конечном массиве, который Excel представляет функции SUM().
Таким образом, SUM() ничего не добавляет для строк, которые так или иначе терпят неудачу, поэтому в результате будут казаться только значения, тесты которых пройдены успешно.
Что ты хочешь?
В этом случае вам нужно написать первый тест, чтобы в случае успеха он вывел ИСТИНА. Что-то вроде:
(B1:B6="City of Tampa")
и второй тест должен быть написан так, чтобы он выдавал 1, если тест не пройден, так сказать, запишите его как "не совпадающий" вид, или напишите "положительным" образом, но оберните это в NOT() функция:
(C1:C6<>"")
(NOT(C1:C6=""))
(Какой бы подход ни подходил вам и который позволит вам работать над таблицей в будущем. Здесь все разные, и супервайзеры иногда выражают требования, чтобы они могли проверить это и быть уверенными, что он дает правильные ответы.)
Ваш первый массив довольно прост, просто диапазон значений, который вы можете суммировать самостоятельно:
A1:A6
(Вы должны заключить тестовые части (второй и третий массивы) в круглые скобки, но не обязательно делать это для первого массива. Но можете, если хотите.)
Затем соедините их все вместе и оберните их функцией SUM():
{=SUM( A1:A6 * (B1:B6="City of Tampa") * (C1:C6<>"") )}
(Нажатие "CSE" добавит {}, который оборачивает вышеупомянутое, конечно.)
Вы можете использовать этот подход не только с большим количеством вариаций на фронте суммирования, но и с множеством других функций. Фактически, некоторые даже не требуют, чтобы вся окончательная формула вводилась с помощью "CSE": это может быть абсолютно нормальная формула, даже если она использует массивы в своей промежуточной работе. Поэтому, если вы будете следовать вышеизложенному, вы сможете применить этот подход ко многим проблемам, с которыми вы столкнетесь.
Я точно не воссоздал вашу проблему (отсюда и мои простые диапазоны в примерах), но часто для работы с диапазоном из нескольких столбцов и строк требуется немного дополнительной работы. Многострочный ИЛИ многостолбцовый диапазон по-прежнему представляет собой массив из одной строки, как однострочная матрица. создание строк и столбцов дает Excel (так, как он есть) массивы с несколькими строками, как, скажем, матрица 3x4. Если вы получаете ошибки, вы должны "раскрутить" это и заставить Excel рассматривать рассматриваемые диапазоны как однорядные массивы. Например, используйте именованные диапазоны, чтобы объединить отдельные столбцы (или строки, если их проще писать) в один диапазон. Есть и другие хитрости. Один из них состоит в том, чтобы написать формулу для выполнения одного набора вычислений для столбца в диапазоне и добавить его ко второму набору вычислений для второго столбца и т.д. Не сложнее, всего несколько наборов одной и той же вещи, перенесенных для каждого столбца. набор столбцов. Но обычно вы можете собрать его вместе без особых проблем, просто немного смазки для локтя.
Наконец, я упомянул, что некоторые формулы используют массивы, но не обязательно, чтобы CSE вводил формулы самостоятельно. Однако, как ни странно, часто, до тех пор, пока не поймешь, почему каждый раз, когда это становится по-настоящему понятным, как у Гомера, «Дох!«В настоящий момент это может дать иной результат, чем то же самое, что и CSE. Если это так, введенная CSE версия всегда будет правильной, хотя вам необходимо проверять ее каждый раз.