Использование логических функций Excel

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

Задание 8

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

2. Для сравнения результатов используйте логическую функцию ЕСЛИ. При верно составленном балансе на экран в ячейке D32 должно выводиться сообщение – «Баланс составлен верно», иначе – «Вы допустили ошибку». Сохраните работу.

3. Воспользуйтесь командой Формат – Условное форматирование и задайте в Условие 1 для значения – равно – «Баланс составлен верно» формат шрифта полужирный зеленый.

4. Нажмите кнопку «А также» и для фразы «Вы допустили ошибку» задайте формат шрифта полужирный красный. Нажмите ОК.

 

 

Самостоятельные задания

Задание 9.

Использование логических и вложенных функций.

10 спортсменов-многоборцев принимают участие в соревнованиях по 5 видам спорта. По каждому виду спорта спортсмен набирает определенное количество очков. Спортсмену присваивается звание мастера, если он набрал в сумме не менее k очков. Сколько спортсменов получило звание мастера? Исходные данные для заполнения таблицы подберите самостоятельно (не менее 8 строк).

Задание 10.

Телефонная компания взимает плату за услуги телефонной связи по следующему тарифу: 370 мин в месяц оплачиваются как абонентская плата, которая составляет 200 монет. За каждую минуту сверх нормы необходимо платить по 2 монеты. Составить ведомость оплаты услуг телефонной связи для 10 жильцов за один месяц.

 

Задание 11.

В сельскохозяйственном кооперативе работают 10 сезонных рабочих. Собирают помидоры. Оплата труда производится по количеству собранных овощей. Дневная норма сбора составляет 150 килограммов. Сбор 1 килограмма помидоров стоит 3 рубля. Сбор каждого килограмма сверх нормы оплачивается в 2 раза дороже. Сколько денег в день получит каждый рабочий за собранный урожай?

 

 

Сводные таблицы

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

Задание 12.

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

1. Организуйте таблицу следующего содержания

 

Вид продукта	Страна	Производство	В отношении к общему кол-ву в %
Рыба	Индия
Рыба	Китай
Рыба	Россия
Моллюски	Китай
Морские водоросли	Китай
Рыба	Япония
Рыба	Индонезия
Ракообразные	Индонезия
Ракообразные	Индия
Ракообразные	Тайланд
Ракообразные	США
Морские водоросли	Япония
Рыба	Тайвань
Моллюски	Южная Корея
Морские водоросли	Филлипины
Рыба	США

 

2. Рассчитайте 4 графу таблицы.

3. С помощью сводных таблиц подведите итоги:

1. по каждому из видов продукта;

2. общее производство морепродуктов по каждой стране;

3. какое кол-во наименований видов продукта производится каждой страной.

4. Сравните полученные результаты с помощью графиков.

Консолидация данных

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

Задание 13.

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

 

Январь		Февраль
Фамилия И.О.	Сделки	Объем		Фамилия И.О.	Объем	Сделки
Иванов И.И.				Сидоров И.Н.
Антонов А.В.				Иванов И.И.
Медведев К.Л.				Николаев А.Н.
Николаев А.Н.

 

Март
Фамилия И.О.	Сделки	Объем
Иванов И.И.
Сидоров И.Н.
Антонов А.В.
Медведев К.Л.

 

Решение.

1. Создайте рабочую книгу. Добавьте лист 4. Переименуйте листы: в «Январь», «Февраль», «Март», «1 квартал». На каждом листе создайте соответствующую таблицу. Обратите внимание, что фамилии в листах идут в полном беспорядке, заголовки столбцов тоже перепутаны (но фамилии всегда в первом столбце!).

 

Консолидация.

2. Перейдите на лист «1 квартал», выделите ячейку А1. Выберите в меню Данные → Консолидация, в появившемся окне в списке «Функция» выберите «Сумма» (можно выбирать и другие функции).

3. Поставьте курсор в поле «Ссылка». На листе «Январь» выделите ячейки необходимые для сводной таблицы, а затем в окне «Консолидация» нажмите кнопку «Добавить». Аналогичным образом выделите и добавьте диапазоны ячеек из листов «Февраль» и «Март». Обратите внимание, что список диапазонов оказался сформированным.

4. Поставьте флажки в полях «Использовать в качестве имен: «подписи верхней строки» и «значения левого столбца». Нажмите ОК.

5. Внимательно посмотрите на полученную таблицу.

 

Установление связей:

1. Вызовите окно «Консолидация» и установите флажок в поле «Создавать связи с исходными данными». Нажмите ОК. В полученной таблице откройте второй уровень структуры.

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

3. Измените количество сделок для сотрудника(ков) на листах «Март» или «Февраль» и посмотрите что изменилось в итоговой таблице. Итоговые данные должны обновляться автоматически.

 

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

Поиск решения

Задание 14.