Ошибка, возможно, неверный номер листа в книге Excel

Обновлено: 21.11.2024

Получите полный доступ к Excel Hacks, 2-е издание и более 60 000 других наименований с бесплатной 10-дневной пробной версией O'Reilly.

Есть также прямые онлайн-мероприятия, интерактивный контент, материалы для подготовки к сертификации и многое другое.

Глава 1. Как уменьшить разочарование в рабочей книге и рабочем листе

Советы 1–16

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

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

Правило 80/20

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

Советы по структуре

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

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

Излишнее распределение данных по нескольким листам

Излишнее распространение данных по разным таблицам

Наличие пустых столбцов и строк в таблицах данных

Оставлять пустые ячейки для повторяющихся данных

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

Неслучайно электронные таблицы Excel могут содержать 1 048 576 строк (65 536 до 2007 г.) и только 16 384 столбца (256 до 2007 г.). Имея это в виду, вы должны настроить таблицы так, чтобы заголовки столбцов проходили через первую строку вашей таблицы, а связанные данные располагались непрерывным образом непосредственно под соответствующими заголовками. Если вы обнаружите, что повторяете одни и те же данные снова и снова для двух или более строк в одном из этих столбцов, не поддавайтесь искушению использовать пустые ячейки для обозначения повторения.

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

Советы по форматированию

Помимо структуры, форматирование также может вызвать проблемы. Несмотря на то, что электронную таблицу должно быть легко читать и использовать, это редко должно происходить за счет эффективности. Мы твердо верим в то, что «все должно быть просто». Слишком многие люди тратят огромное количество времени на форматирование своих электронных таблиц. Хотя они не всегда осознают это, это время часто идет в ущерб эффективности. Часто чрезмерное использование форматирования увеличивает размер вашей рабочей книги, и хотя ваша рабочая книга может для вас произведение искусства, для кого-то другого это может показаться ужасным. Некоторые очень хорошие универсальные цвета, которые можно использовать в электронных таблицах, – это черный, белый и серый.

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

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

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

Объединенные ячейки также могут вызывать проблемы. База знаний Microsoft полна часто встречающихся проблем с объединенными ячейками. В качестве хорошей альтернативы используйте «Центрировать по выделению», которое можно найти в разделе «Главная» → «Группа выравнивания». Стрелка в правом нижнем углу отобразит диалоговое окно «Формат» с активной вкладкой «Выравнивание». щелкните и выберите «Формат ячеек» в контекстном меню (до 2007 г., «Формат» → «Ячейки»).

Советы по формуле

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

Предположим, например, что у вас есть таблица данных от ячейки A1 до ячейки H1000. Вы можете решить, что хотите использовать одну или несколько формул поиска Excel для извлечения необходимой информации. Поскольку ваша таблица может продолжать расти (по мере добавления новых данных), обычно ссылаются на всю таблицу, включая все строки. Другими словами, ваша ссылка может выглядеть примерно как A:H или, возможно, A1:H65536. Вы должны использовать эту ссылку, чтобы при добавлении новых данных в таблицу они автоматически упоминались в формулах.

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

Еще одна распространенная проблема с плохо разработанными электронными таблицами – мучительно медленный пересчет. Многие считают, что переключение режима вычислений в ручной через кнопку Office → Excel → Формулы (до 2007 г., Инструменты → Параметры → Расчеты; Mac OS X, Excel → Настройки → Расчет) решит эту проблему.

Однако в целом это очень плохой совет, чреватый потенциальными бедствиями. Электронная таблица — это все о формулах, расчетах и результатах, которые они дают. Если вы запускаете электронную таблицу в ручном режиме расчета, рано или поздно вы прочитаете некоторую информацию из своей электронной таблицы, которая не будет обновлена. Ваши формулы могут отражать старые значения, а не обновленные значения, потому что, когда вы переходите в режим ручного расчета, вы должны заставить Excel выполнить перерасчет, нажав клавишу F9 ( ⌘-= в Mac OS X). Однако очень легко забыть сделать это! Подумайте об этом так. Если бы тормоза вашего автомобиля терли и замедляли вашу машину, вы бы отключили педаль тормоза и полагались бы на ручной тормоз вместо того, чтобы устранять проблему? Большинство из нас и не мечтают об этом, но многие без колебаний переводят свои электронные таблицы в режим ручного расчета. Если вам нужно запустить электронную таблицу в ручном режиме расчета, у вас проблема с дизайном. Решите эту проблему должным образом и не применяйте «пластырный» подход.

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

Функции базы данных Excel предоставляют множество альтернатив формулам массива, как описано в разделе «Суммирование или подсчет ячеек без ошибочных значений» [Суммирование или подсчет ячеек без ошибочных значений]. Кроме того, в справке Excel есть несколько хороших примеров того, как можно используйте эти формулы в больших таблицах данных, чтобы получить результаты, основанные на нескольких критериях.Еще одна альтернатива, которая часто упускается из виду, — это использование функции сводных таблиц Excel, обсуждаемой в главе 4. Хотя сводные таблицы могут показаться очень сложными при первом знакомстве, мы настоятельно рекомендуем вам познакомьтесь с этой мощной функцией Excel, потому что, как только вы освоите сводные таблицы, вы удивитесь, как вы выжили без них!

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

Создайте персональный просмотр своих книг

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

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

Сначала откройте все рабочие книги, которые вам понадобятся.

Чтобы одновременно открыть несколько книг, нажмите кнопку Office → Открыть…, нажмите клавишу Ctrl (клавиша ⌘ на Mac), выбрав книги, которые хотите открыть, а затем нажмите Открыть (до 2007 г., выберите Файл → Открыть).

В любой из книг (неважно, в какой) выберите «Окна» → «Просмотр» → «Упорядочить все» (до 2007 г. выберите «Окно» → «Упорядочить»). Если установлен флажок «Окна активной книги», снимите его, а затем выберите предпочитаемое расположение окон и нажмите «ОК».

Если вы выберете Мозаичный, ваши книги будут представлены в виде мозаики, как показано на рисунке 1-1 с пустыми книгами.

Выбрав вариант "Горизонтально", вы увидите, что книги расположены в одной стопке, одна поверх другой, как показано на рис. 1-2.

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

Наконец, как показано на рис. 1–4, при выборе параметра "Каскад" все открытые книги будут располагаться друг над другом.

После того как ваши книги отобразятся в удобном для вас виде, вы можете легко перемещать данные между ними (например, копировать, вставлять, перетаскивать).

Если вы думаете, что захотите вернуться к созданному вами представлению, вы можете сохранить это предпочтительное представление как рабочее пространство . Чтобы сохранить рабочую область, просто выберите Windows → Сохранить рабочую область (до 2007 г., Файл → Сохранить рабочую область), введите имя файла рабочей области в поле «Имя файла» и нажмите «ОК». При сохранении рабочей области расширение файла будет .xlw, а не стандартное .xlsx Excel 2007. Чтобы восстановить рабочее пространство Excel до одного полного окна определенной книги, просто дважды щелкните строку заголовка (на Mac нажмите зеленую кнопку «Масштаб» в левом верхнем углу окна), появляющуюся в любой из ваших книг. Вы также можете нажать кнопку «Развернуть» в любом из окон вашей рабочей области. Когда закончите, закройте свои рабочие книги как обычно.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Исправить ошибку для определенной функции

Не видите свою функцию в этом списке? Попробуйте другие решения, перечисленные ниже.

Проблемы с вычитанием

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

Вычесть ссылку на ячейку из другой

Введите два значения в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D2 указана запланированная сумма, а в ячейке E2 — фактическая сумма. F2 имеет формулу =D2-E2.

Или используйте СУММ с положительными и отрицательными числами

Введите положительное значение в одну ячейку и отрицательное значение в другую. В третьей ячейке используйте функцию СУММ, чтобы сложить две ячейки вместе. В этом примере ячейка D6 содержит бюджетную сумму, а ячейка E6 имеет фактическую сумму в виде отрицательного числа. F6 имеет формулу =СУММ(D6,E6).

В Windows откройте панель управления регионом.

Windows 10: нажмите "Пуск", введите "Регион" и нажмите панель управления "Регион".

Windows 8: на начальном экране введите «Регион», нажмите «Настройки», а затем нажмите «Регион».

Windows 7: нажмите "Пуск", введите "Регион", а затем нажмите "Регион и язык".

На вкладке "Форматы" нажмите "Дополнительные настройки".

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

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

Повторите этот процесс для других ячеек, в которых есть ошибка.

Вычесть ссылку на ячейку из другой

Введите две даты в две отдельные ячейки. В третьей ячейке вычтите одну ссылку на ячейку из другой. В этом примере в ячейке D10 указана дата начала, а в ячейке E10 — дата окончания. F10 имеет формулу =E10-D10.

Или используйте функцию РАЗНДАТ

Введите две даты в две отдельные ячейки. В третьей ячейке используйте функцию DATEDIF, чтобы найти разницу в датах. Дополнительные сведения о функции РАЗНДАТ см. в разделе Расчет разницы между двумя датами.

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

Проверить начальные пробелы

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

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

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

Выберите столбец, содержащий дату, щелкнув его заголовок.

Нажмите Данные > Текст в столбцы.

Дважды нажмите "Далее".

На шаге 3 из 3 мастера в разделе Формат данных столбца нажмите Дата.

Выберите формат даты и нажмите "Готово".

Повторите этот процесс для других столбцов, чтобы убедиться, что они не содержат начальных пробелов перед датами.

Проверьте настройки даты вашего компьютера

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

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

Есть два решения этой проблемы. Вы можете изменить систему дат, которую использует ваш компьютер, чтобы она соответствовала системе дат, которую вы хотите ввести в Excel. Или в Excel вы можете создать новый столбец и использовать функцию ДАТА для создания истинной даты на основе даты, сохраненной в виде текста. Вот как это сделать, если система дат вашего компьютера — мм/дд/гггг, а ваша текстовая дата — 31/12/2017 в ячейке A1:

Создайте следующую формулу: =ДАТА(ПРАВО(A1,4),СРЕДИНА(A1,4,2),ЛЕВО(A1,2))

Результатом будет 31 декабря 2017 г.

Если вы хотите, чтобы формат отображался как дд/мм/гг, нажмите CTRL+1 (или + 1 на Mac).

Выберите другой язык, в котором используется формат дд/мм/гг, например английский (Великобритания). Когда вы закончите применять формат, результатом будет 31/12/2017, и это будет настоящая дата, а не текстовая дата.

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

Проблемы с пробелами и текстом

1. Выберите ячейки, на которые ссылаются

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

2. Найти и заменить

На вкладке "Главная" нажмите "Найти и выбрать" > "Заменить".

3. Замените пробелы ничем

В поле Найти введите один пробел. Затем в поле Заменить на удалите все, что там может быть.

4. Заменить или Заменить все

5. Включите фильтр

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

6. Установите фильтр

Нажмите стрелку фильтра и снимите флажок "Выбрать все". Затем установите флажок «Пробелы».

7. Установите любые неназванные флажки

Установите флажки, рядом с которыми ничего нет, например этот.

8. Выберите пустые ячейки и удалите

Когда Excel вернет пустые ячейки, выделите их. Затем нажмите клавишу Удалить. Это удалит все скрытые символы в ячейках.

9. Очистить фильтр

Нажмите на стрелку фильтра и выберите Очистить фильтр от. чтобы все ячейки были видны.

10. Результат

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

Тот же пример с ISTEXT

Здесь функция ISTEXT была добавлена в столбец F. Все ячейки в порядке, кроме одной со значением TRUE. Это означает, что в ячейке E2 есть текст. Чтобы решить эту проблему, вы можете удалить содержимое ячейки и повторно ввести значение 1865,00. Или вы также можете использовать функцию CLEAN, чтобы очистить символы, или использовать функцию REPLACE, чтобы заменить специальные символы другими значениями.

После использования CLEAN или REPLACE вы захотите скопировать результат и использовать Главная > Вставить > Специальная вставка > Значения. Возможно, вам также придется преобразовать числа, сохраненные в виде текста, в числа.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

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

Узнайте, как вводить простую формулу

Формулы – это уравнения, которые выполняют вычисления со значениями на листе. Формула начинается со знака равенства (=). Например, следующая формула добавляет 3 к 1.

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

Части формулы

Функции: функции, включенные в Excel, представляют собой разработанные формулы, которые выполняют определенные вычисления. Например, функция PI() возвращает значение числа пи: 3,142.

Ссылки: ссылки на отдельные ячейки или диапазоны ячеек. A2 возвращает значение в ячейке A2.

Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

Операторы. Оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает. Используйте + и – для сложения и вычитания значений и / для деления.

Примечание. Для некоторых функций требуются так называемые аргументы. Аргументы — это значения, которые определенные функции используют для выполнения своих вычислений. При необходимости аргументы помещаются между скобками функции ().Функция PI не требует никаких аргументов, поэтому она пуста. Некоторые функции требуют одного или нескольких аргументов и могут оставлять место для дополнительных аргументов. Вам нужно использовать запятую для разделения аргументов или точку с запятой (;) в зависимости от настроек вашего местоположения.

Например, для функции SUM требуется только один аргумент, но общее количество аргументов может достигать 255.

=SUM(A1:A10) – это пример одного аргумента.

=СУММ(A1:A10, C1:C10) — это пример нескольких аргументов.

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

Убедитесь, что вы

Начинайте каждую функцию со знака равенства (=)

Если вы опустите знак равенства, введенное вами может отображаться как текст или как дата. Например, если ввести СУММ(A1:A10), Excel отобразит текстовую строку СУММ(A1:A10) и не выполнит расчет. Если вы введете 11/2, Excel отобразит дату со 2 по ноябрь (при условии, что формат ячейки Общий) вместо деления 11 на 2.

Соответствует всем открывающим и закрывающим скобкам

Убедитесь, что все круглые скобки являются частью совпадающей пары (открывающей и закрывающей). Когда вы используете функцию в формуле, важно, чтобы каждая скобка находилась в правильном положении, чтобы функция работала правильно. Например, формула =ЕСЛИ(B5

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

Ошибки можно помечать и исправлять двумя способами: по одной за раз (например, при проверке орфографии) или сразу же, как только они появляются на листе при вводе данных.

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

Для Excel в Windows нажмите "Файл" > "Параметры" > "Формулы" или
для Excel на Mac выберите меню Excel > "Настройки" > "Проверка ошибок".

В Excel 2007 нажмите кнопку Microsoft Office > Параметры Excel > Формулы.

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

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

В разделе "Правила проверки Excel" установите или снимите флажки любого из следующих правил:

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

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

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

Введите формулу в ячейку вычисляемого столбца, а затем нажмите Ctrl + Z или нажмите "Отменить" на панели быстрого доступа.

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

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

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

Ячейки, содержащие годы, представленные в виде двух цифр. Ячейка содержит текстовую дату, которая может быть неправильно истолкована как неверное столетие при использовании в формулах. Например, дата в формуле =ГОД("1/1/31") может быть 1931 или 2031. Используйте это правило, чтобы проверить наличие неоднозначных текстовых дат.

Числа в текстовом формате или с предшествующим апострофом: ячейка содержит числа, сохраненные в виде текста. Обычно это происходит, когда данные импортируются из других источников. Числа, хранящиеся в виде текста, могут привести к неожиданным результатам сортировки, поэтому лучше преобразовать их в числа. ‘=SUM(A1:A10) рассматривается как текст.

Формулы несовместимы с другими формулами в регионе. Формула не соответствует шаблону других формул рядом с ней. Во многих случаях формулы, расположенные рядом с другими формулами, отличаются только используемыми ссылками.В следующем примере с четырьмя соседними формулами Excel отображает ошибку рядом с формулой =СУММ(A10:C10) в ячейке D4, потому что соседние формулы увеличиваются на одну строку, а эта увеличивается на 8 строк — Excel ожидает формулу =СУММ (А4:С4).

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

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

Например, Excel вставляет ошибку рядом с формулой =СУММ(D2:D4), когда применяется это правило, поскольку ячейки D5, D6 и D7 находятся рядом с ячейками, на которые ссылается формула, и ячейкой, на которую ссылается формула. содержит формулу (D8), а эти ячейки содержат данные, на которые должна была быть ссылка в формуле.

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

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

Предположим, вы хотите вычислить среднее значение чисел в следующем столбце ячеек. Если третья ячейка пуста, она не включается в расчет и результат равен 22,75. Если третья ячейка содержит 0, результат равен 18,2.

Данные, введенные в таблицу, недействительны: в таблице произошла ошибка проверки. Проверьте настройку проверки для ячейки, перейдя на вкладку "Данные" > группу "Инструменты данных" > "Проверка данных".

Выберите лист, который вы хотите проверить на наличие ошибок.

Если рабочий лист рассчитан вручную, нажмите F9, чтобы пересчитать.

Если диалоговое окно "Проверка ошибок" не отображается, нажмите вкладку "Формулы" > "Аудит формул" > кнопку "Проверка ошибок".

Если вы ранее игнорировали какие-либо ошибки, вы можете снова проверить их, выполнив следующие действия: нажмите «Файл» > «Параметры» > «Формулы». Для Excel на Mac выберите меню Excel > «Настройки» > «Проверка ошибок».

В разделе "Проверка ошибок" нажмите "Сбросить пропущенные ошибки" > "ОК".

Примечание. При сбросе пропущенных ошибок сбрасываются все ошибки на всех листах активной книги.

Совет. Переместите диалоговое окно "Проверка ошибок" чуть ниже строки формул.

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

Нажмите "Далее".

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

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

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

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

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

Excel отображает эту ошибку, когда число делится либо на ноль (0), либо на ячейку, которая не содержит значения.

Совет. Добавьте обработчик ошибок, как в следующем примере: =IF(C2,B2/C2,0)

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

Если вы используете функцию ВПР, имеет ли то, что вы пытаетесь найти, совпадение в диапазоне поиска? Чаще всего это не так.

=ЕСЛИОШИБКА(ВПР(D2,$D$6:$E$8,2,ИСТИНА),0)

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

Примечание. Если вы используете функцию, убедитесь, что имя функции написано правильно. В этом случае СУММА написана неправильно. Удалите букву «е», и Excel исправит ее.

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

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

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

Вы случайно удалили строку или столбец? Мы удалили столбец B в этой формуле =СУММ(A2,B2,C2) и посмотрите, что получилось.

Либо используйте команду "Отменить" (Ctrl+Z), чтобы отменить удаление, либо перестройте формулу, либо используйте ссылку на непрерывный диапазон, например: =СУММ(A2:C2), которая автоматически обновлялась бы при удалении столбца B.

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

Используете ли вы математические операторы (+, -, *, /, ^) с разными типами данных? Если это так, попробуйте вместо этого использовать функцию. В этом случае =СУММ(F2:F5) устранит проблему.

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

Эту панель инструментов можно перемещать или закреплять, как и любую другую панель инструментов. Например, вы можете закрепить его в нижней части окна. Панель инструментов отслеживает следующие свойства ячейки: 1) Рабочая книга, 2) Лист, 3) Имя (если ячейка имеет соответствующий именованный диапазон), 4) Адрес ячейки, 5) Значение и 6) Формула.

Примечание. В каждой ячейке может быть только одно наблюдение.

Добавить ячейки в окно просмотра

Выберите ячейки, которые вы хотите просмотреть.

Чтобы выделить все ячейки на листе с формулами, на вкладке «Главная» в группе «Редактирование» нажмите «Найти и выделить» (или можно использовать Ctrl+G или Control+G на Mac) > «Перейти к специальным» > «Формулы». .

На вкладке "Формулы" в группе "Аудит формул" нажмите "Окно наблюдения".

Нажмите "Добавить наблюдение".

Подтвердите, что вы выбрали все ячейки, которые хотите отслеживать, и нажмите "Добавить".

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

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

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

Удалить ячейки из окна просмотра

Если панель инструментов "Окно наблюдения" не отображается, на вкладке "Формулы" в группе "Аудит формул" нажмите "Окно наблюдения".

Выберите ячейки, которые хотите удалить.

Чтобы выбрать несколько ячеек, нажмите клавишу CTRL, а затем щелкните ячейки.

Нажмите «Удалить отслеживание».

Иногда сложно понять, как вложенная формула вычисляет окончательный результат, потому что есть несколько промежуточных вычислений и логических тестов. Однако с помощью диалогового окна «Вычислить формулу» можно увидеть, как различные части вложенной формулы оцениваются в том порядке, в котором вычисляется формула. Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5),0) будет легче понять, если вы увидите следующие промежуточные результаты:

В диалоговом окне "Вычислить формулу"

Описание

Сначала отображается вложенная формула. Функция СРЗНАЧ и функция СУММ вложены в функцию ЕСЛИ.

Диапазон ячеек D2:D5 содержит значения 55, 35, 45 и 25, поэтому результат функции СРЗНАЧ(D2:D5) равен 40.

Поскольку 40 не больше 50, выражение в первом аргументе функции ЕСЛИ (аргумент логическая_проверка) равно False.

Функция ЕСЛИ возвращает значение третьего аргумента (аргумент значение_если_ложь). Функция СУММ не вычисляется, поскольку она является вторым аргументом функции ЕСЛИ (аргумент значение_если_истина) и возвращается только в том случае, если выражение истинно.

Выберите ячейку, которую вы хотите оценить. Одновременно можно оценивать только одну ячейку.

Выберите вкладку "Формулы" > "Аудит формул" > "Оценить формулу".

Нажмите "Оценить", чтобы проверить значение подчеркнутой ссылки. Результат оценки выделен курсивом.

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

Кнопка "Вход" недоступна для ссылки во второй раз, когда ссылка появляется в формуле, или если формула ссылается на ячейку в отдельной книге.

Продолжайте нажимать кнопку Оценить, пока не будет оценена каждая часть формулы.

Чтобы снова просмотреть оценку, нажмите "Перезапустить".

Чтобы завершить оценку, нажмите "Закрыть".

Если ссылка пуста, в поле "Оценка" отображается нулевое значение (0).

Следующие функции пересчитываются каждый раз при изменении листа и могут привести к тому, что в диалоговом окне "Вычислить формулу" будут отображаться результаты, отличные от показанных в ячейке: СЛУЧАЙ, ОБЛАСТИ, ИНДЕКС, СМЕЩ, ЯЧЕЙКА, ДВССЫЛ, СТРОКИ, СТОЛБЦЫ, СЕЙЧАС , СЕГОДНЯ, СЛУЧАЙНО МЕЖДУ.

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

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

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

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

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

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

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

Чтобы избежать эффекта "одинокого пользователя", вы можете использовать Excel Online (урезанную веб-версию Excel) или включить функцию Общие книги. Вот краткое руководство о том, как поделиться электронной таблицей.

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

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

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

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

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

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

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

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

Связанные данные обновляются только при открытии файлов, если вы специально не нажмете «Данные > Запросы и подключения > Изменить ссылки > Обновить значения».». Вот краткая демонстрация.

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

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

Конечно, вы можете попытаться вообще избежать связанных книг, но есть вероятность, что вы в конечном итоге будете вводить одни и те же данные более чем в одну книгу, а вместе с этим возникает опасность каждый раз вводить их немного по-разному.< /p>

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

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

Возможно, вы уже страдаете от последствий проблем с проверкой данных, не понимая основной причины. Рассмотрим ситуацию, когда у вас есть список счетов в Excel. Пользователь вводит имя клиента немного по-разному в каждом счете. В результате вы получаете счета на адрес «Jones Ltd», «Jones Limited», «Jonse Ltd» и «joness».

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

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

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

Использование базы данных для структурированных данных

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

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

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

Например, если у вас есть список клиентов и сведения обо всех ваших взаимодействиях с ними, это считается системой управления взаимоотношениями с клиентами (CRM). Несмотря на свое красивое название, CRM-система представляет собой специализированную базу данных. Точно так же пакеты учетных записей, такие как QuickBooks и Sage, являются специализированными базами данных.Если вы не можете найти готовое приложение, отвечающее вашим конкретным потребностям, вы можете создать его самостоятельно или поручить его вашему ИТ-отделу или консультанту.

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

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

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

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

Например, программа может быть надежной при определении таблиц и взаимосвязей, а также при наличии надежных функций анализа и отчетности. Тем не менее, в приложении в конечном итоге отсутствуют какие-либо инструменты для определения экранов ввода данных. Очевидным примером здесь является Microsoft SQL Server. Как и в случае с другими большими системами баз данных, SQL Server заботится о внутренней части и ожидает, что вы будете использовать другой инструмент, например Visual Studio, для разработки внешнего интерфейса.

Какие параметры базы данных вам подходят?

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

Некоторые из шаблонов явно американские и не всегда учат хорошей практике, но они быстро помогут вам приступить к работе. Экраны и функции программирования могут быть довольно сложными. Готовое приложение можно развернуть для других пользователей через внутреннюю сеть (НЕ Интернет), а не через общие файловые ресурсы.

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

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

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

Отлично в Excel

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

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