Excel слишком сложная формула
С помощью Excel можно выполнять почти какого-угодно порядка: вычислять и даже программировать действия компьютера. Все потому, что в функционал заложены функции и формулы. Но иногда пользователю приходится сталкиваться с ошибками при их вводе. Особенно часто они возникают, когда человек пытается ввести формулу вручную. Если пользователь опытный, такая проблема встречается не очень часто, поскольку он уже знает, какой синтаксис правильный, а также довольно внимательный. Но даже профессионалы могут допускать ошибки.
Часто мы не понимаем, почему они возникают. И профессионалы тоже могут не знать, какие причины. Он может понимать, что означает тот или иной код ошибки, но найти конкретное место в формуле не может. Но для профессионала это не проблема, поскольку он знает, как находить ошибки методами самого Excel. Сегодня мы более детально разберемся в причинах самых распространенных ошибок при вводе формул в Excel и разберем методы решения этой проблемы. Тем самым мы значительно ближе будем к профессионалам. Ну что же, начнем?
Изменение формата ячеек Excel
Довольно нередкая причина того, почему у человека появляется неудача в ходе записи формул, заключается в том, что он неверно выбрал формат ячеек, на какие она ссылается. Так, если в ней поставили формат «текст», то вместо итогового значения перед нами будет просто показана формула в виде текстовой строки.
Есть случаи, когда формат считается правильно, но отображается неверно. Причина этого явления — также ошибочно выбранный формат. Эта проблема может появиться и сама собой, через глюки в работе программы. Как ни странно, человеческий фактор влияет и на работу компьютера, который не является человеком. Но это уже лирическое отступление.
Конечно, в таком случае следует выставить правильный формат. Для этого нужно следовать таким шагам:
- Выделить ячейку и перейти на вкладку «Главная» на ленте. После этого перемещаем курсор мыши в группу «число». Здесь также есть поле, в котором демонстрируется формат, в данный момент присвоенные ячейке.
- Нужно нажать на стрелку вниз возле этого поля. После этого появится список с разными форматами ячеек. Нужно выбрать тот, который соответствует тому, который нужно применять в конкретной формуле. После этого делается клик по выбранному варианту.
Есть еще один инструмент, настройки которого более широки. Чтобы его применить, необходимо выполнять следующую инструкцию:
- Определить, нам нужно изменить формат одной ячейки или нескольких. После этого выбираем ту, которая нам подходит (или целый диапазон выделяем), после чего делаем правый клик по ней (ним). Далее ищем в появившемся перечне пункт «Формат ячеек». Задача может быть существенно упрощена, если пользователь нажмет сочетание Ctrl+1.
- После этого появляется диалоговое окно, в котором по умолчанию будет открыта вкладка «Число». Важно убедиться, что открыта именно она. После этого следует обратить ваш взор на список в левой части окна. Там находится перечень доступных форматов ячеек. Нужно выбрать тот, который подходит в конкретной ситуации и нажать ОК.
Для сохранения полученных изменений необходимо воспользоваться функцией редактирования для каждой ячейки с ошибкой при вводе формулы. После того, как мы найдем тот элемент, который нужен, следует отредактировать с помощью клавиши F2 или выполнения двойного клика по соответствующей ячейке. Также внесение изменений возможно с помощью левого щелчка мыши в строке формул. После внесения всех изменений в документ нужно подтвердить свои действия путем нажатия клавиши Enter.
Внимание. При наличии большого количества информации, последний шаг может потребовать очень много времени. В этом случае можно упростить задачу, применив маркер автозаполнения. Правда, этим методом можно воспользоваться только в одном случае. Главное — требование — в каждой ячейке должна применяться та же формула.
Что следует делать на практике?
- Выделяем ячейку, которая расположена в самом верху диапазона.
- Наводим курсор на ее правую нижнюю часть (то есть, на угол), убеждаемся в том, что появился знак плюса черного цвета, нажимаем левую кнопку, зажимаем ее и перемещаем курсор до того места, где должен закончиться этот диапазон.
После выполнения этих операций перед нашим взором стоит готовый столбец, который был сгенерирован почти без нашего участия.
Отключение режима “Показать формулы” в Excel
Случаются ситуации, когда пользователь не обнаруживает результатов, хотя он их ждал после выполнения операций функцией. Вместо них отображаются только формулы. Причина этого может крыться в том, что включена демонстрация формул, и естественно, нам нужно ее отключить, чтобы увидеть итог вычислений. Выполняем следующие действия:
- Находим в главном меню вкладку «Формулы». Немного ниже и правее нее находится группа инструментов «Зависимость формул», в которой находится кнопка «Показать формулы». Нужно сделать один левый клик мышью при условии, что эта функция активирована.
- После этого в каждой ячейке теперь можно увидеть итоги вычислений. Естественно, это может повлиять на границы колонок, но в этом нет никакой супер страшной проблемы. Этот вопрос решается буквально в несколько кликов.
Активизация автоматического пересчета формул в Excel
- Переходим в меню «Файл».
- После этого ищем раздел «Параметры» и переходим в него.
- Далее мы увидим окно. В списке справа находится большое количество различных разделов, но нас интересует «Формулы». После клика мы увидим, что правая часть окна изменилась. Переходим в группу «Параметры вычислений», где ставим флажок возле функции «автоматически». После того, как действия будут выполнены, нам нужно нажать кнопку ОК.
Теперь можно отдыхать, поскольку проблема решена. Все вычисления осуществляются автоматически. И если какое-то из значений вдруг изменяется, программа автоматически его пересчитывает.
Исправление ошибок в формулах
Бывают ситуации, когда человек допускает ошибку при вводе формулы. В этом случае программа будет просто ее определять, как простое значение в текстовом формате. То есть, и расчетов также не будет. Так, самой распространенной ошибкой является ситуация, когда перед вводом знака «равно» ставится пробел. Здесь важно помнить, что перед знаком = не должно быть никаких пробелов, и он всегда должен располагаться перед формулой.
Кроме этого, нередко ошибки появляются в синтаксисе функций. Причина этого проста — не все они просты для заполнения обычными людьми. Особенно это касается ситуаций, когда применяется сразу несколько аргументов. Поэтому ручной ввод формул рекомендуется лишь если пользователь опытный, и в таком случае синтаксис нужно тщательно перепроверять. Для того, чтобы сделать ввод функций более простым, был создан Мастер функций, который вызывается путем нажатия клавиши fx слева от строки ввода формулы.
Для обеспечения работоспособности формулы необходимо тщательно проверять ее перед вводом. В том числе, необходимо убедиться в том, что в ней нигде не стоят знаки, которые не видимы невооруженным глазом. В нашем случае таким знаком служил пробел.
Время от времени лучше удалить формулу, где стоят некоторые непечатные символы или ее синтаксис написан уж откровенно неправильно, чем искать ошибку и ее исправлять. Это же касается не только названия формулы, но и ее функций. О том, что при вводе формулы была допущена ошибка, Excel сообщит определенным кодом. Каждый из кодов имеет конкретное значение. Давайте перечислим наиболее распространенные из них.
Бывают и ситуации потруднее, когда точно сказать, где кроется ошибка, нельзя. Если приходится иметь дело со сложными функциями, то в таком случае нужно использовать специальные инструменты проверки. Следуйте этой инструкции:
- Выделяем ту ячейку, в которой есть ошибочная формула. Далее переходим во вкладку «Формулы». Там нужно найти инструмент «Зависимости формул» — «Вычислить формулу».
- Перед глазами пользователя появится диалоговое окно. В нем детально будет описано, как осуществлять отчет правильно. Чтобы решить проблему, необходимо нажать кнопку «Вычислить». После этого функция будет последовательно проверяться на каждом шагу. Поэтому кнопку нужно нажать несколько раз. Таким образом можно отследить, на каком шагу возникла проблема и оперативно ее решить.
Есть еще один способ определения причин неполадок при работе с формулами. Называется этот инструмент «Проверка ошибок». Его можно найти в том же блоке, что и предыдущий.
После того, как мы нажмем на соответствующую кнопку, появится окно. В нем будет информация о том, что вызвало ошибку, а также будут приведены рекомендации по исправлению сложившейся ситуации.
Исправление ошибки в синтаксисе
Надо сказать, что все особенности исправления ошибок в синтаксисе были описаны выше, потому что это частный случай ошибок в формулах. Тем не менее, давайте резюмируем то, что было сказано, поскольку ошибка в синтаксисе — очень частая проблема начинающих пользователей Excel. Для проверки синтаксиса существует два специализированных инструмента: проверка формул и вычисление. Также можно воспользоваться ручным методом проверки. Когда какой лучше использовать?
- В целом, если формула небольшая, ее можно проверить и самостоятельно. Это не потребует большого количества времени. Также можно без проблем проверять формулы самостоятельно, если их количество небольшое. Дело в том, что при увеличении количества формул увеличивается и количество ошибок при проверке. Мозг загромождается большим количеством информации, пусть и простой для обработки.
- Если есть большое количество формул или сами формулы довольно большие, лучше воспользоваться специализированным инструментом. Каким именно — решать только вам. Каждый из них подходит, просто один предусматривает выполнение действий более в ручном режиме, а другой — автоматическом.
Выводы
С ошибками приходится сталкиваться каждому человеку, который пользуется электронными таблицами. Это неотъемлемая часть рабочего процесса. Поэтому умение обрабатывать ошибки — важнейший навык. Выше были перечислены самые распространенные ошибки, с которыми могут сталкиваться пользователи при вводе формул. Кроме того, были описаны методы решения возникающих проблем и приведены ситуации, в которых необходимо использовать каждый из них.
Работая с большой книгой в Excel в один совсем не прекрасный момент вы делаете что-то совершенно безобидное (добавление строки или вставку большого фрагмента ячеек, например) и вдруг получаете окно с ошибкой "Слишком много различных форматов ячеек":
Почему это происходит
Такая ошибка возникает, если в рабочей книге превышается предельно допустимое количество форматов, которое Excel может сохранять:
- для Excel 2003 и старше - это 4000 форматов
- для Excel 2007 и новее - это 64000 форматов
Причем под форматом в данном случае понимается любая уникальная комбинация параметров форматирования:
- шрифт
- заливки
- обрамление ячеек
- числовой формат
- условное форматирование
Так, например, если вы оформили небольшой фрагмент листа подобным образом:
. то Excel запомнит в книге 9 разных форматов ячеек, а не 2, как кажется на первый взгляд, т.к. толстая линия по периметру создаст, фактически 8 различных вариантов форматирования. Добавьте к этому дизайнерские танцы со шрифтами и заливками и тяга к красоте в большом отчете приведет к появлению сотен и тысяч подобных комбинаций, которые Excel будет вынужден запоминать. Размер файла от этого, само собой, тоже не уменьшается.
Подобная проблема также часто возникает при многократном копировании фрагментов из других файлов в вашу рабочую книгу (например при сборке листов макросом или вручную). Если не используется специальная вставка только значений, то в книгу вставляются и форматы копируемых диапазонов, что очень быстро приводит к превышению лимита.
Как с этим бороться
Направлений тут несколько:
- Если у вас файл старого формата (xls), то пересохраните его в новом (xlsx или xlsm). Это сразу поднимет планку с 4000 до 64000 различных форматов.
- Удалите избыточное форматирование ячеек и лишние "красивости" с помощью команды Главная - Очистить - Очистить форматы (Home - Clear - Clear Formatting) . Проверьте, нет ли на листах строк или столбцов отформатированных целиком (т.е. до конца листа). Не забудьте про возможные скрытые строки и столбцы.
- Проверьте книгу на наличие скрытых и суперскрытых листов - иногда на них и кроются "шедевры".
- Удалите ненужное условное форматирование на вкладке Главная - Условное форматирование - Управление правилами - Показать правила форматирования для всего листа (Home - Conditional Formatting - Show rules for this worksheet) .
- Проверьте, не накопилось ли у вас избыточное количество ненужных стилей после копирования данных из других книг. Если на вкладке Главная (Home) в списке Стили (Styles) огромное количество "мусора":
Запустить его можно с помощью сочетания клавиш Alt+F8 или кнопкой Макросы (Macros) на вкладке Разработчик (Developer) . Макрос удалит все неиспользуемые стили, оставив только стандартный набор:
Все мы при работе с excel рано или поздно сталкиваемся с формулами. В начале нашего пути мы начинаем использовать простейшие формулы с одной функцией (например, СУММ, ОКРУГЛВВЕРХ, МАКС и многие другие).
Однако по мере увеличения мастерства и усложнения проводимых расчетов, нам приходится создавать сложные формулы, в которых используется 2,3,4, а то и больше функций. В таких формулах достаточно сложно разобраться, особенно если видишь их в первый раз. Также в них легко ошибиться.
Я бы хотела привести несколько способов, которые позволят упростить работу с громоздкими формулами:
1. Чтобы не ошибиться при вводе формулы с большим количеством операторов, помните порядок их вычисления . В excel существует порядка 9 операторов, которые мы используем в формулах. Все они имеют свой приоритет при расчете формулы:
- ^ (возведение в степень) - первый приоритет;
- * (умножение) и / (деление) - второй приоритет;
- + (сложение) и - (вычитание) - третий приоритет;
- & (конкатенация) - четвертый приоритет;
- = (равно), < (меньше) и >(больше) - пятый приоритет.
При этом расчеты, заключенные в скобки, выполняются раньше, чем все остальные. Если используются вложенные скобки (скобки в скобках), то сначала вычисляется значение, находящееся во внутренних скобках, затем во внешних.
2. Если вы не уверены, что сможете прописать корректно аргументы функции, пользуйтесь диалоговым окном "Вставка функции" , которое находится на ленте во вкладке Формулы (слева). При открытии этого окна открывается поисковик функций, в котором можно найти необходимую (в примере ниже я искала ВПР). После того, как вы нашли нужную функцию открывается окно "Аргументы функции", в котром содержатся подсказки:
3. Для того, чтобы разобраться в сложной формуле используйте прием "вычисления фрагмента формулы" . Для этого в сложной формуле выделите ее фрагмент и нажмите клавишу F9 и тогда этот фрагмент формулы рассчитается и примет свое итоговое значение прямо в формуле. После этого обязательно нажмите Ctrl+Z, для того, чтобы отменить этот расчет.
Приведу пример на формуле из другой своей статьи. У нас есть таблица с объемом продаж менеджеров, в которой с помощью функций ИНДЕКС и ПОИСКПОЗ мы ищем объем продаж определенного менеджера. На картинке я выделила фрагмент формулы с использованием функции ПОИСКПОЗ (она ищет положение строки c продавцом Авоян):
Те, кто работает с большими данными в excel, порой сталкиваются с тем, что файл начинает подвисать, расчет формул происходит не мгновенно, а в течение нескольких секунд, а то и доходит до десятков секунд.
Все это свидетельствует о том, что ваш файл стал слишком "тяжелым". Как следствие, могут возникнуть и проблемы с отправкой такого файла по почте из-за ограничений размера отправляемого файла.
Есть несколько основных причин, почему ваш файл стал медленно работать и потяжелел:
1. Использование старых форматов книг excel . В последних версиях программы (начиная с 2007) появились новые форматы для файлов excel, которые значительно легче, чем предыдущие (xlsx, xlsm, xlsb) т.к. используют новый подход к сжатию файлов.
При возможности, когда вы сохраняете файл - используйте эти форматы.
2. Форматирование сильно утяжеляет файл . Если в своем excel вы используете условное форматирование, особенно цветное - оно влияет на скорость работы файла и его вес.
По возможности старайтесь минимизировать количество такого форматирования в excel.
3. Диапазон используемых данных при пересчете значений . Если вы, например, ранее использовали диапазон ячеек 100*100, потом удалили значения в этих ячейках и стали использовать диапазон 5*5, то скорее всего excel при работе с вашими данными и при их сохранении в памяти держит бОльший диапазон, что сильно влияет на его вес.
Для того, чтобы точно от этого избавиться, выделите все строки под вашими рабочими данными (например, с помощью сочетания клавиш Ctrl+Shift+стрелка вниз) и удалите эти строки (на ленте во вкладке Главная -> Удалить -> Удалить строки с листа). Тоже самое проделайте и со столбцами.
4. Сводные таблицы . Количество сводных таблица напрямую влияет на вес и быстродействие файла. Чем их больше - тем сложнее с ним работать.
Power Pivot может сильно помочь в этом случае, но придется потратить время на его освоение.
5. Количество графических элементов, диаграмм и примечаний также может повлиять на вес файла. Я видела файлы, в которых десятки, а то и несколько сотен диаграмм. Работать с таким файлом очень сложно.
Если наличие этих элементов не принципиально - избавьтесь от них.
Еще один простой совет - не превращайте excel в инструмент создания дэшбордов и сложных визуализаций. Попробуйте для этого использовать Power Point, Power BI или любую другую программу для визуализации данных.
6. Макросы и еще раз макросы способны из вашего excel сделать очень медленноработающий файл. Особенно громоздкие. При отправке отчета вы можете пересохранить файл в формате, не поддерживающем макросы, и тогда он станет значительно легче.
7. Большое количество расчетных формул в совокупности с "простыней" исходных данных также могут вам помешать. При отправке файла можно сохранить формулы как значения и убрать исходные данные, если это допустимо.
С учетом того, что если соответствовать всем 7 рекомендациям, можно потерять очень много в части визуализации в excel и удобстве работы с отчетом, я предлагаю использовать каждый из пунктов по отдельности и по очереди. Возможно, на каком-то из этапов вам удастся сократить вес файла без потери диаграмм, форматирования и прочих удобных штук.
✔ Ищите ответы на свои вопросы в статье Фишки excel - подборка статей . Там я перечислила все свои статьи по разбору работы функций excel.
✔ Вот здесь есть подборка видео о том, как строить необычные диаграммы - Фишки Excel - подборка видеороликов .
В этом уроке мы познакомимся с понятием Сложная формула в Excel, а также разберем порядок выполнения действий при решении таких формул. Представленная информация является базовой и предназначена в первую очередь для начинающих пользователей Microsoft Excel.
Простая формула – это математическое выражение с одним оператором, такое как 7+9. Сложная формула содержит более одного оператора, к примеру, 5+2*8. Если формула содержит несколько математических операторов, Excel руководствуется порядком действий при выполнении вычислений. Используя Excel для вычисления сложных формул, необходимо знать порядок выполнения действий.
Порядок действий в формулах Excel
Excel выполняет действия, опираясь на следующий порядок:
- Выражения, помещенные в скобки.
- Возведение в степень (например, 3^2).
- Умножение и деление, что идет раньше.
- Сложение и вычитание, что идет раньше.
Пример решения сложной формулы
В качестве примера, попробуем вычислить значение формулы, представленной на следующем рисунке. На первый взгляд это выражение выглядит довольно сложным, но мы можем воспользоваться порядком выполнения операций поэтапно, чтобы найти правильный ответ.
Операции, заключенные в скобки
В первую очередь, мы начнем с выполнения действия в скобках. В данном случае нам необходимо вычислить: 6-3=3.
Возведение в степень
Можно заметить, что сложная формула теперь выглядит немного проще. Далее мы проверим наличие степеней в нашем выражении. Да, она встречается один раз: 2^2=4.
Деление
Далее мы выполним все операции умножения и деления, в порядке следования слева направо. Поскольку деление встречается раньше умножения, то деление выполняется первым: 3/4=0,75.
Умножение
Теперь мы выполним оставшуюся операцию умножения: 0,75*4=3.
Сложение
Далее мы выполним все операции сложения и вычитания, в порядке следования слева направо. Поскольку сложение встречается раньше вычитания, то сложение выполняется первым: 10+3=13.
Вычитание
В заключение остается последнее действие – вычитание: 13-1=12.
В итоге мы получили ответ: 12.
Точно такой же результат вы получите, если введете эту формулу в Excel.
Как видите, в этом нет ничего сложного!
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Читайте также: