Функция бизвлечь возвращает сообщение об ошибке число

Функция

БИЗВЛЕЧЬ()

, английский вариант DGET(),

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

Функция

БИЗВЛЕЧЬ()

относится к наиболее редко используемым функциям и этому есть объективные причины. Рассмотрим синтаксис этой функции и причины ее непопулярности.

Синтаксис функции

БИЗВЛЕЧЬ()

Для использования этой функции требуется чтобы:

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

БИЗВЛЕЧЬ(

база_данных;поле;условия

)

База_данных

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

Базе_данных

строки называются записями, а столбцы — полями.

Поле

— заголовок столбца, из которого выводится значение, если выполняется условие. Аргумент

Поле

можно заполнить введя:

• текст, представляющий собой название одного из заголовков
  
  Базы_данных
  
  . Текст указывается в двойных кавычках, например «Возраст» или «Урожай»,
• число (без кавычек), задающее положение столбца в
  
  Базе_данных
  
  : 1 — для первого столбца, 2 — для второго и т.д.
• ссылку на заголовок столбца.

Условия

— интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для

БИЗВЛЕЧЬ()

аналогична структуре для

Расширенного фильтра

и, например, функции

БДСУММ()

.

Если ни одна из записей не удовлетворяет условию, функция

БИЗВЛЕЧЬ()

возвращает значение ошибки #ЗНАЧ!

Если условию удовлетворяет более чем одна запись, функция

БИЗВЛЕЧЬ()

возвращает значение ошибки #ЧИСЛО!

Задачи

Предположим, что в диапазоне

A

10:С15

имеется таблица продаж (

База_данных

), содержащая поля (столбцы)

Товар

,

Продавец

и

Продажи

(см.

файл примера

).

Сформулируем задачи в виде вопросов.

Вопрос

1 (Продал ли

Мясо

Продавец

Белов

? Если продал, то за сколько?).

Найдем строку, в которой в столбце

Товар

содержится значение

Мясо

, а столбце

Продавец

содержится значение

Белов

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

Продажи

.

Алгоритм следующий:

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

• Условия отбора должны быть записаны в специальном формате: =»=Мясо» (будет производиться поиск в столбце
  
  Товар
  
  только значений
  
  точно совпадающих
  
  со словом
  
  Мясо
  
  (или
  
  мясо
  
  ,
  
  МЯСО
  
  , т.е. без учета регистра). Если написать условие не в виде =»=Мясо», а просто ввести в ячейку значение
  
  Мясо
  
  , то условию будут удовлетворять текстовые строки, которые
  
  содержат
  
  слово
  
  Мясо
  
  , например, «
  
  Свежее
  
  МЯСО», «Мясо барана»
  
  и пр.)
• Предполагаем, что
  
  база_данных
  
  (исходная таблица) находится в
  
  A10:C15
  
  .
  
  С10
  
  – это ссылка на заголовок столбца, из которого выводится значение, если выполняется условие.
  
  B4:С5
  
  – ссылка на табличку критериев (см. рисунок выше) Итоговая формула выглядит так
  
  =БИЗВЛЕЧЬ(A10:C15;C10;B4:C5)
  

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

С

(450).

Вроде функция не плохо справляется, но что произойдет, если в таблице нет удовлетворяющих критерию записей? Функция вернет значение ошибки #ЗНАЧ! A что произойдет, если в таблице 2 и более записи удовлетворяющие критерию? Функция вернет значение ошибки #ЧИСЛО! К сожалению, в EXCEL нет функции, умеющей различать эти ошибки: для

ЕОШ()

,

ЕОШИБКА()

,

ЕСЛИОШИБКА()

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

Таким образом, функция

БИЗВЛЕЧЬ()

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

=СУММПРОИЗВ((A11:A15=»Мясо»)*((B11:B15)=»Белов»)*C11:C15)

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

Вопрос 2 (Есть ли товар ФРУКТЫ (с учетом регистра)?).

Выполним поиск в столбце

Товар

слова ФРУКТЫ с учетом регистра (например, слово

фрукты

не будет удовлетворять условию поиска).

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ. Для этого введем в ячейку

С3

файла примера формулу

=СОВПАД(«ФРУКТЫ»;A11)

, а в

С2

вместо заголовка введем произвольный поясняющий текст, например, «

ФРУКТЫ с учетом регистра

» (заголовок не должен повторять заголовки исходной таблицы).

Записать формулу можно так

=БИЗВЛЕЧЬ(A10:A15;A10;C2:C3)

Результат — слово ФРУКТЫ (значит такое слово есть в диапазоне

А10:А15

и оно единственное).

Альтернативная формула:

=ЕСЛИ(СУММПРОИЗВ(—СОВПАД(«ФРУКТЫ»;A11:A15));»ФРУКТЫ»;»Нет»)

Вопрос 3 (Есть ли продавец с фамилией начинающейся на

Ро

?).

Выполним поиск в столбце

Продавец

с использованием

подстановочного знака

*.

В качестве условия можно записать в ячейке

B3

формулу =»=Ро*»

Формула

=БИЗВЛЕЧЬ(B10:B15;B10;B2:B3)

вернет слово

Рощин

(значит в диапазоне

B10:B15

имеется такая фамилия и она единственная начинается на

Ро

).

В этом случае гораздо предпочтительнее выглядит формула

=ВПР(«Ро*»;B11:B15;1;ЛОЖЬ)

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

Ро

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

БИЗВЛЕЧЬ()

.

Вопрос 4 (Есть ли продавец с фамилией длиной 5 букв?).

Выполним поиск в столбце

Продавец

с использованием

подстановочного знака

?.

В качестве условия можно записать в ячейке

А3

формулу =»=?????»

Формула

=БИЗВЛЕЧЬ(B10:B15;B10;A2:A3)

вернет ошибку #ЧИСЛО! (т.к. таких фамилий несколько).

В этом случае гораздо предпочтительнее выглядит формула

=ВПР(«?????»;B11:B15;1;ЛОЖЬ)

, по тем же причинам, что и в предыдущей задаче.

Вывод

: забудьте про функцию

БИЗВЛЕЧЬ()

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

Функция БИЗВЛЕЧЬ извлекает из столбца списка или базы данных одно значение, удовлетворяющее заданным условиям.

Описание функции БИЗВЛЕЧЬ

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

Синтаксис

=БИЗВЛЕЧЬ(база_данных; поле; условия)

Аргументы

Замечания

• Если ни одна из записей не удовлетворяет условию, функция БИЗВЛЕЧЬ возвращает значение ошибки #ЗНАЧ!.
• Если условию удовлетворяет более чем одна запись, функция БИЗВЛЕЧЬ возвращает значение ошибки #ЧИСЛО!.
• В качестве аргумента «условия» можно использовать любой диапазон, который содержит хотя бы один заголовок столбца и хотя бы одну ячейку с условием, расположенную под заголовком столбца.

  Например, если диапазон G1:G2 содержит заголовок столбца «Доход» в ячейке G1 и значение 10 000 ₽ в ячейке G2, можно определить диапазон «СоответствуетДоходу» и использовать это имя как аргумент «условия» в функции баз данных.

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

Пример

Функция БИЗВЛЕЧЬ в Excel для удобства поиска информации в больших массивах данных. Она предназначена для поиска данных в таблице или базе данных на основе критериев, указанных в качестве ее параметров, и возвращает искомое значение, если оно было найдено, или код ошибки, если совпадения найдены не были или с учетом введенных критериев поиска были найдены 2 и более совпадений.

Пример работы функции БИЗВЛЕЧЬ при выборке данных из таблицы Excel

Как создать базу данных в Excel? Базой данных в программе Excel считается таблица, которая была создана с учетом определенных требований:

1. Заголовки таблицы должны находиться в первой строке.
2. Любая последующая строка должна содержать хотя бы одну непустую ячейку.
3. Объединения ячеек в любых строках запрещены.
4. Для каждой ячейки каждого столбца должен быть определен единый тип хранящихся данных.
5. Диапазон базы данных должен быть отформатирован в качестве списка и иметь свое имя.

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

Функция БИЗВЛЕЧЬ хорошо работает с корректно отформатированными таблицами.



Примеры использования функции БИЗВЛЕЧЬ в Excel

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

Вид таблиц данных и критериев:

В ячейке B2 запишем условие отбора данных следующим способом:

=МИН(СТОЛБЕЦ(B1))

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

В результате получим следующее:

В ячейке A4 запишем следующую формулу:

Описание аргументов:

• A8:F15 – диапазон ячеек, в которых хранится БД;
• 1 – числовое указание номера поля (столбца), из которого будет выводиться значение (необходимо вывести Бренд);
• A2:F3 – диапазон ячеек, в которых хранится таблица критериев.

Результат вычислений:

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

Пример составления запросов для выборки из базы данных в Excel

Пример 2. Используя таблицу из предыдущего примера выводить все данные для отдельных брендов, указанных в качестве критерия поиска (например, отобразить все характеристики модели бренда Apple и т. д.)

В данном случае для вывода данных о бренде Meizu запишем в ячейку A3 условие:

=»=Meizu»

Для решения задачи можно в каждую ячейку ввести формулу БИЗВЛЕЧЬ, но это займет слишком много времени. Поэтому выделим диапазон ячеек A4:F4 и введем следующую формулу массива CTRL+SHIFT+Enter:

Запись СТОЛБЕЦ(A1:F1) интерпретируется как отдельные ссылки на ячейки из диапазона A1:F1, а функция СТОЛБЕЦ поочередно возвращает ссылки на поля данных исследуемой таблицы.

Получаем следующий результат:

Существенный недостаток функции БИЗВЛЕЧЬ – отсутствие возможности вывода сразу нескольких записей, если были найдены 2 и более совпадений. В таких случаях используют комбинации других функций Excel.

Описание возможностей функции БИЗВЛЕЧЬ в Excel

Функция БИЗВЛЕЧЬ имеет следующую синтаксическую запись:

=БИЗВЛЕЧЬ(база_данных;поле;условия)

Описание аргументов:

• база_данных – обязательный для заполнения, принимает ссылку на диапазон ячеек, являющихся таблицей, отформатированной в соответствии с требованиями, которые предъявляют к базам данных в Excel, списком или БД;
• поле – обязательный для заполнения, принимающий текстовые строки (наименование поля данных БД) или числовые значения, соответствующие номеру поля данных (столбца, при этом отсчет начинается с единицы: первый столбец соответствует числу 1 и т. д.); В качестве аргумента может быть указана ссылка на ячейку, в которой содержится число или текст, указывающие на поле данных БД, с которой будет работать рассматриваемая функция.
• условия – обязательный для заполнения, принимает ссылку на диапазон ячеек с критериями поиска в таблице или БД. Минимальным диапазоном условия является ссылка на две ячейки, одна из которых содержит наименование поля БД, а вторая – условие поиска.

Примечания:

1. Результат выполнения функции является кодом ошибки #ЗНАЧ!, если ей не удалось найти ни одной записи, удовлетворяющей поставленным критериям поиска.
2. При нахождении двух и более записей в таблице или БД, соответствующих указанным критериям поиска, функция БИЗВЛЕЧЬ генерирует код ошибки #ЧИСЛО!
3. Диапазон условий не рекомендуют размещать под таблицей или БД, поскольку она может пополняться новыми записями (строками данных) в дальнейшем.
4. При составлении условий нужно учитывать следующие правила:

• для точных совпадений текстовые условия помещают в кавычки. Пример записи: =«=male» — для поиска по половому признаку;
• для частичных совпадений указывают текст, например Samsung. Любая строка, содержащая данную подстроку (например, Samsung S9, Samsung Galaxy) будет считаться найденным совпадением.

Функция БИЗВЛЕЧЬ() в MS EXCEL

Функция БИЗВЛЕЧЬ() , английский вариант DGET(), извлекает из столбца (списка) или таблицы отдельное значение, удовлетворяющее заданным условиям.

Функция БИЗВЛЕЧЬ() относится к наиболее редко используемым функциям и этому есть объективные причины. Рассмотрим синтаксис этой функции и причины ее непопулярности.

Синтаксис функции БИЗВЛЕЧЬ()

Для использования этой функции требуется чтобы:

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

БИЗВЛЕЧЬ(база_данных;поле;условия)
База_данных представляет собой диапазон ячеек с данными связанными логически, т.е. таблицу. Верхняя строка таблицы должна содержать заголовки всех столбцов. В Базе_данных строки называются записями, а столбцы — полями.
Поле — заголовок столбца, из которого выводится значение, если выполняется условие. Аргумент Поле можно заполнить введя:

• текст, представляющий собой название одного из заголовков Базы_данных. Текст указывается в двойных кавычках, например «Возраст» или «Урожай»,
• число (без кавычек), задающее положение столбца в Базе_данных: 1 — для первого столбца, 2 — для второго и т.д.
• ссылку на заголовок столбца.

Условия — интервал ячеек, который содержит задаваемые условия (т.е. таблица критериев). Структура таблицы с критериями отбора для БИЗВЛЕЧЬ() аналогична структуре для Расширенного фильтра и, например, функции БДСУММ() .

Если ни одна из записей не удовлетворяет условию, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЗНАЧ!

Если условию удовлетворяет более чем одна запись, функция БИЗВЛЕЧЬ() возвращает значение ошибки #ЧИСЛО!

Предположим, что в диапазоне A10:С15 имеется таблица продаж (База_данных), содержащая поля (столбцы) Товар, Продавец и Продажи (см. файл примера ).

Сформулируем задачи в виде вопросов.

Вопрос 1 (Продал ли Мясо Продавец Белов? Если продал, то за сколько?). Найдем строку, в которой в столбце Товар содержится значение Мясо, а столбце Продавец содержится значение Белов. Если такая строка есть в таблице, то выведем соответствующее значение из столбца Продажи.

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

• Условия отбора должны быть записаны в специальном формате: =»=Мясо» (будет производиться поиск в столбце Товар только значений точно совпадающих со словом Мясо (или мясо, МЯСО, т.е. без учета регистра). Если написать условие не в виде =»=Мясо», а просто ввести в ячейку значение Мясо, то условию будут удовлетворять текстовые строки, которые содержат слово Мясо, например, «СвежееМЯСО», «Мясо барана» и пр.)
• Предполагаем, что база_данных (исходная таблица) находится в A10:C15. С10 – это ссылка на заголовок столбца, из которого выводится значение, если выполняется условие. B4:С5– ссылка на табличку критериев (см. рисунок выше) Итоговая формула выглядит так =БИЗВЛЕЧЬ(A10:C15;C10;B4:C5)

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

Вроде функция не плохо справляется, но что произойдет, если в таблице нет удовлетворяющих критерию записей? Функция вернет значение ошибки #ЗНАЧ! A что произойдет, если в таблице 2 и более записи удовлетворяющие критерию? Функция вернет значение ошибки #ЧИСЛО! К сожалению, в EXCEL нет функции, умеющей различать эти ошибки: для ЕОШ() , ЕОШИБКА() , ЕСЛИОШИБКА() эти ошибки неразличимы. Т.е. пользователю нужно помнить какой вид ошибки возвращается в каждом случае и принимать в зависимости от этого решение — это не удобно.

Таким образом, функция БИЗВЛЕЧЬ() НЕ возвращает ошибку только в случае, если удовлетворяющая критерию запись единственная. Более логичным решением выглядит формула =СУММПРОИЗВ((A11:A15=»Мясо»)*((B11:B15)=»Белов»)*C11:C15) , которая возвращает ошибку только в случае, если запись удовлетворяющая критерию не обнаружена. Если обнаружено 2 и более записи, то соответствующие значения суммируются.

Вопрос 2 (Есть ли товар ФРУКТЫ (с учетом регистра)?). Выполним поиск в столбце Товар слова ФРУКТЫ с учетом регистра (например, слово фрукты не будет удовлетворять условию поиска).

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы. Формула должна возвращать результат ИСТИНА или ЛОЖЬ. Для этого введем в ячейку С3 файла примера формулу =СОВПАД(«ФРУКТЫ»;A11) , а в С2 вместо заголовка введем произвольный поясняющий текст, например, «ФРУКТЫ с учетом регистра» (заголовок не должен повторять заголовки исходной таблицы).

Записать формулу можно так =БИЗВЛЕЧЬ(A10:A15;A10;C2:C3) Результат — слово ФРУКТЫ (значит такое слово есть в диапазоне А10:А15 и оно единственное).

Альтернативная формула: =ЕСЛИ(СУММПРОИЗВ(—СОВПАД(«ФРУКТЫ»;A11:A15));»ФРУКТЫ»;»Нет»)

Вопрос 3 (Есть ли продавец с фамилией начинающейся на Ро?). Выполним поиск в столбце Продавец с использованием подстановочного знака *.

В качестве условия можно записать в ячейке B3 формулу =»=Ро*»

Формула =БИЗВЛЕЧЬ(B10:B15;B10;B2:B3) вернет слово Рощин (значит в диапазоне B10:B15 имеется такая фамилия и она единственная начинается на Ро).

В этом случае гораздо предпочтительнее выглядит формула =ВПР(«Ро*»;B11:B15;1;ЛОЖЬ) , т.к. не требуется создавать отдельную табличку с критериями и в случае наличия нескольких фамилий начинающихся на Ро, будет выведена первая фамилия, а не ошибка #ЧИСЛО!, как в случае с функцией БИЗВЛЕЧЬ() .

Вопрос 4 (Есть ли продавец с фамилией длиной 5 букв?). Выполним поиск в столбце Продавец с использованием подстановочного знака ?.

В качестве условия можно записать в ячейке А3 формулу =»=. «

Формула =БИЗВЛЕЧЬ(B10:B15;B10;A2:A3) вернет ошибку #ЧИСЛО! (т.к. таких фамилий несколько).

В этом случае гораздо предпочтительнее выглядит формула =ВПР(«. «;B11:B15;1;ЛОЖЬ) , по тем же причинам, что и в предыдущей задаче.

Вывод : забудьте про функцию БИЗВЛЕЧЬ() , если только Вы не на 100% уверены в том, что в таблице гарантировано имеются данные удовлетворяющие критериям, причем в единственном экземпляре.

БИЗВЛЕЧЬ (DGET)

Находит значение в табличном массиве. Для поиска используется метод, похожий на SQL.

Пример использования

БИЗВЛЕЧЬ(database, field, criteria)

database – массив данных или диапазон. Первая строка должна содержать заголовки столбцов.

field – указание на столбец в диапазоне database , который содержит требуемую информацию.

• field может быть либо текстовой меткой, соответствующей заголовку столбца в первой строке database , либо номером требуемого столбца, при том что первый столбец обозначается номером 1.

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

Примечания

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

БИЗВЛЕЧЬ – единственная функция баз данных, которая не является агрегирующей. Если фильтруемые данные не содержат подходящих элементов или число таких элементов превышает 1, функция вернет сообщение об ошибке.

ВПР : Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки.

БДДИСПП : Вычисляет дисперсию совокупности данных в табличном массиве. Для расчетов используется метод, похожий на SQL.

БДДИСП : Вычисляет дисперсию выборки данных в табличном массиве. Для расчетов используется метод, похожий на SQL.

БДСУММ : Вычисляет сумму значений в табличном массиве. Для расчетов используется метод, похожий на SQL.

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

ДСТАНДОТКЛ : Вычисляет стандартное отклонение выборки данных в таблице. Для расчетов используется метод, похожий на SQL.

БДПРОИЗВЕД : Вычисляет произведение значений в табличном массиве по принципу, похожему на SQL.

ДМИН : Находит минимальное значение в табличном массиве. Для поиска используется метод, похожий на SQL.

ДМАКС : Находит максимальное значение в табличном массиве. Для поиска используется метод, похожий на SQL.

БСЧЁТА : Подсчитывает количество числовых и текстовых значений в табличном массиве по принципу, похожему на SQL.

БСЧЁТ : Подсчитывает количество числовых значений в табличном массиве по принципу, похожему на SQL.

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

Трюк №80. Трюк с одной функцией баз данных Excel, чтобы она работала вместо нескольких функций

Функции баз данных Excel, например, БДСУММ (DSUM), БСЧЁТ (DCOUNT) и другие могут работать вместо, возможно, тысяч функций, сокращая, таким образом, и время пересчета, и объем рабочей книги. При работе с функциями баз данных Excel можно указать до 256 различных критериев.

Может понадобиться, например, суммировать значения в столбце А, только если соответствующие значения в столбце В больше 100, а соответствующий возраст в столбце С меньше 40. Если, однако, вы хотите суммировать те значения, для которых соответствующие значения в столбце В меньше 50, понадобится использовать другую функцию и другой диапазон критерия. Было бы намного проще, если бы была только одна функция, для которой можно быстро и просто изменить критерий! Если вы раньше никогда не работали с функциями баз данных Excel, настоятельно рекомендуем познакомиться с ними, так как они прекрасно подходят для выделения статистической информации из базы данных или таблицы Excel.

Чтобы увидеть, как это работает, задайте данные. Укажите те же заголовки столбцов, но в самих столбцах могут быть любые фиктивные данные. Присвойте этой таблице данных, включая все заголовки столбцов, имя AllData. Назовите лист Data. Добавьте еще один рабочий лист и назовите его Results. В ячейке А2 введите следующую формулу: =Data!A1.

Скопируйте ее до ячейки F2, чтобы получить зеркальное отображение заголовков вашей таблицы. В ячейке A3 введите любое имя, присутствующее в таблице на листе с данными, например, John D. Затем в ячейке ВЗ введите формулу =DGET(AllData;В2;$А$2:$А$3), в русской версии Excel =БИЗВЛЕЧЬ(AllData;В2;$А$2:$А$3). Скопируйте эту формулу до ячейки F3 и отформатируйте ячейки C3:F3 нужным образом.

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

Можно продвинуться еще дальше и использовать функцию БСЧЁТ (DCOUNT), чтобы узнать количество человек, для которых значение Full Cost больше одного указанного числа, а значение Percent Paid меньше другого. Для этого сначала необходимо создать динамический именованный диапазон для столбцов Full Cost и Percent Paid. В поле Имя (Names in workbook) введите FullCost. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($С$2;0;0;COUNTA($С$2:$С$1000);1), в русской версии Excel =СМЕЩ($С$2;0;0;СЧЁТЗ($С$2:$С$1000);1). После этого в поле Имя (Names in workbook) введите PercentPaid. В поле Формула (Refers to) введите следующую формулу и щелкните на кнопке Добавить (Add): =OFFSET($E$2;0;0;COUNTA($E$2:$E$1000);1), в русской версии Excel =СМЕЩ($Е$2;0;0;СЧЁТЗ($Е$2:$Е$1000);1).

Перейдите на лист Results, выделите ячейку АИ и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =Full_Cost. Щелкните на кнопке ОК. Выделите ячейку АИ и выберите команду Данные → Проверка (Data → Validation). В поле Тип данных (Allow) выберите Список (List), а в поле Источник (Source) введите =Percent_Paid. Щелкните на кнопке ОК.

В ячейке А12 введите формулу =Data!C1. Выделите ячейку В12 и введите следующую формулу: =Data!E1. Выделите ячейку А13 и введите следующую формулу: =»>»&А11. Выделите ячейку В13 и введите следующую формулу: =» =, >, Базы данных • Формулы • Функции

Функции баз данных

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

• таблица должна обязательно содержать заголовки столбцов. Эти заголовки должны располагаться строго в одной строке, не должны содержать объединенных и пустых ячеек.
• таблица должна быть неделимая, т.е. не должна содержать полностью пустых строк и столбцов, а так же объединенных ячеек
• в каждом столбце должна содержаться однотипная информация: если в столбце должны содержаться даты, значит кроме дат там не должно быть ничего другого; если в столбце числа(суммы, кол-во) — значит должны быть только числа. Не следует при отсутствии чисел оставлять ячейку пустой или ставить пробел. Вместо этого необходимо ставить 0.

Для разбора функции баз данных возьмем следующий пример таблицы:

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

Именно для этой таблицы будут приведены все примеры описаний функций. И критерии заданы следующие: отбирать из поля «Дерево» Яблони с высотой больше 3 и меньше 6 и Вишни, со значением в поле «Возраст» больше 8. Если посмотреть на таблицу данных(из которой будут отбираться данные и производится расчеты функциями), то этим критериям отвечают только две строки: строки 9 и 10 листа.
Как видно, в качестве критериев можно указывать выражение в виде: >6 , , <>0 (не равно нулю), >=7 , . Так же знаки равенства и сравнения можно применять и с текстовыми данными, например: <>«Яблоня» .

Все функции из категории баз данных имеют три одинаковых аргумента:
Функция(база_данных; поле; критерий)
база_данных — ссылка на ячейки данных таблицы, включая заголовок( A6:E12 ).
поле — в данном аргументе можно записать как непосредственно текст с названием столбца в кавычках («Дерево», «Возраст» или «Урожай»), так и число, задающее положение столбца в таблице: 1 — для первого поля(столбца) в таблице «Дерево», 2 — для второго поля «Высота», 3 — для третьего поля «Возраст» и так далее.
критерий — ссылка на диапазон ячеек с условиями отбора( A1:F3 ). Функция отберет данные из таблицы, которые удовлетворяют условиям, указанным в ячейках критериев. В ссылке на критерии обязательно должны быть включены названия столбцов, для которых выполняется отбор данных.

ДСРЗНАЧ (DAVERAGE) — Вычисляет среднее значение выбранных записей базы данных:
=ДСРЗНАЧ( A6:E12 ;5; A1:F3 )
=ДСРЗНАЧ( A6:E12 ;»Прибыль»; A1:F3 )
=DAVERAGE( A6:E12 ,5, A1:F3 ) вернет значение 90 000р. , т.к. сумма прибыли отобранных записей равна 180 000р., а всего отобрано 2 записи. 180 000/2 = 90 000 .

БСЧЁТ (DCOUNT) — Подсчитывает количество числовых ячеек в базе данных:
=БСЧЁТ( A6:E12 ;5; A1:F3 )
=БСЧЁТ( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNT( A6:E12 ,5, A1:F3 ) вернет число 2 , т.к. только две строки в таблице отвечают критериям

БСЧЁТА (DCOUNTA) — Подсчитывает количество непустых ячеек в базе данных:
=БСЧЁТА( A6:E12 ;4; A1:F3 )
=БСЧЁТА( A6:E12 ;»Прибыль»; A1:F3 )
=DCOUNTA( A6:E12 ,4, A1:F3 ) вернет 2, т.е. подсчитает в отвечающих критериям строках количество непустых ячеек в столбце «Прибыль»

БИЗВЛЕЧЬ (DGET) — Извлекает из базы данных одну запись, удовлетворяющую заданному условию:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F3 )
=БИЗВЛЕЧЬ( A6:E12 ;»Прибыль»; A1:F3 )
=DGET( A6:E12 ,5, A1:F3 ) для заданных условий вернет значение ошибки #ЧИСЛО! (#NUM!) , т.к. этим условиям отвечает более одной записи. Если же указать диапазон для критерия как:
=БИЗВЛЕЧЬ( A6:E12 ;5; A1:F2 ) то функция вернет значение 75 000р. , т.е. единственную запись о прибыли для Яблонь с высотой больше 3 и меньше 6 (в данный промежуток попадает лишь строка 10 — Яблона, высота 5)

ДМАКС (DMAX) — Находит максимальное значение среди выделенных записей базы данных:
=ДМАКС( A6:E12 ;5; A1:F3 )
=ДМАКС( A6:E12 ;»Прибыль»; A1:F3 )
=DMAX( A6:E12 ,5, A1:F3 ) вернет сумму 105 000р. , т.к. это максимальная прибыль из всех отвечающих критериям строк.

ДМИН (DMIN) — Находит минимальное значение среди выделенных записей базы данных:
=ДМИН( A6:E12 ;5; A1:F3 )
=ДМИН( A6:E12 ;»Прибыль»; A1:F3 )
=DMIN( A6:E12 ,5, A1:F3 ) вернет сумму 75 000р. , т.к. это минимальная прибыль из всех строк, отвечающих критериям

БДПРОИЗВЕД (DPRODUCT) — Перемножает значения определенного поля в записях базы данных, удовлетворяющих условию:
=БДПРОИЗВЕД( A6:E12 ;3; A1:F3 )
=БДПРОИЗВЕД( A6:E12 ;»Возраст»; A1:F3 )
=DPRODUCT( A6:E12 ,3, A1:F3 ) вернет 210 , т.к. будут перемножены все значения столбца «Возраст», отвечающие критериям( 14*15=210 )

ДСТАНДОТКЛ (DSTDEV) — Оценивает стандартное отклонение по выборке из выделенных записей базы данных:
=ДСТАНДОТКЛ( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛ( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEV( A6:E12 ,4, A1:F3 ) вернет 0,707107 , т.е. оценку стандартного отклонения урожайности по указанным критериям.

ДСТАНДОТКЛП (DSTDEVP) — Вычисляет стандартное отклонение по генеральной совокупности из выделенных записей базы данных:
=ДСТАНДОТКЛП( A6:E12 ;4; A1:F3 )
=ДСТАНДОТКЛП( A6:E12 ;»Урожайность»; A1:F3 )
=DSTDEVP( A6:E12 ,4, A1:F3 ) вернет 0,5 , т.е. точное стандартное отклонение урожайности по указанным критериям, если считать, что данные в базе данных описывают генеральную совокупность всех деревьев в саду.

БДСУММ (DSUM) — Суммирует числа в поле для записей базы данных, удовлетворяющих условию:
=БДСУММ( A6:E12 ;5; A1:F3 )
=БДСУММ( A6:E12 ;»Прибыль»; A1:F3 )
=DSUM( A6:E12 ,5, A1:F3 ) вернет сумму прибыли всех строк, отвечающих критериям, т.е. 180 000р.
=БДСУММ( A6:E12 ;5; A1:A2 )
=DSUM( A6:E12 ,5, A1:A2 ) вернет сумму прибыли от всех Яблонь, т.е. 225 000р.

БДДИСП (DVAR) — Оценивает дисперсию по выборке из выделенных записей базы данных:
=БДДИСП( A6:E12 ;4; A1:A2 )
=БДДИСП( A6:E12 ;»Урожайность»; A1:A2 )
=DVAR( A6:E12 ,4, A1:A2 ) вернет 0,5 , что будет оценкой дисперсии урожайности по указанным критериям, если считать, что данные в таблице являются выборкой из генеральной совокупности всех деревьев в саду

БИЗВЛЕЧЬ работа с функциями базы данных в Excel

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

Примеры работы функции базы данных БИЗВЛЕЧЬ в Excel

Допустим мы располагаем базой данных, которая экспортированная в Excel так как показано ниже на рисунке:

Наша задача найти всю информацию (номер фактуры, номер клиента, сумма и т.д.), которая относится к одной конкретной фамилии определенного клиента. Для этой цели рекомендуем воспользоваться функцией Excel для работы с базами данных – БИЗВЛЕЧЬ. Данная функция на основе критериев поискового запроса, введенных в ее аргументы, по отдельности выберите все соответствующие строки из базы данных.

Функция БИЗВЛЕЧЬ примеры в Excel

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

1. Выше базы данных добавим 4 пустых строки. Для этого достаточно выделить 4 заголовка строк листа Excel и щелкнуть правой кнопкой мышки. Из контекстного меню выбрать вставить. Или после выделения строк по заголовкам нажать комбинацию горячих клавиш CTRL+SHIFT+=.
2. Далее скопируйте все заголовки столбцов базы данных и вставьте их в первую строку листа для вспомогательной таблицы критериев.

Пространство для заполнения критериев запросов выше данных базы.

Сначала попытаемся получить номер фактуры по фамилии клиента:

1. В ячейке D2 введите фамилию Антонова.
2. В ячейке A3 введите следующую формулу:

Сразу же получаем готовый результат как показано ниже на рисунке:

Формула нашла соответствующий номер фактуры для клиента с фамилией Антонова.

Разбор принципа действия функции БИЗВЛЕЧЬ для работы с базами данных в Excel:

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

В нашем случаи функция БИЗВЕЧЬ вернула одно значение – без ошибок. Эту функцию можно так же использовать для вывода целой строки за одну операцию без копирования функции в другие ячейки с другими аргументами. Чтобы избежать необходимости указывать новый критерий для каждой ее копии составим простую формулу, в которую добавим функцию СТОЛБЕЦ. Для этого:

1. В ячейке A3 введите следующую формулу:
2. Скопируйте ее во все ячейки диапазона A3:E3.

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

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

В конструкции функции БИЗВЕЧЬ изменили мы только второй аргумент, значение которого вычисляется функцией СТОЛБЕЦ в место числа 1. Данная функция возвращает номер текущего столбца для текущей ячейки.

Бесспорное преимущество использования функции БИЗВЛЕЧЬ заключается в автоматизации. Достаточно лишь изменить критерий и в результате мы получаем уже новую строку информации из базы данных клиентов фирмы. Например, найдем данные теперь по номеру клиента 58499. Удаляем старый критерий вводим новый и сразу же получаем результат.

Данную задачу можно было бы решить и с помощью сложных формул с комбинациями функций ИНДЕКС, ПОИСКПОЗ, ВПР, ПРОСМОТР, но зачем изобретать велосипед? Функция БИЗВЛЕЧЬ прекрасно справляется с поставленной задачей и при этом весьма лаконична.

Обработка баз данных в Excel по нескольким критериям

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

1. Расширьте диапазон для просматриваемой таблицы $A$5:$E$18 в параметрах формул, так как у нас добавился новый клиент и на одну запись стало больше: Теперь функция возвращает ошибку #ЧИСЛО! так как в базе более чем 1 запись по данному критерию.
2. В поле критериев «Имя» вводим значение «Василий», а потом в поле «Фамилия» вводим значение «Великий».

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

Функция ЕОШ() в MS EXCEL

​Смотрите также​Для лечения подобных ситуаций​ на листе Исправленный​ пишут вместо 10,5​ проверки на лишние​ следующее:​ причем в единственном​B3​

Синтаксис функции

​ запись удовлетворяющая критерию​​ условие.​

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

Функция ЕОШ() vs ЕОШИБКА() и ЕСЛИОШИБКА()

​Решение​ обработки ошибок, такие​Функция ЕОШ(), английский вариант​ в Microsoft Excel​ в диапазоне​ значение 10.5 (на​

​ пробелы можно написать​сформируем список уникальных значений​ экземпляре. ​формулу =»=Ро*»​ не обнаружена. Если​B4:С5​
​ строка есть в​

​ «Возраст» или «Урожай»,​ выше).​​Функция ЕСЛИОШИБКА() впервые появилась​​ формула. Если вам​: проверьте правильность синтаксиса.​

excel2.ru

Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

​ как ЕОШИБКА, ЕОШ​ ISERR(), проверяет на​ есть мегаполезная функция​A2:A53​ американский манер). Избавиться​ следующую формулу:​ из Списка №2​В реальной жизни часто​Формула =БИЗВЛЕЧЬ(B10:B15;B10;B2:B3) вернет слово​ обнаружено 2 и​– ссылка на​ таблице, то выведем​число (без кавычек), задающее​Функция БИЗВЛЕЧЬ(), английский вариант​ в EXCEL 2007.​ нужно добавить обработчик​ Ниже приведен пример​ или ЕСЛИОШИБКА.​

Проблема: аргумент ссылается на ошибочные значения.

​ равенство значениям: #ЗНАЧ!,​ ЕСЛИОШИБКА (IFERROR), которая​) просуммируем с помощью​ от этой опечатки​

​=ДЛСТР(СЖПРОБЕЛЫ(A3))=ДЛСТР(A3)​​ (см. статью Отбор​ приходится сравнивать списки,​ Рощин (значит в​ более записи, то​ табличку критериев (см.​ соответствующее значение из​ положение столбца в​ DGET(), извлекает из​ЕСЛИОШИБКАзначениезначение_при_ошибке​ ошибок, лучше сделать​ правильно составленной формулы,​Если имеется ссылка на​

• ​ #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!,​ умеет проверять заданную​

• ​ формулы:​ можно с помощью​Если в значении есть​

​ уникальных значений) и​​ созданные разными людьми,​

• ​ диапазоне​ соответствующие значения суммируются.​ рисунок выше) Итоговая​ столбца Продажи.​Базе_данных​ столбца (списка) или​)​ это тогда, когда​ в которой функция​ ячейку с ошибочным​ #ИМЯ? или #ПУСТО!​ формулу или ячейку​=СУММЕСЛИ(исправленный!$A$2:$A$53;E3;исправленный!$B$2:$B$53)​ функции ПОДСТАВИТЬ().​ пробел, например, в​ найдем сумму масс​

• ​ в разное время,​B10:B15​

Проблема: неправильный синтаксис.

​Вопрос 2 (Есть ли​ формула выглядит так​Алгоритм следующий:​

​: 1 — для​​ таблицы отдельное значение,​Значение​ вы будете уверены,​ ЕСЛИ вкладывается в​ значением, функция ЕСЛИ​ и возвращает в​ и, в случае​В итоге получим таблицу​Для проверки на неправильный​

​ самом конце слова,​

​ каждого металла;​ содержащие опечатки, лишние​​имеется такая фамилия​ товар ФРУКТЫ (с​ =БИЗВЛЕЧЬ(A10:C15;C10;B4:C5)​Создадим табличку критериев (желательно​ первого столбца, 2​ удовлетворяющее заданным условиям.​- аргумент, проверяемый​ что формула работает​ другую функцию ЕСЛИ​ возвращает ошибку #ЗНАЧ!.​ зависимости от этого​ возникновения любой ошибки,​​ для сравнения масс​

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

​ учетом регистра)?).​

​Как видно из исходной​ над исходной таблицей,​ — для второго​Функция БИЗВЛЕЧЬ() относится к​ на возникновение ошибок.​ правильно.​ для расчета вычетов​Решение​ ИСТИНА или ЛОЖЬ.​ выдавать вместо нее​ металлов, обнаруженных в​ следующую формулу:​ ЛОЖЬ.​ из обоих списков​ неправильном формате и​ начинается на Ро).​Выполним поиск в​ таблицы, условиям удовлетворяет​ чтобы она не​ и т.д.​ наиболее редко используемым​Значение_при_ошибке​Примечание:​ на основе уровня​

​: используйте с функцией​​ЕОШзначение​ заданное значение: ноль,​ ходе инвентаризации и​=НЕ(ЕОШ(ПОДСТАВИТЬ(B3;».»;»,»)-B3))​Английские буквы в русском​ (см. статьи Отбор​ пр. В этой​В этом случае гораздо​ столбце Товар слова​ вторая запись в​ мешала добавлению новых​ссылку на заголовок столбца.​ функциям и этому​   — значение, возвращаемое​ Значения в вычислениях разделяются​ доходов.​

У вас есть вопрос об определенной функции?

​ ЕСЛИ функции для​)​

Помогите нам улучшить Excel

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

support.office.com

Функция ЕСЛИОШИБКА() в MS EXCEL

​ ФРУКТЫ с учетом​ таблице, поэтому будет​ данных в таблицу),​Условия​ есть объективные причины.​ при ошибке.​ точкой с запятой.​=ЕСЛИ(E2​ обработки ошибок, такие​Значение​ «» или что-то​ документам.​ легко обнаружить: текстовые​Часто слово может​ двух диапазонов и​

​ только само сравнение​ =ВПР(«Ро*»;B11:B15;1;ЛОЖЬ), т.к. не​

Синтаксис функции

​ регистра (например, слово​​ выведено соответствующее значение​

​ состоящую из заголовка​​  — интервал ячеек, который​ Рассмотрим синтаксис этой​

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

​ как ЕОШИБКА, ЕОШ​- ссылка на​ еще.​Сравнение масс можно производить​ значения выравниваются по​ содержать латиницу вперемешку​
​ Сравнение 2-х списков);​

​ списков, но и​ требуется создавать отдельную​ фрукты не будет​

​ из столбца​ (совпадает с названием​ содержит задаваемые условия​ функции и причины​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​ значения запятой, функция​ выразить так:​ и ЕСЛИОШИБКА. В​ ячейку или результат​Синтаксис функции следующий:​

Функция ЕСЛИОШИБКА() vs ЕОШИБКА()

​ элементарной формулой =F3=G3.​ левому краю, а​ с кириллицей. Обнаружить​для каждого металла выведем​ их подготовку к​ табличку с критериями​
​ удовлетворять условию поиска).​

​С​ заголовка столбца исходной​ (т.е. таблица критериев).​ ее непопулярности.​ #ЧИСЛО!, #ИМЯ? или​ ЕСЛИ будет рассматривать​

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

excel2.ru

Функция БИЗВЛЕЧЬ() в MS EXCEL

​ массы металлов из​ сравнению.​ и в случае​В качестве условия отбора​(450).​

​ таблицы, к которому​ Структура таблицы с​Для использования этой функции​ #ПУСТО! обычно используют​ их как одно​ A5 меньше чем​ как использовать функции​

Синтаксис функции БИЗВЛЕЧЬ()

​ необходимо проверить.​Так, в нашем примере​

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

​ критериями отбора для​
​ требуется чтобы:​​ формулу вида (см.​ дробное значение. После​ 31 500, значение умножается​ ЕСЛИ, ЕОШИБКА, ЕОШ​В отличие от функций​ можно было бы​Тот, кто никогда не​​ формате ячеек установлено​​ (буквы с, о,​сравним массы из двух​ в лаборатории был​
​ начинающихся на Ро,​​ вычисляемое при помощи​ справляется, но что​ собственно критерия (условия​ БИЗВЛЕЧЬ() аналогична структуре​​исходная таблица имела заголовки​​ файл примера):​

• ​ процентных множителей ставится​ на 15 %. Но​​ и ЕСЛИОШИБКА в​​ ЕОШИБКА() и ЕСЛИОШИБКА()​ все исправить так:​ ошибался — опасен.​
• ​ выравнивание по горизонтали​ a есть в​​ списков для каждого​​ составлен список металлов​ будет выведена первая​ формулы. Формула должна​ произойдет, если в​
• ​ отбора);​

​ для Расширенного фильтра​​ столбцов;​=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)​ символ %. Он​ ЕСЛИ это не​ формуле, если аргумент​ функция ЕОШ() считает,​Все красиво и ошибок​(Книга самурая)​ «по значению»).​

​ обоих алфавитах). Зато​ из металлов.​ с указанием их​ фамилия, а не​ возвращать результат ИСТИНА​

​ таблице нет удовлетворяющих​Условия отбора должны быть​ и, например, функции​критерии должны были оформлены​

Задачи

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

​В результате должны получить​ массы (Список №1).​

​ ошибка #ЧИСЛО!, как​ или ЛОЖЬ. Для​ критерию записей? Функция​ записаны в специальном​​ БДСУММ().​ в виде небольшой​ ошибки результат вычисления​ значение должно обрабатываться​ ли это значение,​ значения.​ #Н/Д не является​Обратите внимание, что эта​ когда они случаются​Если числовое значение​

​ с помощью формулы​

• ​ следующую таблицу:​ По определению этот​ в случае с​ этого введем в​ вернет значение ошибки​ формате: =»=Мясо» (будет​Если ни одна из​ таблицы с заголовками;​ A2/B2.​ как процентное. В​ чем 72 500. ЕСЛИ​Исправление ошибки #ЗНАЧ! в​

• ​ ошибкой.​ функция появилась только​ не по твоей​ находится в ячейке​ массива:​Вроде все просто, но​​ список не должен​​ функцией БИЗВЛЕЧЬ().​ ячейку​ #ЗНАЧ! A что​ производиться поиск в​ записей не удовлетворяет​заголовки таблицы критериев совпадали​Если результат вычисления не​ противном случае такие​ это так, значение​ функции СЦЕПИТЬ​Для обработки ошибок #ЗНАЧ!,​​ с 2007 версии​​ вине. Так в​ с текстовым форматом,​=СУММ(ЕСЛИ((КОДСИМВ(ПСТР($A3;СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР($A3)));1))>=65)*(КОДСИМВ(ПСТР($A3;​
• ​ из-за того, что​​ содержать повторяющихся значений.​​Вопрос 4 (Есть ли​С3​​ произойдет, если в​​ столбце Товар только​ условию, функция БИЗВЛЕЧЬ()​ с заголовками исходной​ является ошибкой #Н/Д,​ значения пришлось бы​​ умножается на 25 %;​​Исправление ошибки #ЗНАЧ! в​ #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!,​ Microsoft Excel. В​ Microsoft Excel, некоторые​ то, например, функция​

​СТРОКА(ДВССЫЛ(«A1:A»&ДЛСТР($A3)));1))​ элементы Списка №2​Металлы в лабораторию поступали​ продавец с фамилией​файла примера формулу​ таблице 2 и​​ значений​​ возвращает значение ошибки​

​ таблицы (если критерий​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​ вводить как дробные​ в противном случае —​ функции СРЗНАЧ или​ #ИМЯ? или #ПУСТО!​ более ранних версиях​ функции и формулы​ СУММ() его проигнорирует.​Подробности работы этой формулы​ могут содержать ошибки,​ в течение нескольких​ длиной 5 букв?).​ =СОВПАД(«ФРУКТЫ»;A11), а в​ более записи удовлетворяющие​точно совпадающих​ #ЗНАЧ!​ не задается формулой).​ #ЧИСЛО!, #ИМЯ? или​ множители, например «E2*0,25».​ на 28 %​ СУММ​ используют формулы следующего​ приходилось использовать функции​ могут выдавать ошибки​

​ Чтобы избежать этих​ в статье Определяем,​ то уже на​ лет по различным​Выполним поиск в​С2​ критерию? Функция вернет​со словом Мясо​Если условию удовлетворяет более​БИЗВЛЕЧЬ(база_данных;поле;условия)​ #ПУСТО! , то​Задать вопрос на форуме​.​Примечания:​

​ вида (см. файл​ЕОШ (ISERROR)​ не потому, что​​ ошибок читайте статью​ есть ли в​ первом шаге можем​ накладным. Движение материалов,​ столбце Продавец с​вместо заголовка введем​

​ значение ошибки #ЧИСЛО!​ (или мясо, МЯСО,​ чем одна запись,​База_данных​ формула возвращает результат​ сообщества, посвященном Excel​Чтобы использовать функцию ЕСЛИОШИБКА​ ​​ примера):​​и​ вы накосячили при​​ Преобразование чисел из​​ слове латиница/ заглавные/​ получить проблемы.​ как водится, велось​ использованием подстановочного знака​ произвольный поясняющий текст,​ К сожалению, в​

​ т.е. без учета​ функция БИЗВЛЕЧЬ() возвращает​представляет собой диапазон​ вычисления A2/B2, если​У вас есть предложения​​ с уже имеющейся​​Функция ЕСЛИОШИБКА появилась в​

​=ЕСЛИ(ЕОШ(A1);»ОШИБКА!»;A1) или =ЕСЛИ(ЕОШ(A1/A2);»ОШИБКА!»;A1/A2)​

​ЕНД (ISNA)​ вводе, а из-за​ текстового формата в​​ строчные/ числа?​Как видно из рисунка​ в текстовом формате​ ?.​

​ например, «ФРУКТЫ с​ EXCEL нет функции,​​ регистра). Если написать​​ значение ошибки #ЧИСЛО!​

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

​ временного отсутствия данных​ числовой. Для проверки​Опечатки в текстовых значениях​ выше, в списке​ (WORD), причем разными​В качестве условия можно​ учетом регистра» (заголовок​ умеющей различать эти​ условие не в​Предположим, что в диапазоне​ связанными логически, т.е.​ строку Ошибка в​ версии Excel? Если​

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

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

​ ошибки: для ЕОШ(),​ виде =»=Мясо», а​A10:С15​

​ таблицу. Верхняя строка​ расчете.​ да, ознакомьтесь с​ функцию ЕСЛИОШИБКА:​ ЕОШИБКА и ЕОШ,​А1​

​ЕСЛИОШИБКА​ «с запасом» на​ формулу:​ нахождения опечаток типа​ из Списка №2,​ свода данных из​А3​ заголовки исходной таблицы).​ ЕОШИБКА(), ЕСЛИОШИБКА() эти​

excel2.ru

Сравнение реальных списков в MS EXCEL

​ просто ввести в​имеется таблица продаж​ таблицы должна содержать​В отличие от функции​ темами на портале​=ЕСЛИОШИБКА(ЕСЛИ(E2​ так как не​ошибки, формулой выводится​, но они только​ избыточные ячейки. Классический​=ЕЧИСЛО(B3)​ «паладий» (пропущена одна​ присутствуют повторы, а​

Задача

​ накладных, получился длинный​формулу =»=?????»​Записать формулу можно так​ ошибки неразличимы. Т.е.​ ячейку значение Мясо,​ (​ заголовки всех столбцов.​ ЕСЛИОШИБКА() функция ЕОШИБКА()​

​ пользовательских предложений для​Это означает, что ЕСЛИ​ требует избыточности при​ слово ОШИБКА!​ проверяют наличие ошибок​ пример — ошибка​Подобные ошибки часто появляются​ буква л), естественно,​ вместо значений масс​ перечень повторяющихся значений​Формула =БИЗВЛЕЧЬ(B10:B15;B10;A2:A3) вернет ошибку​ =БИЗВЛЕЧЬ(A10:A15;A10;C2:C3) Результат -​ пользователю нужно помнить​ то условию будут​База_данных​ В​ не умеет самостоятельно​

​ Excel.​ в результате вычисления​ построении формулы. При​ЕСЛИ — одна из самых​ и не умеют​ деления на ноль​ при работе с​ нет.​

Решение

​ отображается ошибка.​ (один металл мог​

• ​ #ЧИСЛО! (т.к. таких​ слово ФРУКТЫ (значит​ какой вид ошибки​ удовлетворять текстовые строки,​), содержащая поля (столбцы)​Базе_данных​
• ​ обрабатывать ошибку -​Функция ЕСЛИОШИБКА(), английский вариант​ какой-либо части исходной​ использовании функций ЕОШИБКА​ универсальных и популярных​ заменять их на​
• ​ при вычислении среднего:​ датами. Значение даты​Можно посоветовать скопировать​
• ​Причина — наличие опечаток,​ приходить несколько раз)​ фамилий несколько).​

​ такое слово есть​ возвращается в каждом​

​ которые​ Товар, Продавец и​строки называются записями,​ приходится задействовать функцию​ IFERROR(), проверяет выражение​ формулы возвращается ошибка,​ и ЕОШ формула​

​ функций в Excel,​ что-то еще. Поэтому​Причем заметьте, что итоги​ 05.11.2009 запросто может​ список в WORD,​ которые приводят к​ с соответствующими числовыми​

​В этом случае гораздо​ в диапазоне​ случае и принимать​содержат​ Продажи (см. файл​

Типичные ошибки в списках

​ а столбцы —​
​ ЕСЛИ():​ на равенство значениям​ выводится значение 0,​ вычисляется дважды: сначала​ которая часто используется​ приходилось использовать их​ в нашей таблице​ быть сохранено как​ пусть он подчеркнет​ появлению похожих значений.​ значениями (массами). Это​

​ предпочтительнее выглядит формула​

​А10:А15​ в зависимости от​слово Мясо, например,​ примера).​ полями.​

​=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)​ #Н/Д, #ЗНАЧ!, #ССЫЛКА!,​
​ а в противном​ проверяется наличие ошибок,​ в одной формуле​ обязательно в связке​ тоже уже не​ 2009-ноября-5 или как​ ошибки. А можно​ Разберем типичные ошибки​ Список №2 (см.​ =ВПР(«?????»;B11:B15;1;ЛОЖЬ), по тем​и оно единственное).​
​ этого решение -​
​ «Свежее МЯСО», «Мясо​

​Сформулируем задачи в виде​Поле​Т.к. функция ЕСЛИОШИБКА() впервые​ #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?​ случае возвращается результат​

​ а затем возвращается​
​ несколько раз (иногда​ с функцией проверки​ считаются — одна​ 2009,11,5: см. статью​ внимательно посмотреть список​
​ подробнее.​ Файл примера).​ же причинам, что​Альтернативная формула: =ЕСЛИ(СУММПРОИЗВ(—СОВПАД(«ФРУКТЫ»;A11:A15));»ФРУКТЫ»;»Нет»)​ это не удобно.​ барана» и пр.)​ вопросов.​  — заголовок столбца, из​ появилась в EXCEL​ или #ПУСТО! Если​ выражения ЕСЛИ. Некоторые​

​ результат. При использовании​ в сочетании с​
​ЕСЛИ (IF)​ ошибка начинает порождать​ Является ли значение​ уникальных значений: наверняка​Лишние пробелы​Теперь необходимо оценить результаты​ и в предыдущей​

​Вопрос 3 (Есть ли​Таким образом, функция БИЗВЛЕЧЬ()​Предполагаем, что​
​Вопрос1 (Продал ли Мясо​

​ которого выводится значение,​ 2007, то в​ проверяемое выражение или​ пользователи при создании​ функции ЕСЛИОШИБКА формула​ другими функциями). К​, создавая вложенные конструкции​ другие, передаваясь по​ датой?​

​ слова с опечатками​
​Текстовые значения «медь»​ инвентаризации: если все​ задаче.​ продавец с фамилией​ НЕ возвращает ошибку​база_данных​ Продавец Белов? Если​ если выполняется условие.​ более ранних версиях​ значение в ячейке​ формул изначально реализуют​ вычисляется только один​
​ сожалению, из-за сложности​

​ типа:​ цепочке от одной​После исправления ошибок можно​ расположены где-то рядом​ и «медь »​ в порядке, то​Вывод: забудьте про функцию​ начинающейся на Ро?).​ только в случае,​

Сравнение списков

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

​ списки №1 и​ БИЗВЛЕЧЬ(), если только​Выполним поиск в​ если удовлетворяющая критерию​ в​ сколько?).​

​Поле​ ЕОШИБКА().​ функция возвращает определенное​ делать это не​

excel2.ru

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

​Конструкция =ЕСЛИОШИБКА(Формула;0) гораздо лучше​ ЕСЛИ легко столкнуться​
​ работает и сложнее​

​ другой. Так что​ списков. Значения масс​ опечатались в первой​ в EXCEL считаются​ №2 должны совпадать​ Вы не на​ столбце Продавец с​ запись единственная. Более​A10:C15С10​Найдем строку, в​можно заполнить введя:​Обратите внимание, что использована​ для этого случая​ рекомендуется, так как​ конструкции =ЕСЛИ(ЕОШИБКА(Формула;0;Формула)).​ с ошибкой #ЗНАЧ!.​ для понимания, так​

​ из-за одной ошибочной​ для каждого металла​ букве).​ совершенно справедливо разными​ и по наименованиям​ 100% уверены в​ использованием подстановочного знака​ логичным решением выглядит​– это ссылка​ которой в столбце​текст, представляющий собой название​ именно ЕОШИБКА(), а​ значение, в противном​

​ обработчик подавляет возможные​Если синтаксис функции составлен​ Обычно ее можно​ что лучше использовать​ ячейки, в конце​ из исправленного Списка​Перепутан разделитель целой и​ значениями. Для удаления​ металлов, и по​ том, что в​ *.​ формула =СУММПРОИЗВ((A11:A15=»Мясо»)*((B11:B15)=»Белов»)*C11:C15), которая​ на заголовок столбца,​

​ Товар содержится значение​

​ одного из заголовков​

​ не ЕОШ(), т.к.​ случае — результат​ ошибки и вы​

​ неправильно, она может​ подавить, добавив в​

​ новую функцию ЕСЛИОШИБКА,​ концов, может перестать​ №2 (список находится​ дробной части​ лишних пробелов существует​ массам каждого металла.​​ таблице гарантировано имеются​​В качестве условия можно​​ возвращает ошибку только​​ из которого выводится​ Мясо, а столбце​​Базы_данных​​ для последней ошибка​ вычисления выражения или​ не будете знать,​ вернуть ошибку #ЗНАЧ!.​ формулу функции для​ если это возможно.​ работать весь расчет.​ в файле примера​​Довольно частая ошибка:​​ функция СЖПРОБЕЛЫ(). Для​Чтобы сравнить списки сделаем​

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

planetaexcel.ru

​ #Н/Д ошибкой не​

мы найдем, на какую
сумму Джен продала блеск для губ.

Рис.
4-2
Примеры применения функций баз данных

Эту
же формулу также можно ввести в виде

Бдсумм(данные;«Сумма»;о4:р5).

Как
видно из рис 4-2, Джен продала блеск для
губ на сумму $5461,61.

ПРИМЕР
2. Какое
среднее количество губной помады продает
Джен каждый раз в Восточном регионе?

Можно
вычислить это значение, введя в ячейку
N8
формулу

Дсрзнач(данные;4;о7:q)8)

Используя
4 в качестве значения параметра поля,
мы указываем столбец Единицы,
и диапазон критериев O7:Q8
задает строки базы данных, в которых
Имя
— Джен,
значение поля Продукт
— губная по­мада
и значение поля Местоположение
— восток.

Применение
функции ДСРЗНАЧ гарантирует нам, что
мы вычисляем среднее количе­ство
проданной продукции для отмеченных
строк. Как видно из рис. 4-2, в сред­нем
Джен продавала 42,25
единицы губной помады за одну транзакцию
в Вос­точном регионе.

ПРИМЕР
3. Какую
сумму составляют продажи Эмили и продажи
в Восточном регионе?

В
ячейке N11
(рис. 4-2) мы можем вычислить суммарный
объем продаж ($76156,48)
тор­гового агента Эмили или продаж,
произведенных в Восточном регионе,
ис­пользуя формулу

Бдсумм(данные;5;о10:р12).

Критерии
в диапазоне О10:Р12 указывают продажи в
Восточном регионе или
торгового агента Эмили.

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

ПРИМЕР
4. На
какую сумму продали губной помады Колин
и Зарет в Восточном регионе?

Формула
БДСУММ(данные;5;О13:Q15)
в ячейке N14 вычисляет суммарный до­ход
от продажи губной помады торговыми
агентами Колин и Зарет ($1073,20)
в Восточном регионе.

Обратите
внимание, что O14:Q14
содержит критерии, которые отбирают
губную помаду, проданную в Восточном
регионе агентом Колин, a
O15:Q15
— губную помаду, проданную в Восточном
регионе агентом Зарет. Вспомните,
что критерии в разных строках
интерпретируются как ИЛИ.

ПРИМЕР
5.
Сколько продаж губной помады осуществлено
вне Восточного региона?

В
ячейке N17
мы вычисляем общее количество транзакций
по продаже губной помады (164)
вне Восточного региона по формуле

Бсчёт(данные;4;о16:р17).

Используем
функцию БСЧЁТ
для решения этой задачи, потому что нам
нужен такой критерий, чтобы функция
подсчитала число строк, со­держащих
продажи губной помады и регионы, отличные
от Восточного.

Excel
интерпретирует выражение <>восток
в диапазоне критериев, как «не восток».

Так
как функция СЧЕТ
считает числа, мы должны сослаться на
столбец, содержащий числовые значения.
Столбец 4 (Единицы)
содержит чис­ла, поэтому мы указали
его в формуле. Формула
БСЧЁТ(данные;3;О16:Р17)
верну­ла
бы 0, так как третий столбец базы данных
(столбец J рабочего листа) не со­держит
числовых значений.

Конечно
же, корректное значение воз­вратит и
формула

Бсчёта(данные;3;о16:р17),

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

Сравните
с функцией СЧЕТЗ,
которая используется для подсчета
количества непустых ячеек в интервале
или массиве.

ПРИМЕР
6. На
какую сумму продала губной помады Джен
в 2004г.?

Главная
задача в
этом примере указать только продажи,
осуществленные в 2004 г. Включив в одну
строку диапазона критериев ссылку на
поле Дата,
и используя выражения >=1/1/2004
и <1/1/2005,
мы охватываем только про­дажи 2004 г.

Таким
образом, введя в ячейку N19
формулу

БДСУММ(данныe;5;O18:R19),

мы
найдем общую сумму продаж губной помады
торговым агентом Джен ($1690,79)
в период с 01.01.2004 по 01.01.2005.

ПРИМЕР
7. 
Сколько единиц товара продано по цене
не ниже $3,20?

Этот
пример содержит вычисляемый
критерий.
Вычисляе­мый критерий отбирает
строки базы данных на основании того,
истинно или ложно значение вычисляемого
условия для каждой строки. В этом примере
мы хотим отобрать строки, для которых
отношение Сумма/Единицы
>=$3,20.

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

Например,
вы не можете использовать Имя,
Продукт
или другой заголовок из строки 4 этого
листа. Если же вы введете
заголовок столбца, вычисляемый критерий
примет значение ИСТИНА
на основе значений первой строки базы
данных.

Таким
об­разом, для указания строк, для
которых средняя цена выше или равна
$3,20, мы должны ввести =(L5/K5)>=3,2
в диапазон критериев под заголовком,
кото­рый не совпадает с заголовком
столбца. Если первая строка данных не
удо­влетворяет этому условию, вы
увидите значение ЛОЖЬ
в соответствующей ячейке листа, но Excel
продолжит обработку всех строк, для
которых цена за единицу продукции выше
или равна $3,20.

Рис.
4-3
Пример вычисляемого критерия

Введя
в ячейку N22
формулу

БДСУММ(данные;4;О21:О22),

мы
найдем общее количество проданного
това­ра (1127)
для которого цена выше или равна $3,20.
Обратите
внимание, что ячейка О22
содержит
формулу =(L5/K5)>=3,2.

ПРИМЕР
8. 
На какую сумму каждый торговый агент
продал товар каждого вида?

Используем
в этом примере функцию БДСУММ,
диапа­зон критериев которой не содержит
ни столбец Имя,
ни столбец Продукт.

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

Введем
имя любого торгового агента в ячейку
Х26 и наименование про­дукции любого
вида в ячейку Y26 (рис. 4-4). Затем введем
в ячейку Q25 формулу БДСУММ(данные;5;Х25:Y26),
которая подсчитает общий доход от
продаж ка­рандаша для глаз, выполненных
Бетси (рис.4-4).

Далее введем имя
каждого торгового агента в диапазон
ячеек Q26:Q33 и название каждого вида
продукции в диа­пазон R25:V25.

Затем
выделим диапазон с таблицей данных
(Q25:V33) и щел­кнем в меню Данные
команду Таблица
подстановки.
В поле Подставлять
значения по столбцам в
укажем ячейку Y26,
а в поле Подставлять
значения по строкам в —
ячейку X26.

Полученный
результат показан на рис. 4-4.

Рис.
4-4
Совместное использование таблицы
подстановки с функцией БДСУММ

Каждая
запись в таблице под­становки вычисляет
доход, полученный для разных комбинаций
Имя/Про­дукт,
так как имена, указанные в таблице
подстановки, помещаются в ячейку Х26, а
виды продукции — в ячейку Y26.
Например, мы определили, что Эшли продала
губной помады на $3245,44.

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

Полезные
ухищрения, используемые при определении
диапазона критериев

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

Предположим,
заголовок столбца в первой строке
диапа­зона критериев
соответствует столбцу, содержащему
текстовые данные (напри­мер, столбец
Н):

• *Эшли*
  указывает записи, содержащие строку
  Эшли в столбце Н;

• А?Х
  указывает записи столбца Н,
  начинающиеся с символа А и имеющие
  третьим символом X (второй символ может
  быть любым!);

• <>*В*
  указывает записи столбца Н, которые
  не содержат символ В.

Если
заголовок столбца в первой строке
диапа­зона критериев
соответствует столбцу, содержащему
числовые значения (напри­мер, столбец
I) то:

• >100 указывает
  записи столбца I, содержащие значение,
  превышающее 100;

• <>100 указывает
  записи столбца I, содержащие значение,
  не равное 100;

• >=1000 указывает
  записи столбца I, содержащие значение
  не меньше 1000.

ПРИМЕР
9. 
В базе данных, для каждой торговой
транзакции указаны доход, дата и код
товара. Есть ли легкий способ выяснить
доход от транзакции, зная дату и код
товара?

Файл
dget.xls
(рис. 4-5) содержит базу данных, в которой
указаны доход, дата и код товара для
набора транзакций продаж.

Для
определения дохода от транзакции при
известных дате транзак­ции и коде
товара используем функцию БИЗВЛЕЧЬ.

Синтаксис
функции БИЗВЛЕЧЬ
следующий:

БИЗВЛЕЧЬ(база_данных;поле#;критерий).

• Для
  указанных значений база_данных
  (диапазон ячеек) и поле#
  (номер столбца в диапазоне, начиная с
  самого левого) функция БИЗВЛЕЧЬ
  возвращает запись столбца поле#,
  удовлетворяющую критерию.

• Если
  не окажется записи, удовлетворяющей
  критерию, функция БИЗВЛЕЧЬ
  возвращает ошибку #ЗНАЧ
  (#VALUE).

• Если
  критерию удовлетворяет более одной
  записи, функция БИЗВЛЕЧЬ
  возвра­щает ошибку #ЧИСЛО!
  (#NUM!).

Пусть
наша база данных размещена в диапазо­не
ячеек B7:D32
(рис. 4-5). Предположим, что мы хотим узнать
доход, полученный в результате про­дажи
товара с кодом 62426 от 09.01.2006. Если в
указанный день была выполнена лишь одна
транзакция такого вида, то формула
(введенная в ячейку G9)
БИ3BJIEЧЬ(B7:D32;1;G5:H6)
возвратит доход от этой транзакции,
равный $980.

Рис.
4-5
Пример функции БИЗВЛЕЧЬ

Обратите
вни­мание, мы использовали 1
для аргумента поле#,
так как заголовок Доход
разме­щен в первом столбце базы данных.

Диапазон
критериев G5:H6
гарантирует, что мы отберем транзакцию,
содержащую код товара 62426 за 09.01.2006.

Тренинг

(пункт
4.1. Получение
итоговых данных)

1. Какое
   количество блеска для губ продала Зарет
   на протяжении 2004 и 2005 гг. (файл s91_l_3.xls)?

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

3. Сколько
   блеска для губ продала Колин вне
   Западного региона (файл s91_l_3.xls)?

4. Файл
   s91_4.xls
   содержит для выбранных домов следующую
   ин­формацию:

• площадь;

• цена;

• число ванных
  комнат;

• число спальных
  комнат.

Используя эту
информацию, ответьте на следующие
вопросы.

4.1. Какова
средняя цена всех домов, имеющих общее
количество ван­ных и спальных комнат
>= 6?

4.2. Сколько
продается домов с общим количеством
ванных и спальных комнат <=5 и ценой
выше $300000?

4.3. Сколько
домов имеют по крайне мере 3 ванных
комнаты, но суммарное количество ванных
и спальных комнат <=6?

4.4. Какова
наивысшая цена для домов с площадью не
более 3000 квад­ратных футов и общим
количеством ванных и спальных комнат
<=6? (Совет: используйте функцию ДМАКС
для решения этой задачи.)

Соседние файлы в папке КИС_Лаб_4

• #
• #