В excel не работает впр: почему не срабатывает функция (формула) – WindowsTips.Ru. Новости и советы

Содержание

Функция ВПР Excel не работает

от Олеся Полякова

Как работает ВПР в Excel читайте в посте “Функция ВПР – чудо экономии времени”. Здесь же вы узнаете, что делать, если ВПР не работает. Так бывает. Скорее всего вы допустили ошибку. Есть четыре наиболее распространенных варианта ошибок:

  1. Ошибка Н/Д в каждой ячейке.
  2. Ошибка Н/Д преимущественно в нижней части таблицы.
  3. Ошибка Н/Д в отдельных ячейках.
  4. Ошибка #ССЫЛКА.

А теперь разберем каждый вариант подробнее.

Вариант 1. ВПР вернула ошибку #Н/Д во всех ячейках

Причины:

  • Вы неправильно выделили диапазон поиска – начали не с того столбца:

Все будет в порядке, как только вы исправите ошибку:

=впр(B3;'[прайс-лист. xlsx]Лист1′!$B$3:$E$7;3;ложь) на

=впр(B3;'[прайс-лист.xlsx]Лист1′!$C$3:$E$7;3;ложь)

 

  • Не совпадает текст, по которому ВПР ищет данные.

Например, в одном файле текст “Компьютер AF”, в другом файле “Компьютер—AF”. Если не заметили, разница всего лишь в наличии дефиса во втором случае. Тем не менее, ВПР не считает такое расхождение мелочью и, следовательно, не сработает.

Единственное исключение – допускаются разные регистры. Например, “Компьютер AF” и “компьютер af” не составит проблемы для ВПР.

Идентичность текста удобно проверять функцией =ЯЧЕЙКА=ЯЧЕЙКА. Смотрите пример на рисунке ниже и описание под ним:

  1. Введите в ячейку D2 знак “=”
  2. Выделите ячейку B2 с первым значением Планшет DC.
  3. Опять введите “=”.
  4. Выделите ячейку C2 со вторым значением.
  5. Кликните Enter и протяните формулу вниз.

В результате в ячейках отобразится либо “ИСТИНА” (значения совпадают), либо “ЛОЖЬ” (значения не совпадают).

Как видите, не совпадают значения в строке 4: “Компьютер А” и “Компьютер F” (в ячейке D4 видим слово “ЛОЖЬ”).

Использовать функцию можно как в одном, так и в разных файлах.

Что можно предпринять, если текст не совпадает?

  1. Если вы регулярно работаете с этими файлами, поменяйте текст вручную. Единожды потратив на это время, вы значительно сэкономите его в будущем. Если смена текста невозможна, создайте дополнительный столбец, куда введете текст из второго файла.
  2. Иногда текст не совпадает из-за каких-то наборов знаков, которые повторяются в каждой ячейке. К примеру, в одном из файлов везде добавлен артикул: “010-01583 Компьютер AF”, а в другом файле этого артикула нет “Компьютер AF”. В этом случае текст можно заменить автоматически. Об этом читайте в следующих постах.

 

  • В одном из файлов в тексте содержатся невидимые пробелы. Это хитрая разновидность несовпадения текста. Хитрая, потому что не всегда ее можно заметить. Вот полюбуйтесь:

Как быть? Воспользуйтесь формулой =СЖПРОБЕЛЫ(ЯЧЕЙКА). Эта формула убирает все лишние пробелы. Подробнее – в посте о работе с текстом.

 

  • В одном файле числа сохранены в виде текста, в другом – в виде чисел:

Для устранения проблемы сделайте следующее:

  1. Выделите весь столбец с числами в виде текста (на рисунке выше диапазон B2:В5).
  2. Подведите курсор к значку “!” и кликните по появившейся стрелочке.
  3. Выберите из выпадающего списка “Преобразовать в число” – см. рисунок ниже.

Вариант 2. ВПР подтянула значения только в верхней части таблицы, в нижней — #Н/Д

Причина:

  • Смещение диапазона. Чаще всего это происходит, если данные подтягиваются из одного и того же файла. Например, из Листа 1 на Лист 2 или даже из одного и того же листа. Что происходит? Мы тянем формулу вниз, а вместе с ней тянется и диапазон. Вот так:

Как исправить? Надо закрепить диапазон:

  1. Кликните дважды по ячейке с формулой (С5) – отобразится формула.
  2. В отобразившейся формуле поместите курсор на В12 и нажмите клавишу F4 на клавиатуре.
  3. Переместите курсор на С14 в этой же формуле и опять нажмите F4.
  4. Кликните Enter и формула преобразится, из а) в б)

а) =ВПР(В5;В12:С14;2;ложь)

б) =ВПР(В5;$B$12:$С$14;2;ложь)

Значок “$” указывает на то, что диапазон закреплен. Теперь при протягивании формулы вниз, он не будет смещаться.

Вариант 3. ВПР подтягивает только часть значений, остальные  – #Н/Д

Причина:

  • В некоторых ячейках текст не совпадает на 100%.

Если таких ячеек немного, то проще всего исправить это вручную, копируя из одного файл в другой.

Вариант 4. ВПР возвращает ошибку #ССЫЛКА

Причина:

  • Вы неверно указали номер столбца. ВПР всегда считает столбцы, начиная с первого столбца выделенного диапазона. Поэтому на рисунке ниже красные номера неверные, зеленые – правильные:

И еще несколько советов по функции ВПР в Excel:

  1. При вводе формулы используете точку с запятой, а не запятые.
  2. Если протягиваете формулу вправо, не забывайте закреплять ссылку в начале формулы, чтобы формула имела вид: =ВПР($В5;$B$12:$С$14;2;ложь). Для этого наведите курсор на В5 (прямо в формуле) и трижды нажмите клавишу F4 на клавиатуре.
  3. Не используйте аргумент “ИСТИНА” в ВПР, если работаете с текстом. Это приблизительный поиск, в котором будет много ошибок.

13 Excel-граблей, на которые может наступить контекстник — Сервисы на vc.ru

Сколько бы сервисов в стеке специалиста по рекламе не появилось, а работа с Excel никуда не денется. И к сожалению, будут случаться неловкие ситуации, когда непонятно, что делать или как это вышло. В статье разберем основные моменты из опыта агентства Mello. Если знаете что добавить, пишите в комментариях, будем дополнять.

531
просмотров

1. #ИМЯ? При работе с ключевыми словами

Некоторые ключевые слова, собранные через wordstat, могут содержать плюсы перед стоп-словами. Если стоп-слово идет первым в фразе, то в самом начале окажется плюс. При вставке таких запросов в Excel может возникнуть ошибка #ИМЯ ?.

По умолчанию Excel считает строки с плюсом в начале формулами и пытается их посчитать, поэтому возникает эта ошибка.

Решение

Можно сделать массовую замену, заменив “=+” на “‘+” (равно с плюсом на одиночный апостроф с плюсом).

Также можно предварительно изменить формат ячеек на текстовый и после этого вставлять запросы.

2. Безжалостная протяжка

В Excel есть удобное свойство, благодаря которому можно указать лишь несколько цифр из диапазона в разных ячейках, а потом автоматически заполнить пустые ячейки с нужным шагом. Например, мы можем написать несколько четных чисел, выделить диапазон, зажать квадратик справа внизу и, протянув вниз, в итоге получить диапазон из четных чисел нужной длины.

Это очень удобная вещь, но при невнимательном использовании может наставлять палок в колеса. Например, вы пишете акционные тексты и в конце указываете дату окончания акции. При протяжке может возникнуть следующая ситуация:

И если с текстами вы еще можете заметить ошибку, то с какими-нибудь ссылками или метками эта история может оказаться скрыта.

Решение

Не протягивайте, копируйте.

3. Числа не работают

Иногда числа при вставке могут восприниматься как текст (из-за пробела между разрядами) и никакие арифметические формулы не будут работать.

Решение

Сделайте массовую замену, заменив пробел (либо символ между разрядами) на пустоту.

4. Буквы сломались

Открываете файл, а там абракадабра типа этой:

Вероятно, проблема с кодировкой файла. Но нормальные буквы чаще всего возможно увидеть.

Решение

Попробуйте открыть файл блокнотом или другим редактором, а потом скопируйте содержимое в эксель.

Символы могут распознаться, если повезет.

5. Всё в одном столбце

При открытии csv и подобных файлов с разделителями вы можете получить все данные в одном столбце:

Конечно с этим неудобно работать.

Решение

Воспользуйтесь функцией «Текст по столбцам». Выделите диапазон, в меню данных выберете эту функцию.

Укажите формат данных с разделителями, затем определитесь с разделителем (чаще всего запятая) и нажмите «Готово». Данные должны разбежаться по разным столбцам.

6. Ячейки рвутся

У функции «Текст по столбцам» могут быть последствия. После применения действий, описанных выше, к любой вставке в книгу извне будет применяться «Текст по столбцам».

Например:

Решение

Надо снова зайти в меню «Текст по столбцам» и снять галку с разделителя, который был выбран ранее, после этого при вставке проблем не будет.

7. Формулы не работают, да много чего еще тоже не работает

Иногда вы начинаете пользоваться какими-то формулами, и начинают вылетать ошибки вычислений, нередко дело может быть в том, что у вас разные/ не подходящие форматы ячеек. Также может быть ситуация, когда вы пишите корректную формулу, но в ячейке ничего не считается, а так и продолжает отображаться формула.

Решение

Приведите ячейки к корректным форматам, а чтобы где-то формула перестала восприниматься текстом, выберите формат общий и еще раз отредактируйте формулу, просто войдя в режим редактирования, править ничего не надо.

8. Копируешь что-то, а ничего не вставляется, либо ошибка

Иногда вы пользуетесь формулами в Excel и получаете значения, например строка итогов в какой-нибудь таблице.

Потом вы копируете эти данные в новое место, но вставляются уже другие значения:

Это происходит и-за того, что копируются не значения, а формулы, и при вставке в новое место они начинают считаться для новых диапазонов.

Решение

Выполняйте не просто вставку, а вставку «как значение»:

9. Фильтры и вставки

Не пытайтесь что-то вставлять рядом с активным фильтром, что-то точно вставится некорректно. Пользуйтесь новым листом или делайте вставку в те строки, где действие фильтра вас не достанет.

10. Не работает ВПР

В Excel есть очень удобная функция для поиска соответствий ВПР. Иногда она может не найти соответствия:

Решение

Первым делом проверьте формулу, всё ли в ней корректно. Если всё верно, но соответствие не найдено, значит искомое и входящее значения действительно разные. Возможно в ячейке есть лишние пробелы или другие незаметные символы, поэтому формула не отрабатывает.

Для проверки можете сделать простое сравнение ячеек, в которых вы уверены. Ну а дальше с помощью массовой замены удалите лишние символы, или с помощью формул избавьтесь от лишних пробелов.

Нередко такая ситуация бывает, когда в списке фраз с минус-словами убирают минус-слова с помощью функции «Текст по столбцам», а потом пытаются сопоставить фразы с другими. Так вот, после разбивки по столбцам у исходной фразы, в конце остается лишний незаметный пробел.

11. Числа превращаются в даты

Работая с числами, определитесь, какой у вас разделитель для дробной части — точка или запятая. Если в вашей системе вы работаете с запятой и откроете файл, где разделитель точка, такие числа могут превратиться в даты.

Решение

Попробуйте вставить данные как текст, потом с помощью массовой замены в числах замените точку на запятую, потом поменяйте формат ячеек на общий или числовой.

Другой же вариант, при открытии файла, если появится меню открытия (Мастер текстов), выберите подходящий тип разделителя на 3 шаге:

Если при первом открытии файла подобное меню не появилось, то закройте файл, и снова откройте его через Excel.

12. Ячейки прыгают

Бывает так: работаешь, работаешь, кликаешь на ячейку для редактирования, но твой клик попадает не на ячейку, а на полоску между ячейками. Вот на неё:

И тебя отбрасывает в самую нижнюю заполненную ячейку. Ну и наоборот, кликнув по верхней границе дважды, тебя отбросит в самый верх.

Решение

Кликайте на сами ячейки, а не между ними.

13. Условное форматирование работает некорректно

Настроив условное форматирование для ячейки вы замечаете, что оно срабатывает не тогда, когда нужно. Вероятно, к ячейкам применено слишком много правил.

Решение

Проверяйте сколько правил уже применено в меню Условное форматирование -> Управление правилами.

Если вы будете добавлять новое, хоть и такое же точно правило, то оно не будет перезаписывать предыдущее, а будет добавляться к текущим.

Скорее всего, это еще не все неудобства Excel, о которых следует знать. Однако, надеюсь, изложенная выше информация будет полезна и убережет некоторое количество нервных клеток специалистам по контекстной рекламе.

Удачи!

Автор: Андрей Педченко, специалист отдела контекстной рекламы в Mello

Excel не отвечает, зависает, зависает или перестает работать

В этой статье обсуждаются шаги по устранению неполадок, которые могут помочь решить наиболее распространенные проблемы, когда вы получаете сообщение об ошибке Excel не отвечает, Excel зависает или зависает при запуске или открытии книги Excel. Проблемы такого типа могут возникать по одной или нескольким причинам, перечисленным ниже.

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

Примечание. Если у вас возникли проблемы с открытием файлов Excel после обновления с Windows 7 до Windows 10, см. раздел Ошибки при открытии файлов Office после обновления с Windows 7 до Windows 10.

Устранение неполадок в безопасном режиме

Когда вы запускаете Excel в безопасном режиме, он обходит функции и настройки, такие как альтернативное место запуска, измененные панели инструментов, папки запуска, используемые Excel, надстройки Excel и большинство надстроек COM. Хотя надстройки могут улучшить ваш опыт, они могут иногда мешать или конфликтовать с Excel.

Вы можете устранить неполадки в безопасном режиме одним из двух способов:

Автоматизировать устранение неполадок в безопасном режиме

Помощник по поддержке и восстановлению Майкрософт полностью автоматизирует процесс изоляции безопасного режима, чтобы вы знали, связана ли проблема с надстройками, файлами в папках автозапуска или настройками панели инструментов.

Важно:

  • Помощник в большинстве случаев изменяет реестр и перемещает файлы из папок автозагрузки, используемых Excel. Перед запуском помощника убедитесь, что вы создали резервную копию реестра и всех файлов в папках автозагрузки, используемых Excel, чтобы можно было восстановить реестр и резервные копии файлов в случае возникновения проблемы.

  • Дополнительные сведения о резервном копировании и восстановлении реестра см. в разделе Резервное копирование и восстановление реестра в Windows.

  • Дополнительные сведения о папках запуска, используемых Excel, см. в разделе Настройка запуска Excel.

Скачайте и запустите помощник

Если вы используете Помощник и он не решает вашу проблему, выполните действия, описанные в разделе Установка последних обновлений этой статьи.

Сценарий средства устранения сбоев Excel автоматизирует устранение неполадок в безопасном режиме, который включает следующее:

  • Запуск Excel в безопасном режиме

  • Отключение надстроек COM

  • Отключение надстроек Excel

  • Перемещение файлов из папок автозагрузки, используемых Excel

  • org/ListItem»>

    Переименование файлов пользовательских панелей инструментов (.xlb)

В зависимости от того, что обнаружил сценарий на вашем устройстве, некоторые из этих шагов по устранению неполадок могут потребовать от вас ручного сброса конфигурации надстройки и перемещения файлов обратно в папки автозагрузки. При необходимости используйте следующую информацию, чтобы определить изменения, которые могли быть внесены сценарием.


Надстройки COM и надстройки Excel

Если сценарий отключает надстройки COM или надстройки Excel:

Примечание. Вы можете не найти \Addins или \AddinsRenamedBySaRA в своем реестре в указанных выше разделах. Это всего лишь полный список того, что можно найти в реестре.

  • Любое значение REG_SZ, которое является либо «OPEN», либо начинается с «OPEN» (например, OPEN1), имеет перед именем «RenamedBySaRA». Например, «OPEN» переименовывается в «RenamedBySaRAOPEN»

    HKEY_CURRENT_USER\ПРОГРАММНОЕ ОБЕСПЕЧЕНИЕ\Microsoft\Office\16.0\Excel\Параметры

  • На рабочем столе Windows создается папка с именем ExcelAddinRegistryDataBySaRA .

  • В папке ExcelAddinRegistryDataBySaRA создается файл с именем AddinRegistryDataExportedBySaRA.reg .

    Файл AddinRegistryDataExportedBySaRA.reg содержит резервную копию указанных выше разделов реестра в том виде, в каком они существовали до запуска сценария.

Файлы в папках автозагрузки

Если сценарий перемещает файлы из папок автозагрузки, используемых Excel:

Примечание:  У вас может не быть вышеуказанных папок. Это всего лишь исчерпывающий список того, что может быть найдено сценарием.

  • Файлы найдены в папке, указанной следующими данными реестра:

    • Ключ: HKEY_CURRENT_USER\Software\Microsoft\Office\1x.0\Excel\Options

      Примечание. В этом подразделе 1x.0 представляет номер версии программы. 16.0 появится для версий Excel 2016 и выше, а 15.0 появится для Excel 2013.

    • Имя: AltStartup

    • org/ListItem»>

      Тип: REG_EXPAND_SZ

    • Данные: путь к папке

      Примечание.  У вас могут отсутствовать указанные выше данные реестра. Это всего лишь исчерпывающий список того, что может быть найдено сценарием.

  • Файлы, перемещенные из указанных выше папок, перемещаются в папку с именем ExcelStartupFilesFoundBySaRA , созданную на рабочем столе Windows.

  • В папке ExcelStartupFilesCopiedBySaRA создается файл с именем ExcelStartupFolderFiles. log.
    Файл ExcelStartupFolderFiles.log содержит список всех перемещенных файлов и их исходное расположение.

Файлы пользовательских панелей инструментов (.xlb)

Если сценарий находит файл пользовательской панели инструментов:

Устранение неполадок в безопасном режиме вручную

Если вы не использовали Помощник, вручную откройте Excel в безопасном режиме. Выполните одно из следующих действий:

  • Если вы используете Windows 11 или Windows 10, выберите Пуск > Все приложения > Система Windows > Выполнить . Введите Excel /safe в поле Выполнить и нажмите OK .

  • org/ListItem»>

    Если вы используете Windows 8 или Windows 8.1, нажмите Выполнить в меню Приложения , введите Excel /safe в поле Выполнить и нажмите OK .

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

Если Excel не запускается в безопасном режиме, перейдите к разделу Установка последних обновлений этой статьи.

Надстройки COM

Выполните следующие действия, чтобы определить, является ли надстройка COM причиной проблемы:

  1. Щелкните Файл > Параметры > Надстройки .

  2. Выберите Надстройки COM, и щелкните Перейти .

  3. Снимите все флажки в списке и нажмите 9.0007 ОК .

  4. Закройте и перезапустите Excel в обычном режиме (не в безопасном).

Если проблема не возникает при перезапуске Excel в обычном режиме, начинайте включать надстройки по одной, пока это не произойдет. Это позволит вам выяснить, какая надстройка вызывает проблему. Обязательно перезапускайте Excel каждый раз, когда включаете надстройку.

Если отключение надстроек COM не решило вашу проблему, проверьте, не связана ли проблема с надстройкой  Excel .

Надстройки Excel

Важно:

  • Внимательно следуйте инструкциям в этом разделе. При неправильном изменении реестра могут возникнуть серьезные проблемы. Обязательно сделайте резервную копию реестра перед его изменением. Убедитесь, что вы знаете, как восстановить реестр в случае возникновения проблемы.

  • Дополнительные сведения о резервном копировании и восстановлении реестра см. в разделе Резервное копирование и восстановление реестра в Windows.

  1. Запустить редактор реестра. Для этого нажмите клавишу Windows + R, чтобы открыть диалоговое окно «Выполнить». Введите regedit.exe и нажмите OK .

  2. Найдите и выберите следующий раздел реестра:
    HKEY_CURRENT_USER\Software\Microsoft\Office\<1x.0>\Excel\Options

    Примечание. В этом подразделе <1x.0> представляет номер версии программы. 16.0 будет отображаться для версий Excel 2016 и более поздних версий, а 15.0 — для Excel 2013.

  3. В столбце Name найдите любое значение REG_SZ, равное OPEN.
    Если вы не найдете значение REG_SZ с именем OPEN, перейдите к разделу Исследуйте возможные проблемы с файлами в папках автозагрузки.

  4. Щелкните правой кнопкой мыши OPEN и выберите Переименовать .

  5. Переименуйте OPEN в RenamedOPEN и нажмите Enter.

  6. Повторите шаги 4 и 5, добавив «Переименовано» в начало любого значения REG_SZ, которое начинается с OPEN. Например, переименуйте OPEN1 в RenamedOPEN1.

  7. Запустите Excel в обычном режиме.

  8. Если проблема не возникает при перезапуске Excel в обычном режиме, начните повторно включать надстройки Excel одну за другой, удалив «Переименовано» из имени. Вы сможете определить надстройку, которая вызывает проблему. Не забывайте перезапускать Excel каждый раз, когда вы повторно включаете надстройку в реестре.

Если отключение надстроек COM и надстроек Excel не решило проблему, перейдите к разделу Исследование возможных проблем с файлами в папках автозагрузки.

Исследуйте возможные проблемы с файлами в папках автозагрузки

При запуске Excel открывает все рабочие книги, найденные в папке XLStart, или альтернативную папку запуска в дополнение к папке XLStart. Попробуйте запустить Excel, не открывая книги в папке XLStart или альтернативной папке автозагрузки.

рабочих книг в папке XLStart

Любая книга, шаблон или файл рабочей области, которые вы помещаете в папку XLStart, автоматически открывается при запуске Excel. Вы можете переместить файлы из этой папки и открыть Excel, чтобы проверить и определить, вызывает ли проблема конкретную книгу. Чтобы найти путь к папке XLStart и переместить из нее книги, выполните следующие действия:

  1. Щелкните Файл > Параметры .

  2. Щелкните Центр управления безопасностью , а затем в разделе Центр управления безопасностью Microsoft Office Excel щелкните Параметры центра управления безопасностью .

  3. Щелкните Trusted Locations , а затем запишите путь к папке XLStart в списке надежных расположений.

  4. org/ListItem»>

    Переместите все файлы, найденные в этой папке, в другую папку, а затем запустите Excel в обычном режиме.

Если проблема не возникает при перезапуске Excel в обычном режиме, начните перемещать книги, перемещенные на шаге 4, одну за другой обратно в папку XLStart, каждый раз перезапуская Excel в обычном режиме. Если есть проблема с одной из книг в папке XLStart, это должно идентифицировать книгу.

Если перемещение книг из папки XLStart не решило проблему, проверьте книги в другой папке автозагрузки.

Книги в альтернативной папке запуска

Любая книга, шаблон или файл рабочей области, помещенные в альтернативную папку автозагрузки, автоматически открываются при запуске Excel. Поскольку Excel попытается открыть каждый файл в альтернативной папке автозагрузки, убедитесь, что в этой папке находятся только те файлы, которые может открыть Excel.

Попробуйте запустить Excel, не открывая книги в этой папке:

  1. Щелкните Файл > Параметры > Дополнительно .

  2. В разделе General в поле At Startup откройте все файлы в , обратите внимание на путь к папке, настроенной как альтернативная папка запуска.

  3. Переместите все файлы, найденные в этой папке, в другую папку, а затем запустите Excel в обычном режиме.

  4. org/ListItem»>

    Если проблема не возникает, начните перемещать все книги, перемещенные на шаге 3, обратно в альтернативную папку автозагрузки одну за другой, каждый раз перезапуская Excel в обычном режиме. Вы сможете определить книгу, которая вызывает проблему.

Если перемещение файлов из папки XLStart и альтернативной папки автозагрузки не решило проблему, перейдите к следующему разделу.

Установить последние обновления

Возможно, вам потребуется настроить Центр обновления Windows для автоматической загрузки и установки рекомендуемых обновлений. Установка любой важной рекомендации и оптимальных обновлений часто может исправить проблемы, заменив устаревшие файлы и исправив уязвимости. Чтобы установить последние обновления Office, выполните действия, описанные в этой статье: Обновите Office и свой компьютер.

Если установка последних обновлений Office не решила проблему, перейдите к следующему пункту в этом списке.

Убедитесь, что Excel не используется другим процессом

Если Excel используется другим процессом, эта информация будет отображаться в строке состояния в нижней части окна Excel. Если вы попытаетесь выполнить другие действия во время использования Excel, Excel может не ответить. Дайте выполняемой задаче завершить свою работу, прежде чем предпринимать другие действия.

Если Excel не используется другим процессом, перейдите к следующему элементу в списке.

Исследовать детали и содержимое файла Excel

Файлы Excel могут существовать на компьютере длительное время. Они обновляются от версии к версии и часто перемещаются от одного пользователя к другому. Часто пользователь наследует файл Excel, но не знает, что в него включено. Следующие факторы могут вызвать проблемы с производительностью или сбоем:

  • org/ListItem»>

    Формулы, ссылающиеся на целые столбцы.

  • Формулы массива, ссылающиеся на нечетное количество элементов в аргументах.

  • Сотни или, возможно, тысячи скрытых объектов или объектов с нулевой высотой и шириной.

  • Чрезмерное количество стилей, вызванное частым копированием и вставкой между книгами.

  • Избыточные и недопустимые определенные имена.

Если эти действия не помогли решить вашу проблему, перейдите к следующему пункту в списке.

Проверьте, не создается ли ваш файл третьей стороной

Иногда файлы Excel создаются сторонними приложениями. В этом случае файлы могут быть сгенерированы неправильно, а некоторые функции могут работать некорректно при открытии файлов в Excel. В этом случае проверьте функции в новых файлах вне стороннего приложения. Если функции работают правильно, вы должны убедиться, что третья сторона знает о проблеме.

Если проблема не устранена после тестирования вне стороннего приложения, перейдите к следующему пункту в списке.

Выполните выборочный запуск, чтобы определить, конфликтует ли программа, процесс или служба с Excel

При запуске Windows некоторые приложения и службы запускаются автоматически, а затем работают в фоновом режиме. Эти приложения и службы могут мешать работе другого программного обеспечения на вашем ПК. Выполнение выборочного запуска (также известного как «чистая загрузка») может помочь вам выявить проблемы с конфликтующими приложениями. Чтобы выполнить выборочный запуск, выберите одну из следующих ссылок в зависимости от вашей версии Windows, затем выполните действия, описанные в статье:

Windows 10, Windows 7, Windows 8: запуск выборочного запуска с использованием конфигурации системы
Windows Vista: запустите выборочный запуск, используя конфигурацию системы
Windows XP: как создавать и настраивать учетные записи пользователей в Windows XP

Выборочный запуск используется для выявления вызывающего нарушение процесса, службы или приложения, которое конфликтует с Excel.

Если проблема не решена после повторного создания профиля, перейдите к следующему пункту в списке.

Восстановление программ Office

Исправление программ Office может решить проблемы, связанные с тем, что Excel не отвечает, зависает или зависает, путем автоматического исправления ошибок в файлах Office. Инструкции по этому поводу см. в разделе Восстановление приложения Office.

Если восстановление программ Office не решило проблему, перейдите к следующему пункту в списке.

Проверьте, обновлено ли ваше антивирусное программное обеспечение и не конфликтует ли оно с Excel 9.0011

Если ваше антивирусное программное обеспечение не обновлено, Excel может работать неправильно.

Проверьте, обновлено ли ваше антивирусное программное обеспечение:

Чтобы не отставать от создаваемых новых вирусов, поставщики антивирусного программного обеспечения периодически предоставляют обновления, которые можно загрузить из Интернета. Загрузите последние обновления, посетив веб-сайт поставщика антивирусного программного обеспечения.

Список поставщиков антивирусных программ см. в разделе Поставщики потребительского программного обеспечения для обеспечения безопасности.

Проверьте, конфликтует ли ваше антивирусное программное обеспечение с Excel:

Если ваше антивирусное программное обеспечение включает интеграцию с Excel, у вас могут возникнуть проблемы с производительностью. В этом случае вы можете отключить всю интеграцию Excel с антивирусным программным обеспечением. Или вы можете отключить любые надстройки антивирусного программного обеспечения, установленные в Excel.

Важно:  Изменение настроек антивируса может сделать ваш компьютер уязвимым для вирусных, мошеннических или вредоносных атак. Корпорация Майкрософт не рекомендует вам пытаться изменить настройки антивируса. Используйте этот обходной путь на свой страх и риск.

Возможно, вам придется обратиться к поставщику антивирусного программного обеспечения, чтобы определить, как настроить ваше программное обеспечение, чтобы исключить любую интеграцию с Excel или исключить сканирование в Excel.

Дополнительная информация

Расширенный поиск и устранение неисправностей

Если методы, упомянутые ранее, не помогли решить вашу проблему, проблема может быть связана либо с файлом, либо с окружающей средой. В следующем разделе описывается, как устранить дополнительную проблему, которая может привести к зависанию или сбою Excel.

Факторы окружающей среды

Факторы окружающей среды так же важны, как содержимое файлов и надстройки, при устранении сбоев. Вы можете помочь определить причину проблемы, выполнив следующие действия:

В следующих разделах описаны некоторые области, которые стоит изучить.

Где хранится файл

Локальное перемещение файла поможет определить, что не так с файлом или с местом его сохранения. При сохранении файла Excel по сети или на веб-сервере может возникнуть несколько проблем. Рекомендуется сохранить файл локально. Это следует делать в следующих случаях:

• Папка «Мои документы» перенаправлена ​​на сервер

• Автономные файлы

• Открытие файлов из веб-папки или SharePoint

• Удаленный рабочий стол/Citrix

• Сетевые устройства

• Виртуальная среда. Дополнительные сведения о программном обеспечении Майкрософт, работающем в виртуализированной среде, см. в следующей статье: Политика поддержки программного обеспечения Майкрософт, работающего в программном обеспечении для аппаратной виртуализации, отличном от Майкрософт.

Память

Файлы Excel могут стать довольно большими, если вы начнете добавлять большое количество форматирования и форм. Убедитесь, что в вашей системе достаточно оперативной памяти для запуска приложения. Системные требования для пакетов Microsoft Office см. в следующих статьях Microsoft: 

.

Системные требования для Office 2016

Системные требования для Office 2013

Системные требования для Office 2010

Системные требования для Excel 2007


В Office 2010 представлены собственные 64-разрядные версии продуктов Office, позволяющие использовать преимущества большей вычислительной мощности. Дополнительные сведения о 64-разрядных выпусках Office см. в следующих статьях Microsoft:
.

64-разрядные версии Office 2013

Общие сведения о 64-разрядной версии Office

Принтеры и видеодрайверы

Когда вы запускаете Excel, он проверяет драйверы принтера и видео по умолчанию, которые будут отображать ваши книги Excel. Excel интенсивно использует принтер и будет работать медленнее, если файлы Excel будут сохранены в режиме предварительного просмотра с разрывом страницы. Тестирование файла с использованием различных принтеров, таких как драйвер принтера Microsoft XPS Document Writer или видеодрайвер VGA, позволит определить, связана ли проблема с конкретным принтером или видеодрайвером.

Если у вас по-прежнему возникают проблемы с зависанием или сбоем в Excel после того, как вы проработаете методы решения, упомянутые здесь, вам следует обратиться в службу поддержки Microsoft для устранения неполадок.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

7 основных причин и решений проблемы «Формула Excel не работает»

Microsoft Excel — мощный инструмент для создания и хранения ценной информации и баз данных о различных функциях или аспектах. Возможности включают не только вычисления, сводные таблицы и сводные графики, но также поддерживают язык программирования макросов, известный как Visual Basic для приложений. MS Excel широко используется в качестве стандартного инструмента для создания, визуализации и анализа данных. Чтобы стать высококвалифицированным специалистом в Microsoft Excel для финансового анализа, изучение и освоение формул Excel необходимо любому пользователю.

Электронные таблицы MS Excel также используются аналитиками финансовых данных и инвестиционными банкирами для представления аналитических данных, визуализации и финансового моделирования. Но иногда вы можете столкнуться с проблемами, даже используя такой сложный программный инструмент. Можете ли вы представить, если вы работаете в сжатые сроки и вдруг понимаете, что ваша формула Excel не работает должным образом? Конечно, это хаос, но не волнуйтесь, мы здесь, чтобы помочь вам, если ваша формула Excel не работает «.

Любой пользователь MS Excel может столкнуться с подобными проблемами, которые могут вызвать огромное разочарование, особенно в случае, когда все формулы Excel внезапно перестают работать. При работе с формулами Excel могут возникнуть некоторые основные проблемы, с которыми вы можете столкнуться: формула не вычисляет значение или формула Excel не автоматически обновляет значение . Если вы столкнулись с похожими проблемами, вам больше не нужно беспокоиться, так как вы собираетесь решить проблемы через мгновение и получите все ответ на ваши вопросы, которые у вас в голове.

Часть 1. Основные расчеты и термины в MS Excel

Вы можете пропустить этот раздел и перейти к следующему, если вы не новичок в Microsoft Excel и хорошо знакомы с формулами и функциями MS Excel.

Для выполнения любых вычислений на листе Excel есть два основных способа, которые вы можете использовать, например, Функции и Формулы .

Функция : Функция — это не что иное, как предопределенная формула в MS Excel. Они уже определены в программе, чтобы облегчить вам работу и исключить необходимость ввода формул вручную для ваших данных. Эти предопределенные формулы имеют удобные для пользователя имена, такие как сумма, количество, среднее значение и поиск максимального значения или минимального значения для диапазона ячеек. «СРЗНАЧ» — это функция, которая используется для вычисления среднего значения элементов в заданном диапазоне ячеек. Например, если вы примените функцию «=СРЗНАЧ(C2:C7)», она рассчитает средние значения элементов в ячейках от C2 до C7. «=AVERAGE(C2:C7)» содержит один аргумент, бит, если вы хотите передать несколько аргументов, они должны быть разделены запятыми. Например, «=СРЗНАЧ(C2:C7, B1:B4, D3:D9)» будет вычислять среднее значение всех диапазонов ячеек в трех аргументах.

Формула: Формула, с другой стороны, является определяемым пользователем выражением в Excel. Она выполняет указанную операцию над ячейкой или над значениями в диапазон ячеек. Например, выражение «=D2+D23+D4» найдет сумму диапазона значений от ячейки D2 до D4. Следует отметить, что выражение добавляет все ячейки, т.е. D2 +D3+D4 для данного выражения. С другой стороны, используя функцию или предопределенную формулу, вы можете использовать «= СУММ (D2: D4), и это даст сумму для диапазона значений от ячейки D2 до ячейки D4 .

Часть 2. Как исправить ошибку «Формула Excel не работает»

Прочитав базовые сведения о вычислениях и терминах в MS Excel, вы с большей вероятностью поймете причину и способы устранения этой проблемы Excel.

1. Ячейки отформатированы как текст

2. Проблема с дополнительным пространством или пробелами

3. Рабочая книга настроена на ручной расчет

4. Скрытые и непечатаемые символы

5. Excel просто показывает формулу и не рассчитывая значение

6. Неправильное использование двойных кавычек

7. Неправильное использование «абсолютной» ссылки

1. Ячейки отформатированы как текст

Одна из наиболее вероятных причин распространенной проблемы «Формула Excel не работает или неправильно вычисляет». » заключается в том, что ячейки отформатированы как текст.

Например, если вы применяете формулу «=СУММ(D2:D4)» и она отображается как «=СУММ(D2:D4)»
вместо этого отображается результат или вычисленное значение формулы. Наиболее вероятной причиной такой проблемы является «Ячейка отформатирована как текст», и в результате Excel игнорирует формулу и принимает ее как обычный текст, который вы вводите, вместо того, чтобы вычислять значение для формулы. Такое поведение может быть связано с довольно распространенной проблемой текстового формата, возникающей при импорте данных из других исходных файлов, т. е. файла Блокнота или файла CSV.

Эту проблему можно решить, изменив формат ячейки или диапазона ячеек. Вы можете выбрать формат «Общий» или какой-либо другой формат в зависимости от ситуации. Есть несколько методов, которые вы можете применить для изменения формата ячейки.

  • Переместитесь (F2 или щелчок мышью) к проблемной ячейке, которая не дает ожидаемого результата, и снова немедленно покиньте ячейку. Это быстрое решение для одиночной формулы. Но если у вас возникли проблемы со многими формулами, это займет много времени.

  • Чтобы обновить все формулы Excel, вы можете использовать опцию «Найти и заменить» (нажав Ctrl + F), а затем заменить = на =, это обновит формулы Excel, и они могут начать нормально рассчитываться.

2. Проблема лишнего пробела или пробелов

Как и в предыдущем случае с форматированием ячеек, если вы извлекли данные в формате CSV-файла, то, скорее всего, вы столкнетесь с другой проблемой лишних ненужных пробелов. Вам придется столкнуться с этой проблемой в основном в случае использования функции VLOOKUP в MS Excel. Это используется для поиска и извлечения данных в таблице из определенной таблицы. Если столбцы поиска расположены справа, значения поиска должны отображаться в первом столбце таблицы. Эта функция поддерживает точное совпадение, а также приближенное и подстановочные знаки для частичного совпадения. Трудно идентифицировать и удалить эти лишние ненужные пробелы. Например, если вы хотите использовать функцию ВПР для поиска имени ученика в качестве основы, и если в вашем случае у вас есть два ученика с похожими именами. . Например, как указано в таблице ниже.


Саймон Тоффел

12

Саймон Тоффел

13

На наш взгляд они выглядят одинаково, но в имени есть два пробела. Теперь, используя ВПР, вы не получите ожидаемого результата, так как он не соответствует критериям функции ВПР, и вы получите ошибку. Вы можете легко решить эту проблему, используя Обрезать формулу , которая найдет более одного пробела между словами и удалит их. Поэтому всегда рекомендуется использовать Формулу обрезки перед использованием ВПР.

3. Рабочая книга настроена на ручной расчет

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

Чтобы решить эту проблему, вы можете снова включить автоматический расчет. Для этого нажмите кнопку Вычислить в левом углу Excel или нажмите F9. Другой способ переключить рабочую книгу обратно на автоматический расчет — перейти к параметру «Расчет по формулам».

4. Скрытые символы и непечатаемые символы

Подобно предыдущим проблемам, это также является вероятной причиной того, что формула Excel не работает. Если ваши данные содержат такие символы, которые скрыты или непечатаемые, рекомендуется исправить это перед использованием формул Excel.

Чтобы решить эту проблему, вы можете использовать функцию CLEAN , аналогичную формуле обрезки. Его можно использовать для удаления нежелательных скрытых символов,

5. Excel просто показывает формулу и не вычисляет значение

В Excel есть возможность показать все формулы рабочей книги. Несмотря на то, что использовать эту опцию очень полезно, вам нужно проявлять некоторую осторожность при использовании этой опции «Показать формулы», так как вы можете не получить значения для примененных формул.

6. Неправильное использование двойных кавычек

При написании формул вы часто используете двойные кавычки, но важно знать, когда следует использовать двойные кавычки, а когда нет.

Читайте также: