Используйте Ctrl + Shift + Enter (формулы CSE), чтобы дополнить ваши формулы в Excel! Да, это правда - в Excel есть секретный класс формул. Если вы знаете три волшебных ключа, вы можете получить одну формулу массива Excel, чтобы заменить тысячи других формул.
95% пользователей Excel не знают о формулах CSE. Когда большинство людей слышат свое настоящее имя, они думают, что «это не кажется ни малейшей пользы», и никогда не удосуживаются узнать о них. Если вы думаете, что SumIf и CountIf - это круто, вы скоро обнаружите, что формулы Ctrl + Shift + Enter (CSE) будут запускать круги вокруг SumIf и CountIf. Формулы CSE позволяют буквально заменять тысячи ячеек формул одной формулой. Да, мои товарищи по Excel Guru, прямо у нас под носом есть что-то настолько мощное, и мы никогда им не пользуемся.
До появления SumIf вы могли использовать формулу CSE для того же, что и SumIf. Microsoft предоставила нам SumIf и CountIf, но формулы CSE не устарели. О нет, они могут намного больше! Что насчет того, если вы хотите использовать AverageIf? Как насчет GrowthIf? AveDevIf? Даже MultiplyByPiAndTakeTheSquareRootIf. Практически все, что вы можете себе представить, можно сделать с помощью одной из этих формул CSE.
Вот почему я думаю, что формулы CSE никогда не прижились. Во-первых, их настоящее имя всех отпугивает. Во-вторых, для того, чтобы заставить их работать, им требуется чуждое, нелогичное обращение. После того, как вы введете формулу CSE, вы не сможете просто нажать Enter. Вы не можете просто выйти из ячейки, щелкнув клавишу со стрелкой. Даже если вы правильно составили формулу и нажмете клавишу ввода, Excel выдаст вам совершенно неудобное для пользователя «ЗНАЧЕНИЕ!». ошибка. Он не говорит: «Вау, это прекрасно. Вы прошли 99,1% пути», что, вероятно, и было.
Вот секрет: после того, как вы введете формулу CSE, вы должны удерживать клавиши Ctrl и Shift, а затем нажать Enter. Возьмите стикер и запишите его: Ctrl Shift Enter. Пользователи Power Excel будут иметь возможность использовать эти формулы примерно раз в месяц. Если вы прямо сейчас не напишите Ctrl Shift Enter, вы забудете об этом к тому времени, когда что-то появится снова.
Изучите этот пример: предположим, вы хотите усреднить только значения в столбце C, где столбец A находился в восточном регионе.
Формула в ячейке A13 - это пример формулы CSE.
=AVERAGE(IF(A2:A10="East",C2:C10))
Подключите это, и вы получите 7452,667, среднее значение только для Востока. Здорово? Вы только что создали свою собственную версию несуществующей функции Excel AverageIf. Помните: нажмите Ctrl + Shift + Enter, чтобы ввести формулу.
Посмотрите следующий пример ниже.
В этом примере мы делаем формулу CSE более общей. Вместо того, чтобы указывать, что мы ищем "Восток", укажите, что вам нужно любое значение в A13. Формула сейчас =AVERAGE(IF($A$2:$A$10=A13,$C$2:$C$10)).
Как ни странно, Excel позволяет копировать и вставлять формулы CSE без каких-либо специальных нажатий клавиш. Я скопировал C13 в C14: C15, и теперь у меня есть средние значения по всем регионам.
Наша мэйнфрейм-система хранит количество и цену за единицу, но не расширенную цену. Конечно, достаточно просто добавить столбец C и заполнить его, =A2*B2а затем получить итоговый столбец C, но это необязательно!
Здесь наша формула CSE =SUM(A2:A10*B2:B10). Он берет каждую ячейку в A2: A10, умножает на соответствующую ячейку в B2: B10 и суммирует результат. Введите формулу, удерживая Ctrl и Shift, нажимая клавишу ВВОД, вы получите ответ одной формулой вместо 10.
Вы видите, насколько это мощно? Даже если бы у меня было 10 000 строк данных, Excel возьмет эту единственную формулу, произведет 10 000 умножений и выдаст мне результат.
Хорошо, вот правила: вы должны нажимать Ctrl + Shift + Enter каждый раз, когда вводите или редактируете эти формулы. В противном случае будет получено совершенно неоднозначное значение #VALUE! ошибка. Если у вас несколько диапазонов, все они должны иметь одинаковую общую форму. Если у вас есть диапазон, смешанный с отдельными ячейками, отдельные ячейки будут «реплицированы» в памяти, чтобы соответствовать форме вашего диапазона.
После успешного ввода одного из них и просмотра его в строке формул у вас должны появиться фигурные скобки вокруг формулы. Вы никогда сами не войдете в фигурные скобки. Нажатие Ctrl + Shift + Enter помещает их туда.
Эти формулы сложно освоить. от одной мысли о них заболела голова. Если мне сложно войти, я перехожу в Инструменты> Мастера> Мастер условной суммы и просматриваю диалоговые окна. Результатом работы мастера условной суммы является формула CSE, поэтому она обычно может дать мне достаточно подсказок о том, как ввести то, что мне действительно нужно.
Тебе приходилось брать BASIC в 11 классе с мистером Ирвином? Или, что еще хуже, вы получили двойку по линейной алгебре с миссис Герцогиня в колледже? Если это так, то вы в хорошей компании и будете вздрагивать, когда услышите слово «массив». - Я с криком бегу в другую сторону, когда кто-нибудь говорит «массив». Я их понимаю, но это Excel, мне здесь массивы не нужны !. Злой секрет в том, что Excel и Microsoft называют эти формулы «формулами массива». Стой - не убегай. Это нормально. переименовал их в формулы CSE, потому что это звучит менее пугающе, и потому что имя помогает вам запомнить, как их вводить - Ctrl Shift Enter. Я упоминаю здесь настоящее имя только на тот случай, если вы столкнетесь с кем-то из Microsoft, у которого никогда не было миссис Дюшес по линейной алгебре, или в случае, если вы решите просмотреть файлы справки. Если ты пойдешь на помощь,ищите под злым псевдонимом "формула массива", но между нами, друзья, назовем их CSE - ОК?
Прочтите статью Использование специальной формулы массива Excel для моделирования СУММЕСЛИ с двумя условиями.
