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

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

Десять последних функций, применяемых пользователем, Excel автоматически группирует в категорию «10 недавно использовавшихся».

Диагностика ошибок в формулах Excel

Если Excel не может выполнить обработку формулы в ячейке и вывести результат, то он генерирует сообщение об ошибке и выводит его в данной ячейке (вместо самой формулы или ее результата). Сообщение об ошибке всегда начинается со знака «#».

Сообщения об ошибках в Excel могут принадлежать к одному из 8 типов:

######

#ЗНАЧ!

#ДЕЛ/0!

#ИМЯ?

#ЧИСЛО!

#ПУСТО!

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

Ошибка #ЗНАЧ ! возникает, когда используется недопустимый тип аргумента, например, пользователь пытается сложить текстовое и числовое значение.

Ошибка #ДЕЛ/0 появляется, когда в формуле делается попытка деления на ноль.

Сообщение об ошибке типа #ИМЯ? появляется, когда Excel не может найти имя, используемое в формуле. Например, такая ситуация возникнет, если:

o при наборе имени произошла опечатка;

o текст ошибочно не был заключен в двойные кавычки;

Сообщение об ошибке вида #ЧИСЛО! возникает в том случае, когда в формуле задан неприемлемый аргумент для функции.

Сообщение об ошибке типа #ПУСТО! появляется, когда используется ошибочная ссылка на ячейку или диапазон, например, задано пересечение двух областей, которые не имеют общих ячеек.

Ввод и обработка данных в Excel

Значительная часть работы в Excel приходится на ввод данных, их редактирование и обработку.

Рабочий лист в Excel 2007 состоит из 13384 столбцов и 1048576 строк.

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

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

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

Форматирование и защита рабочих листов

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

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

Форматирование в Excel включает в себя решение следующих вопросов:

изменение шрифта, размеров, начертания и цвета символов;

выравнивание и изменение ориентации текста и чисел в ячейках;

форматирование чисел, дат и времени;

форматирование строк и столбцов;

создание и использование пользовательских форматов;

условное форматирование;

защита ячеек, листов и рабочих книг;

использование стилей при форматировании;

применение автоформатов.

Работа с электронными таблицами

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

Рабочий лист электронной книги состоит из ячеек, каждая из которых имеет свой адрес: сочетание имени столбца и строки.Столбцы идентифицируются буквами латинского алфавита (А, B, C, D…), астроки

– арабскими цифрами (1,2,3…). Ячейка, в которой находится курсор, считается активной, то есть предназначенной для ввода данных. Например, адрес F10 говорит о том, что вводимая информация при активной ячейке F10 попадет именно в эту ячейку, а содержимое этой ячейки отразится в строке формул.

Многие команды Excel позволяют работать с блоками ячеек. Блок ячеек - это прямоугольник, задаваемый координатами противоположных углов, обычно, верхней левой и нижней правой ячеек. Имена ячеек в блоках разделяются двоеточием(:) . Например, блок А1:В4 включает в себя ячейки А1, А2, А3, А4, В1, В2, В3 и В4. Выделение блока ячеек осуществляется протаскиванием курсора мыши на нужный диапазон. При этом ячейка, начиная с которой выделяется блок, остается белого цвета, а остальные ячейки затемняются. Но блок ячеек не обязательно может включать смежные ячейки. Для выделения таких ячеек в блоке следует использовать клавишу .

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

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

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

1. #ДЕЛ/О! — данная ошибка практически всегда означает, что формула в ячейке пытается разделить какое-то значение на ноль. Чаще всего это происходит из-за того, что в другой ячейке, ссылающейся на данную, находится нулевое значение или значение отсутствует. Вам необходимо проверить все связанные ячейки на предмет наличия таких значений. Также данная ошибка может возникать, когда вы вводите неправильные значения в некоторые функции, например в ОСТАТ() , когда второй аргумент равен 0. Также ошибка деления на ноль может возникать, если вы оставляете пустые ячейки для ввода данных, а какая-либо формула требует некоторые данные. При этом будет выведена ошибка #ДЕЛ/0! , что может смутить конечного пользователя. Для этих случаев вы можете использовать функцию ЕСЛИ() для проверки, например =ЕСЛИ(А1=0;0;В1/А1) . В этом примере функция вернет 0 вместо ошибки, если в ячейке А1 находится нулевое или пустое значение.
2. #Н/Д — данная ошибка расшифровывается как недоступно, и это означает, что значение недоступно функции или формуле. Вы можете увидеть такую ошибку, если введете неподходящее значение в функцию. Для исправления проверьте прежде всего входные ячейки на предмет ошибок, особенно если в них тоже появляется данная ошибка.
3. #ИМЯ? — данная ошибка возникает, когда вы неправильно указываете имя в формуле или ошибочно задаете имя самой формулы. Для исправления проверьте еще раз все имена и названия в формуле.
4. #ПУСТО! — данная ошибка связана с диапазонами в формуле. Чаще всего она возникает, когда в формуле указывается два непересекающихся диапазона, например =СУММ(С4:С6;А1:С1) .
5. #ЧИСЛО! — ошибка возникает, когда в формуле присутствуют некорректные числовые значения, выходящие за границы допустимого диапазона.
6. #ССЫЛКА! — ошибка возникает, когда были удалены ячейки, на которые ссылается данная формула.
7. #ЗНАЧ! — в данном случае речь идет об использовании неправильного типа аргумента для функции.

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

Обработка ошибок с помощью функции ЕОШИБКА()

Перехватить любые ошибки и обработать их можно с помощью функции ЕОШИБКА() . Данная функция возвращает истину или ложь в зависимости от того, появляется ли ошибка при вычислении ее аргумента. Общая формула для перехвата выглядит так: =ЕСЛИ(ЕОШИБКА(выражение);ошибка; выражение) .

Функция если вернет ошибку (например, сообщение), если при расчете появляется ошибка. Например, рассмотрим следующую формулу: =ЕСЛИ(ЕОШИБКА(А1/А2);""; А1/А2) . При возникновении ошибки (деление на 0) формула возвращает пустую строку. Если же ошибки не возникает, возвращается само выражение А1/А2 .

Существует другая, более удобная функция ЕСЛИОШИБКА() , которая совмещает две предыдущие функции ЕСЛИ() и ЕОШИБКА() : ЕСЛИОШИБКА(значение;значение при ошибке) , где: значение — выражение для расчета, значение при ошибке — возвращаемый результат в случае ошибки. Для нашего примера это будет выглядеть так: =ЕСЛИОШИБКА(А1/А2;"") .

Читатели Лайфхакера уже знакомы с Денисом Батьяновым , который делился с нами . Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.

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

Вы не даёте заголовки столбцам таблиц

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

Пустые столбцы и строки внутри ваших таблиц

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

На одном листе располагается несколько таблиц

Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.

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

Данные одного типа искусственно располагаются в разных столбцах

Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

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

Дело в том, что данный формат содержит 2 измерения: чтобы , вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel - формул и сводных таблиц.

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

Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.

Разнесение информации по разным листам книги «для удобства»

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

Информация в комментариях

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

Бардак с форматированием

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

Объединение ячеек

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

Объединение текста и чисел в одной ячейке

Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.

Числа в виде текста в ячейке

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

Если ваша таблица будет презентоваться через LCD проектор

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

Страничный режим листа в Excel

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

Ещё больше полезной информации про Excel можно узнать на

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

Формулы могут возвращать ошибки по многим причинам. Например, формула =1/0 возвращает ошибку #ДЕЛ/0!, поскольку деление на 0 недопустимо. Предусмотрены следующие значения ошибок: #ДЕЛ/0!, #Н/Д, #ИМЯ?, #ПУСТО!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.

Преобразование ошибки в нулевое значение и использование формата для скрытия значения

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

Создание образца ошибки

Откройте чистый лист или создайте новый.

Введите 3 в ячейку B1, 0 - в ячейку C1 и формулу =B1/C1 - в ячейку A1.
В ячейке A1 отобразится значение ошибки #ДЕЛ/0!.

Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.

После знака равенства (=) введите ЕСЛИОШИБКА с открывающей круглой скобкой:
ЕСЛИОШИБКА(

Переместите курсор в конец формулы.

Введите ,0) , т. е. запятую с нулем и закрывающей круглой скобкой.
Формула =B1/C1 примет вид =ЕСЛИОШИБКА(B1/C1,0) .

Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

Применение условного формата

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

Выберите команду Создать правило .

В диалоговом окне выберите параметр .

Убедитесь, что в разделе в первом списке выбран пункт Значение ячейки , а во втором - равно . Затем в текстовом поле справа введите значение 0.

Нажмите кнопку Формат .

На вкладке Число в списке Числовые форматы выберите пункт (все форматы) .

В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК . Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

Скрытие значений ошибок путем изменения цвета текста на белый

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

Выделите диапазон ячеек, содержащих значение ошибки.

На вкладке Главная в группе Стили Условное форматирование и выберите пункт Управление правилами .
Появится диалоговое окно Диспетчер правил условного форматирования .

Выберите команду Создать правило .
Откроется диалоговое окно Создание правила форматирования .

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

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

Нажмите кнопку Формат и откройте вкладку Шрифт .

Щелкните стрелку, чтобы открыть список Цвет , и в разделе Цвета темы выберите белый цвет.

Отображение тире, #Н/Д или НД вместо значения ошибки

Возможны ситуации, когда необходимо, чтобы вместо значений ошибок в ячейках отображалась текстовая строка, например "#Н/Д", прочерк или "НД". Сделать это можно с помощью функций ЕСЛИОШИБКА и НД , как показано в примере ниже.

Описание функции

ЕСЛИОШИБКА . С помощью этой функции можно определить, содержит ли ячейка или возвращает ли формула ошибку.

НД Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис функции: =НД() .

Скрытие значений ошибок в отчете сводной таблицы

Щелкните отчет сводной таблицы.
Появится вкладка Работа со сводными таблицами .

Excel 2016 и Excel 2013: на вкладке Анализ в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Excel 2010 и Excel 2007: на вкладке Параметры в группе Сводная таблица щелкните стрелку рядом с командой Параметры и выберите пункт Параметры .

Откройте вкладку Разметка и формат и выполните одно или оба указанных ниже действия.

• Измените способ отображения ошибок. В группе Формат установите флажок Для ошибок отображать . Введите в поле значение, которое должно отображаться вместо ошибок. Чтобы в пустых полях отображались ошибки, удалите из поля весь текст.

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

Ошибки в формуле Excel отображаемые в ячейках

В данном уроке будут описаны значения ошибок формул, которые могут содержать ячейки. Зная значение каждого кода (например: #ЗНАЧ!, #ДЕЛ/0!, #ЧИСЛО!, #Н/Д!, #ИМЯ!, #ПУСТО!, #ССЫЛКА!) можно легко разобраться, как найти ошибку в формуле и устранить ее.

Как убрать #ДЕЛ/0 в Excel

Как видно при делении на ячейку с пустым значением программа воспринимает как деление на 0. В результате выдает значение: #ДЕЛ/0! В этом можно убедиться и с помощью подсказки.

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

Результат ошибочного вычисления – #ЧИСЛО!

Неправильное число: #ЧИСЛО! – это ошибка невозможности выполнить вычисление в формуле.

Несколько практических примеров:

Ошибка: #ЧИСЛО! возникает, когда числовое значение слишком велико или же слишком маленькое. Так же данная ошибка может возникнуть при попытке получить корень с отрицательного числа. Например, =КОРЕНЬ(-25).

В ячейке А1 – слишком большое число (10^1000). Excel не может работать с такими большими числами.

В ячейке А2 – та же проблема с большими числами. Казалось бы, 1000 небольшое число, но при возвращении его факториала получается слишком большое числовое значение, с которым Excel не справиться.

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

Как убрать НД в Excel

Значение недоступно: #Н/Д! – значит, что значение является недоступным для формулы:

Записанная формула в B1: =ПОИСКПОЗ(„Максим”; A1:A4) ищет текстовое содержимое «Максим» в диапазоне ячеек A1:A4. Содержимое найдено во второй ячейке A2. Следовательно, функция возвращает результат 2. Вторая формула ищет текстовое содержимое «Андрей», то диапазон A1:A4 не содержит таких значений. Поэтому функция возвращает ошибку #Н/Д (нет данных).

Ошибка #ИМЯ! в Excel

Относиться к категории ошибки в написании функций. Недопустимое имя: #ИМЯ! – значит, что Excel не распознал текста написанного в формуле (название функции =СУМ() ему неизвестно, оно написано с ошибкой). Это результат ошибки синтаксиса при написании имени функции. Например:

Ошибка #ПУСТО! в Excel

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

В данном случаи пересечением диапазонов является ячейка C3 и функция отображает ее значение.

Заданные аргументы в функции: =СУММ(B4:D4 B2:B3) – не образуют пересечение. Следовательно, функция дает значение с ошибкой – #ПУСТО!

#ССЫЛКА! – ошибка ссылок на ячейки Excel

В данном примере ошибка возникал при неправильном копировании формулы. У нас есть 3 диапазона ячеек: A1:A3, B1:B4, C1:C2.

Под первым диапазоном в ячейку A4 вводим суммирующую формулу: =СУММ(A1:A3). А дальше копируем эту же формулу под второй диапазон, в ячейку B5. Формула, как и прежде, суммирует только 3 ячейки B2:B4, минуя значение первой B1.

Когда та же формула была скопирована под третий диапазон, в ячейку C3 функция вернула ошибку #ССЫЛКА! Так как над ячейкой C3 может быть только 2 ячейки а не 3 (как того требовала исходная формула).

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

Как исправить ЗНАЧ в Excel

#ЗНАЧ! – ошибка в значении. Если мы пытаемся сложить число и слово в Excel в результате мы получим ошибку #ЗНАЧ! Интересен тот факт, что если бы мы попытались сложить две ячейки, в которых значение первой число, а второй – текст с помощью функции =СУММ(), то ошибки не возникнет, а текст примет значение 0 при вычислении. Например:

Решетки в ячейке Excel

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

Так решетки (######) вместо значения ячеек можно увидеть при отрицательно дате. Например, мы пытаемся отнять от старой даты новую дату. А в результате вычисления установлен формат ячеек «Дата» (а не «Общий»).

Неправильный формат ячейки так же может отображать вместо значений ряд символов решетки (######).