Этой цели отвечает функция ПОИСК(). Она осуществляет поиск одного текста внутри другого и возвращает номер позиции, где этот текст найден. Обращение к этой функции имеет вид:

ПОИСК(искомый текст; текст для поиска; начальная позиция)

Недостатком этой функции является то, что при отрицательном результате поиска она возвращает код ошибки #ЗНАЧ! Чтобы освободиться от кода ошибки, который не воспринимается другими функциями, надо использовать функцию ЕОШ или ЕОШИБКА.

Функция ЕОШИБКА(значение) проверяет, является ли значение кодом ошибки (#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ИМЯ?, #ПУСТО!) и возвращает значение "истина", если значение является кодом ошибки и "ложь" - в противном случае.

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

ЕОШИБКА (ПОИСК(искомый текст; текст для поиска; начальная позиция))

Будет давать значение ложь, если исходный текст найден, истину - если не найден.

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

НЕ( ЕОШИБКА (ПОИСК ("инженер"; В3;1)))

возвращает значение "истина", если в тексте значения ячейки В3 присутствует слово "инженер". Если в ячейке В3 слова "инженер" нет, то возвращается "ложь".

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

Таким образом, функция ЕСЛИ, помещаемая в ячейку Е3, примет вид:

ЕСЛИ(И(НЕ(ЕОШИБКА(ПОИСК "инженер"; В3; 1))); С3>20000); 1; 0).

Скопируем эту формулу в ячейки Е4:Е15. получим столбец, состоящий из 0 и 1. Теперь остается только подсчитать количество единиц или, что то же самое, подсчитать сумму содержимого ячеек этого диапазона:

СУММ(Е3:Е15).

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

Задание 4. Определить, насколько самый большой доход превышает самый маленький (по формуле).

Порядок работы.

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

МАКС(С3:С15)-МИН(С3:С15).

Контрольные вопросы.

1. Каков синтаксис функции ЕСЛИ()?

2. Для чего нужны формулы массивов?

3. Как выглядит адрес ячейки, расположенной на другом листе?

4. В чем различие абсолютной и относительной адресации?

5. Что вычисляет функция СЧЕТЕСЛИ()?

Лабораторная работа №3

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

Задание 1. Создать базу данных с записями следующей структуры:

В этой базе данных заданы зарплаты по кварталам Годовая З/П - вычисляемое поле - сумма зарплат по кварталам. Заполнить базу данных записями (более 10 штук), в котором повторяются 2 отдела и 3-4 должности.

Порядок работы.

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

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

Задание 2. Просмотреть список с помощью формы.

Порядок выполнения.

Форма - это окно диалога, предназначенное для управления списком. Excel создает форму автоматически, анализируя структуру списка. Чтобы получить форму, надо выделить список как диапазон и выбрать в меню Данные команду Форма (см. рис. 7). В окне формы отображается только одна запись. С помощью кнопки Далее можно поочередно просмотреть все записи списка. С помощью формы можно редактировать все поля списка, за исключением вычисляемых и защищенных полей. При создании формы данных Excel начинает с имен полей и добавляет текстовое поле для каждого значения, доступного для редактирования.

Рис. 6.

Рис. 7.

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

Задание 3. Добавить, удалить несколько записей с помощью формы.

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

1. Открыть окно формы.

2. Нажать кнопку Добавить. Excel создаст новую пустую запись - очистит поля редактирования и выведет на месте индикатора номера записи Новая запись.

3. Заполнить поля новой записи.

4. По окончании нажать кнопку Закрыть.

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

Для удаления записи с помощью формы нужно выполнить следующие шаги:

1. Открыть окно формы данных.

2. Найти запись для удаления.

3. Нажать кнопку Удалить. Excel предупреждает, что запись будет удалена окончательно.

4. Подтвердить удаление. Excel удалит запись.

5. Для возврата в экран листа нажать кнопку Закрыть.

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

Задание 4. С помощью формы вывести данные о сотрудниках старше 35 лет, у которых зарплата за 1 квартал <40000.

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

Для поиска записей нужно:

1. Открыть окно формы.

2. Нажать кнопку Критерии. Excel выводит пустые поля записи и заменяет индикатор номера записи словом Критерии.

3. Зайти в поле, по которому будет выполняться поиск, и ввести критерий. В нашем задании в поле "Возраст" установим критерий <35, а в поле "З/п 1 кв": <40000 (см. рис 8).

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

Задание 5. Используя функции для работы с базой данных и задавая критерии, определить:

a) средний возраст женщин и средний возраст мужчин;

b) количество пенсионеров по возрасту ( у мужчин - начиная с 60 лет, у женщин - с 55 лет);

c) среднюю зарплату секретарей в отделе КПО (за год);

d) количество мужчин в возрасте от 30 до 50 лет.

Рис. 8.

Порядок работы. Для списков в Excel существует набор функций, который носит название "функции баз данных" или Д-функции. Эти функции отличаются тем, что:

- подводят итоги для столбца таблицы;

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

Общий синтаксис функций баз данных:

Д-функция(список, поле, критерий).

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

С помощью мастера функций выбираем функции, относящиеся к разделу "Работа с базой данных" и затем функцию ДСРЗНАЧ(). Как было указано выше Д-функция имеет три аргумента. В качестве первого аргумента выбираем диапазон ячеек, формирующих список.

Рис. 9.

Вторым аргументом Поле задается заголовок столбца в двойных кавычках или число, представляющее номер столбца в списке. Третий аргумент - диапазон, содержащий задаваемые условия. Таким образом, Д-функция будет иметь следующий вид: ДСРЗНАЧ(A1:J11; D1; G21:G22). Аналогично надо поступить при вычислении среднего возраста мужчин, создав нужный критерий. Подсчитаем количество пенсионеров среди мужчин (пункт b)). Для этого нам понадобится критерий следующего вида:

В качестве Д-функции выбираем функцию БСЧЕТ() со следующими параметрами: БСЧЕТ(A1:J11; D1; B13:C14) (см. рис. 10).

Рис.10.

В пункте c) требуется подсчитать зарплату секретарей в отделе КПО (за год). Здесь опять будет двойной критерий, но по полям Должность и Отдел. Чтобы подсчитать среднюю зарплату воспользуемся Д-функцией ДСРЗНАЧ(). В качестве второго параметра Д-функции выступает столбец J, в котором определена годовая зарплата сотрудника (см. рис.11).

Рис. 11.

Подсчитаем количество мужчин в возрасте от 30 до 50 лет (пункт d)). При создании критерия в данной задаче необходимо дважды использовать столбец Возраст, т.к. его значение должно быть >30 и <50. Поскольку определяется количество, то выбираем Д-функцию БСЧЕТ() (см. рис. 12).

Рис. 12.

Таким образом, мы привели несколько примеров работы с таблицей в Excel как с базой данных.

Контрольные вопросы.

1. Что называют списком в Excel?

2. Как получить форму для таблицы?

3. Как называются функции для работы с базой данных?

4. Можно ли при задании критерия выбирать значения нескольких столбцов?

5. Какие Д-функции вы знаете?

Литература

1. Гарнаев А.Ю. Excel, VBA, Internet в экономике и финансах. - СПб.: БХВ-Петербург, 2001. - 816 с.

2. Лавренов С.М. Сборник примеров и задач. - М.: Финансы и статистика, 2001. - 336 с.

3. Попов А.А. Excel: практическое руководство. - М.:ДЕСС КОМ, 2000. - 302 с.

Страницы: 1, 2, 3