Как сократить формулу в excel
Формулы – это хорошо. Они автоматически пересчитываются при любом изменении исходных данных, превращая Excel из "калькулятора-переростка" в мощную автоматизированную систему обработки поступающих данных. Они позволяют выполнять сложные вычисления с хитрой логикой и структурой. Но иногда возникают ситуации, когда лучше бы вместо формул в ячейках остались значения. Например:
- Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
- Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
- Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
- Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании "сползут" все ссылки в формулах.
В любой подобной ситуации можно легко удалить формулы, оставив в ячейках только их значения. Давайте рассмотрим несколько способов и ситуаций.
Способ 1. Классический
Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:
- Выделите диапазон с формулами, которые нужно заменить на значения.
- Скопируйте его правой кнопкой мыши – Копировать(Copy) .
- Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :
либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:
Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.
В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special - Values) в открывшемся диалоговом окне:
Способ 2. Только клавишами без мыши
При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:
- Копируем выделенный диапазон Ctrl + C
- Тут же вставляем обратно сочетанием Ctrl + V
- Жмём Ctrl , чтобы вызвать меню вариантов вставки
- Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :
Способ 3. Только мышью без клавиш или Ловкость Рук
Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:
- Выделяем диапазон с формулами на листе
- Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
- В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .
После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)
Способ 4. Кнопка для вставки значений на Панели быстрого доступа
Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл - Параметры - Панель быстрого доступа (File - Options - Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:
Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:
Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:
Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу
Если вас не пугает слово "макросы", то это будет, пожалуй, самый быстрый способ.
Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:
Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:
И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:
Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert - Module). Запускать их потом можно через вкладку Разработчик - Макросы (Developer - Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить - применяйте их с осторожностью.
Способ 6. Для ленивых
Если ломает делать все вышеперечисленное, то можно поступить еще проще - установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:
Функция LET появилась в версии Office 365 (по подписке) и будет доступна, начиная с версии Office 2021. Функция LET позволяет присваивать имена фрагментам формулы, а затем использовать эти имена в вычислениях. Формулы становятся короче и лучше читаемы. Это похоже на использование имен ячеек, диапазонов и формул в диспетчере имен. Но имена, присвоенные функцией LET, действуют только в пределах указанной формулы, и нигде больше. При использовании в русском Office имя функции сохраняется (не переводится на русский язык).
Синтаксис
Рис. 1. Синтаксис функции LET
Более строго, синтаксис функции LET:
Имя1 – обязательный аргумент, должен начинаться с буквы или нижнего подчеркивания. Значение_имени1 – обязательный аргумент, содержит значение для Имя1. имя2/значение_имени2 – необязательные аргументы; всего может быть не более 126 таких пар. Вычисление – обязательный аргумент, формула, использующая имена.
В простейшем случае:
Рис. 2. Возведение в степень с помощью функции LET
Внутри функции LET определено имя х и его значение 3, а также имя y со значением 4. С этими именами выполняется действие x y . Этот пример поясняет базовые возможности функции LET. Для подобных простых вычислений использование LET не оправдано. Поэтому обратимся к более сложным формулам, в которых использование LET будет полезным.
Функция LET устраняет дублирование фрагментов формулы
Допустим, вам нужно вывести все рабочие дни между двумя датами, хранящимися в ячейках листа. Функция LET присвоит имя исходному массиву дат, а затем использует это имя в функциях ФИЛЬТР() и ДЕНЬНЕД():
Если вы не знакомы с функциями динамических массивов, вам для начала сюда))
Рис. 3. Упрощение формулы за счет именования ее фрагмента
Первый аргумент функции LET – даты – вводит имя внутри формулы. Второй аргумент функции LET задает массив дат: ПОСЛЕД(C5-C4+1;;C4). Этот массив включает на одно значение больше, чем разность в ячейках С5 и С4, начинается с С4 с шагом 1.
Без функции LET формула могла выглядеть так:
Объявляя переменные и присваивая им значения, функция LET упрощает написание сложных формул. Внутри LET может использоваться несколько пар: имя/значение. Аргумент вычисление всегда будет последним в формуле.
Функция LET дает три существенных преимущества
Простота. Использование имен для повторяющихся фрагментов упрощает формулы и облегчает их редактирование. Если требуется, вы вносите одно изменение, а не несколько. Это быстрее и снижает вероятность ошибки.
Ясность. Использование говорящих имен облегчает понимание того, как работает формула. Человеку, написавшему формулу и использующему её, гораздо проще дешифровать внутреннюю структуру записи.
Производительность. Удаление дублирования сокращает время вычислений. Это незаметно в обычных книгах Excel, но очень важно в моделях с миллионами записей.
Использование LET во вложенных конструкциях ЕСЛИ
Допустим у вас есть результаты трех тестов по трем отдельным предметам (столбцы В, C и D). Вам нужно рассчитать среднее значение для каждого человека и выставить оценку в соответствии с критерием в правой таблице:
Рис. 4. Результаты тестов для оценки
Без функции LET вы, скорее всего, выставите оценку с помощью вложенных ЕСЛИ:
Рис. 5. Оценка с помощью функций ЕСЛИ
Проблема в том, что СРЗНАЧ(B2:D2) рассчитывается трижды. Чтобы сделать формулу более эффективной (и наглядной) мы можем присвоить среднему значению имя:
Рис. 6. Выставление оценки с использованием LET
Если вам понадобится добавить еще один экзамен, то вы поправите формулу лишь в одном месте:
Рис. 7. Добавление еще одного экзамена требует лишь одного изменения в формуле
Без LET вам пришлось бы вносить изменения в трех местах.
Использование нескольких имен в функции LET
Допустим у вас есть ФИО, и нужно извлечь имя. Следующая формула справляется с этой задачей, но понять, как она работает совсем не просто:
= ПСТР ( A2 ; ПОИСК ( " " ; A2 ) + 1 ; ПОИСК ( " " ; A2 ; ПОИСК ( " " ; A2 ) + 1 ) - ПОИСК ( " " ; A2 ) - 1 )
Рис. 8. Извлечение имени из ФИО
Чтобы работа формулы стала более понятной, можно ввести несколько имен:
- ФИО = А2
- пробел1 = ПОИСК( " " ;A2)
- пробел2 = ПОИСК( " " ;A2;ПОИСК( " " ;A2)+1)
Теперь формула стала намного понятнее:
Рис. 9. Та же формула, но с использованием имен
Функция LET и динамические массивы
В предыдущих примерах формула с LET вводилась в одну ячейку, а далее копировалась вдоль столбца. Чаще у нас на входе будет массив, и результат также будет представлять из себя массив. В Excel 365 обработать такие ситуации можно с помощью одной формулы с поддержкой динамических массивов.
Предположим, в одной таблице у вас есть график экзаменов (см. рис. 10, диапазон А2:С21), а во второй таблице вы хотите вывести только ту часть графика, которая приходится на n ближайших дней, не включая сегодняшний. Да к тому же отсортировать список по фамилиям по алфавиту. Определите имена внутри функции LET, и используйте функции динамических массивов СОРТ и ФИЛЬТР. Результатом будет массив записей в диапазоне Е6:G10.
Рис. 10. Использование LET с динамическими массивами
Поскольку даты экзаменов я задал в ячейке С2 случайной функцией СЛМАССИВ, нажимая F9 вы будете изменять даты в С2:С21. Соответственно будет меняться таблица, начинающаяся в Е6.
Извлечение предпоследнего слова из текстовой строки
Допустим у вас есть серия фраз, из которых нужно извлечь предпоследнее слово. В принципе понятно, как найти решение, но также ясно, что оно будет не простым, и основано на целом ряде шагов. Можно, например:
- сжать пробелы (вдруг между какими-то словами используется более одного пробела);
- подсчитать длину исходной фразы;
- длину фразы с удаленными пробелами;
- узнать количество слов во фразе;
- заменить пробел перед предпоследним словом на знак карет ^;
- выделить часть фразы после карет ^;
- найти позицию пробела между оставшимися двумя словами;
- выделить левое слово.
Рис. 11. Найти предпоследнее слово в пять шагов
Поскольку часто хотят получить ответ в одной ячейке, указанные выше пять формул можно объединить в одну. Для этого начните с формулы в ячейке F2:
Вместо ссылки на Е2 дважды подставьте формулу из Е2 (без знака равно). Получится:
= ЛЕВСИМВ ( ПСТР ( D2 ; НАЙТИ ( "^" ; D2 ) + 1 ; 50 ) ; НАЙТИ ( " " ; ПСТР ( D2 ; НАЙТИ ( "^" ; D2 ) + 1 ; 50 ) ) - 1 )
Теперь выделите формулу в D2 и подставьте ее содержимое четыре раза вместо ссылки на D2. Если вы дойдете до конца, то получите формулу, ссылающуюся только на А2:
Рис. 12. Объединенная формула в одной ячейке
Вот именно в таких задачах выгода от использования функции LET становится очевидной. Для начала вы дадите имя переменной для формулы из B2 (см. рис. 11), затем, используя это первое имя, поименуете формулу в ячейке С3, и т.д., двигаясь теперь слева направо. Вы можете провести подготовительную работу в таблице…
Рис. 13. Имена и значения
… а затем подставить эти имена и формулы в функцию LET:
Рис. 14. Функция LET облегчает написание и понимание формулы
Чтобы формула с LET лучше читалась, используйте Alt+Enter для перехода к новой строке в строке формул после каждой пары аргументов.
Расчет ответа в игре Быки и коровы
Кратко напомню правила. Играют двое. Каждый задумывает и записывает тайное 4-значное число с неповторяющимися цифрами (ноль может быть первым). Игрок, который начинает игру по жребию, делает попытку отгадать число. Попытка — это 4-значное число с неповторяющимися цифрами, сообщаемое противнику в виде вопроса. Противник говорит в ответ, сколько цифр угадано с совпадением их позиций в тайном числе и сколько угадано без совпадения. Например: задумано тайное число 3219; попытка (вопрос) 2310; результат (ответ): один «бык» (цифра 1 из вопроса входит в тайное число и стоит на своем месте) и две «коровы» (цифры 2 и 3 из вопроса входят в тайное число, но стоят не на своем месте). Ответ сообщается в виде 2-значного числа. В нашем примере ответ – 12 (один «бык», две «коровы»). Игроки делают попытки по очереди. Побеждает тот, кто первым получит на свой вопрос ответ 40.
Рис. 15. Формула для получения ответа в игре Быки и коровы
Тогда я использовал механизм объединения нескольких формул в ряде ячеек в одну сложную формулу. Промежуточные вычисления у меня не сохранились, так что сейчас мне было бы довольно непросто понять, как работает формула. За 10 лет изменился Excel и мои знания формул, так что сегодня эту задачу я бы решал иначе.
Итак, нам нужно сравнить два 4-значных числа, и подсчитать, сколько цифр совпадает в них с точностью до позиции и сколько просто совпадает:
Рис. 16. Формула для получения ответа в игре Быки и коровы с использованием LET
Первые четыре переменные извлекают цифры из проверяемого числа. Вторые четыре переменные – из числа-вопроса. Имя Бык возвращает количество совпадающих цифр с точностью до позиции. Кор1, … Кор4 возвращают число совпадений на разных позициях. Вычисление выводит ответ. Вуаля!
Литература
Baxter Floyd. Excel 2021. A Complete Guide About Excel 2021 With All-in-One Approach For Beginners
Bill Jelen. MrExcel 2021 – Unmasking Excel. Holy Macro! Books. 2021
Разработка оптимальной стратегии игры «Быки и коровы» на основе теории информации
(в каждой ячейке своя, индивидуальная, заботливо написанная трудолюбивыми руками)
заменил одной формулой:
которая выдает точно теже результаты
гиганской формулы в 1.5 млн. знаков не нашел (и хорошо. )
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете
В Вашем случае необходимо использовать доп столбцы или макросы
По укорачиванию формулы (в ячейке J5):
Вот этот фрагмент (буду объяснять на нём, потому что остальные аналогичны по структуре):
длинной в 115 знаков, четырежды проверяет одно и тоже условие $B1=J$1. Это надо убирать, например так:
Получается уже 51 знак, т.е. в 2,25 раз короче
Для формулы в 8 000 знаков это означает её сокращение до размера около 3 500 знаков
Пойдём далее. В Вашей формуле так же четырежды сравниваются значения диапазона B2:F2 и ячейки J2. Это тоже необходимо устранять, путём прямого однократного сравнения J2=B2:F2. В результате чего получаем вместо 115-ти знаков всего 28:
Исходная формула укорачивается в 4,1 раза (общая длинна не более 2000 знаков), причём она не будет увеличиваться при увеличении количества проверяемых столбцов в диапазоне, сейчас обозначенном как B2:F2.
И это только на первый взгляд. Наверняка можно сократить ещё. Всерьёз разбирать формулу в 8 000 знаков совершенно не хочется, да и время позднее
Пытаюсь понять то что вы мне предлагаете , но ни чего не работает .
Описание программы в ячейке J5
Спасибо огромное ! Ваша формула заработала сразу . Я просто сам запутался и перемудрил , а вы даже при этом сделали все правильно .
Мне оказывается нужно было все варианты раздельно а не вместе. А формула длинная оказывается даже мне не нужна была бы . Но все равно не маленькая где то 800 символов .
Всем большое спасибо !
Это моя была бы (т.е. я с ней работал) =ЕСЛИ(И($B1=J$1;$B2=J$2);1;0)+ЕСЛИ( И($B1=J$1;$C2=J$2);1;0)+ЕСЛИ(И($B1= J$1;$D2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$E 2=J$2);1;0)+ЕСЛИ(И($B1=J$1;$F2=J$2) ;1;0)+ЕСЛИ(И($C1=J$1;$B2=J$2);1;0)+ ЕСЛИ(И($C1=J$1;$C2=J$2);1;0)+ЕСЛИ(И ($C1=J$1;$D2=J$2);1;0)+ЕСЛИ(И($C1=J $1;$E2=J$2);1;0)+ЕСЛИ(И($C1=J$1;$F2 =J$2);1;0)+ЕСЛИ(И($D1=J$1;$B2=J$2); 1;0)+ЕСЛИ(И($D1=J$1;$C2=J$2);1;0)+Е СЛИ(И($D1=J$1;$D2=J$2);1;0)+ЕСЛИ(И( $D1=J$1;$E2=J$2);1;0)+ЕСЛИ(И($D1=J$ 1;$F2=J$2);1;0)+ЕСЛИ(И($E1=J$1;$B2= J$2);1;0)+ЕСЛИ(И($E1=J$1;$C2=J$2);1 ;0)+ЕСЛИ(И($E1=J$1;$D2=J$2);1;0)+ЕС ЛИ(И($E1=J$1;$E2=J$2);1;0)+ЕСЛИ(И($ E1=J$1;$F2=J$2);1;0)+ЕСЛИ(И($F1=J$1 ;$B2=J$2);1;0)+ЕСЛИ(И($F1=J$1;$C2=J $2);1;0)+ЕСЛИ(И($F1=J$1;$D2=J$2);1; 0)+ЕСЛИ(И($F1=J$1;$E2=J$2);1;0)+ЕСЛ И(И($F1=J$1;$F2=J$2);1;0)
А в таком виде вместо моей ваша =ЕСЛИ(И(СУММПРОИЗВ((J$1=$B1:$F1)*1) >0;СУММПРОИЗВ((J$2=$B2:$F2)*1)>0);С УММПРОИЗВ((J$2=$B2:$F2)*1)
Еще раз всем спасибо !
Произошел нюанс при написании формулы в ексел честно говоря я даже не думал что в функции если есть ограничение вложенности поэтому формулу не сокращал можете помочь сократить формулу в ексел или подсказать как можно ее продолжить.
Вот сама формула :
=ЕСЛИ(sel=A197;265864;ЕСЛИ(sel=Лист2!A198;41075;ЕСЛИ(sel=A19 9;16200;ЕСЛИ(sel=A200;54668;ЕСЛИ(sel=A201;16512;ЕСЛИ(sel=A20 2;16212;ЕСЛИ(sel=A203;20868;ЕСЛИ(sel=A204;31493;ЕСЛИ(sel=A20 5;17095;ЕСЛИ(sel=A206;38635;ЕСЛИ(sel=A207;54567;ЕСЛИ(sel=A20 8;16456;ЕСЛИ(sel=A209;15442;ЕСЛИ(sel=E198;66142;ЕСЛИ(sel=E20 0;53998;ЕСЛИ(sel=E201;105461;ЕСЛИ(sel=E202;294303;ЕСЛИ(sel=E 203;;ЕСЛИ(sel=E204;55585;ЕСЛИ(sel=E205;302675;ЕСЛИ(sel=E206; 295915;ЕСЛИ(sel=E207;55506;ЕСЛИ(sel=E208;130227;ЕСЛИ(sel=E20 9;239514;ЕСЛИ(sel=E210;103943;ЕСЛИ(sel=E211;77525;ЕСЛИ(sel=E 212;642602;ЕСЛИ(sel=H196;475573;ЕСЛИ(sel=H200;53558;ЕСЛИ(sel =H207;77096;ЕСЛИ(sel=H211;63249;ЕСЛИ(sel=H215;89537;ЕСЛИ(sel =H219;40672;ЕСЛИ(sel=H224;33666;ЕСЛИ(sel=H229;51095;ЕСЛИ(sel =H231;56355;ЕСЛИ(sel=H233;;ЕСЛИ(sel=A401;342435;ЕСЛИ(sel=A40 2;69008;ЕСЛИ(sel=A403;14030;ЕСЛИ(sel=A404;27787;ЕСЛИ(sel=A40 5;17197;ЕСЛИ(sel=A406;19459;ЕСЛИ(sel=A407;21291;ЕСЛИ(sel=A40 8;13972;ЕСЛИ(sel=A409;11974;ЕСЛИ(sel=A410;29222;ЕСЛИ(sel=A41 1;12890;ЕСЛИ(sel=A412;21349;ЕСЛИ(sel=A413;146441;ЕСЛИ(sel=E4 05;73634;ЕСЛИ(sel=E406;74411;ЕСЛИ(sel=E407;52160;ЕСЛИ(sel=E4 08;36365;ЕСЛИ(sel=E409;38990;ЕСЛИ(sel=E410;77042;ЕСЛИ(sel=E4 11;75727;ЕСЛИ(sel=H401;42045;ЕСЛИ(sel=H402;18315;ЕСЛИ(sel=H4 03;21920;ЕСЛИ(sel=H404;44486;ЕСЛИ(sel=H405;27043;ЕСЛИ(sel=H4 06;23948;ЕСЛИ(sel=H407;44460;ЕСЛИ(sel=H408;20484)))))))))) ))))))))))))))))))))))))))))))))))))))))))))))))))))))
Сокращение формулы
Здравствуйте. Возможно сократить формулу?
Сокращение формулы ЕСЛИ
Здравствуйте. Цель данной формулы в том что бы выводить сумму значений из ячеек J22:J31 при.
сокращение формулы сложения
Можно ли упростить уравнение sin A * sin B - cos A * cos B? (как, например, cos A * cos B - sin A *.
Сокращение булевой функции. Делема о применимости формулы
Могу ли я воспользоваться свойством: -(A v B) = -A-B Для упрощения функции: -(A v B v C) И.
ВикторzzZ, будет проще организовать исходные таблицы по аналогии с аксессом (уникалный ключ + данные по нему+связь с другими таблицами, при необходимости) и так несколько таблиц. А в сводную "тянуть" из исходных таблиц по ключу, выбираемому юзером.
А то, как у тебя сделано - ОПАСНО.
При изменении структуры хоть одной исходной таблицы,
будет как в песне поётся: "за***ся тексты переделывать".
Как сокращать числа в Excel?
Если у вас есть список больших чисел, набранных в Excel, и теперь вы хотите сократить их, как показано на скриншотах ниже, вы можете прочитать это руководство для получения подробной информации.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Сокращенный номер
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Вы можете настроить форматирование чисел в диалоговом окне «Формат ячеек».
1. Выберите числа, которые нужно сократить, и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек из контекстного меню.
3. Нажмите OK чтобы закрыть диалог, теперь большие числа сокращены.
Читайте также: