Методика выполнения работы. 1. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов или меню Файл команда Создать.).

Практическая работа 1

1. Создайте новую рабочую книгу (кнопка Создать на стандартной панели инструментов или меню Файл команда Создать.).

2. Переименуйте текущий рабочий лист (дважды щелкните на яр­лыке текущего рабочего листа и переименуйте его).

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

4. Сохраните созданный вами файл под именем book.xls в своем каталоге (меню Файл команда Сохранить).

5. Создайте таблицу по предложенному образцу (табл. 5.1). Для этого нужно выполнить следующие действия:

в ячейку А1 ввести заголовок таблицы «Экзаменационная ведо­мость»;

в ячейку A3 ввести «№ n/n»j в ячейку ВЗ ввести «Фамилия, имя, отчество»; в ячейку СЗ ввести «№ зачетной книжки»; в ячейку D3 ввести «Оценка»; в ячейку ЕЗ ввести «Фамилия экзаменатора».

Пример выполнения пятого пункта задания.

Таблица 5.1

№ п\п	Фамилия, имя, отчество	№ зачетной книжки	Оценка	Фамилия экзаменатора
	Иванов И. И.			Иващенко И.И.
	Петров В. В.			Иващенко И.И.
	Сидорове. С. И.			Иващенко И.И.
	Федоров Ф. Ф.			Иващенко И.И.
	Фролов Е. Е.			Иващенко И.И.
	Демидов Д. Д.			Иващенко И.И.

6. Отформатируйте ячейки шапки таблицы:

выделите блок ячеек (АЗ:ЕЗ);

выполните из меню Формат команду Ячейки и перейдите ко вкладке Выравнивание,

в диалоговом окне Выравнивание выберите опции: Горизонтальное — по центру; Вертикальное — по верхнему краю; переключатель — Переносить по словам, а по вкладке Шрифт измените начертание букв и размер шрифта).

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

8. Обрамите таблицу (Панель инструментов —> кнопка Обрамление).

9. Присвойте каждому студенту свой порядковый номер, используя маркер заполнения. Для этого:

сделайте текущей первую ячейку столбца «№ п\п» и введите в нее цифру 1;

затем заполните цифрой 2 следующую ячейку этого столбца;

выделите блок, состоящий из двух заполненных ячеек;

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

или выберите команду Правка —> Заполнить —> Прогрессия.

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

11. Скопируйте таблицу на другой рабочий лист при помощи буфе­ра обмена. Для этого следует:

выделить таблицу или диапазон ячеек;

правой клавишей мыши вызвать контекстное меню;

выполнить команду Копировать;

затем перейти на другой лист;

установить курсор в первую ячейку предполагаемой таблицы;

выполнить команду Вставить из контекстного меню.

12. Добавьте в новую таблицу одну строку и один столбец. Для этого нужно:

выделить диапазон ячеек по столбцу;

щелкнуть правой кнопкой мыши и в открывшемся контекстном Меню выбрать команду Добавить ячейки;

то же самое повторить для строки.

13. Внесите в таблицу ряд изменений:

очистите колонку с фамилией экзаменатора; озаглавьте эту колонку «Подпись экзаменатора».

14. Отсортируйте в новой таблице столбцы 2 и 3 по возрастанию (меню Данные команда Сортировка или на Стандартной панели инст­рументов кнопка Сортировать по возрастанию (Сортировать по убыванию)).

15. Распечатайте созданный документ (Файл —> Печать).

Работа № 2. Построение диаграмм

Задание.На основе данных, приведенных в табл. 5.2, постройте не­сколько типов диаграмм, наглядно показывающих итоги сессии.

Таблица 5.2

Средний балл по группе
Группа	Информатика	Математический анализ	История	Экономика
И-123	4,2	3,8	4,5	4,3
И-124	4,4	4,4	4,4	4,2
И-125	3,9			3,9
И-126	4,3	4,4	4,4	4,1
И-127	3,8		3,9	3,9
И-128	3,3	3,9	4,8	3,6
И-129	4,5	4,8		3,9

Практическая работа 2

1. На листе 1 создайте таблицу «Сведения о результатах сдачи сес­сии на факультете», внесите в нее данные.

2. Постройте диаграмму для всех групп и всех предметов на от­дельном листе типа Столбчатая или График. Для этого следует:

выделить всю таблицу;

выполнить команду меню Вставка — Диаграмма;

или воспользоваться кнопкой Мастер диаграмм на стандартной панели инструментов.

3. На третьем шаге построения диаграммы внесите название диа­граммы, обозначения осей, добавьте легенду.

4. Постройте диаграммы и сравните результаты сдачи по предме­там: информатика, математический анализ и экономика:

выделите столбцы «Группа», «Информатика», «Математический анализ» и, удерживая клавишу Ctrl, выделите столбец «Экономика»;

выберите тип диаграммы График.

5. Измените результаты сдачи сессии и проверьте, как это отрази­лось на построенных диаграммах.

6. Отчет о работе представьте в виде диаграмм на отдельных лис­тах рабочей книги.

Работа № 3. Формулы в Excel

Цель работы: создание и использование простых формул в Excel.

Задание № 1. Торговая фирма имеет в своем ассортименте следую­щий товар: телевизоры стоимостью $300, видеомагнитофоны стои­мостью $320, музыкальные центры стоимостью $550, видеокамеры стоимостью $700, видеоплееры стоимостью $198, аудиоплееры стоимостью $40. В январе было продано телевизоров — 10, видео­магнитофонов — 5, музыкальных центров — 6, видеокамер — 2, видеоплееров — 7, аудиоплееров — 4. Используя возможности Excel, найти сумму выручки от продаж в рублях и долларах.

Методика выполнения работы

1. Создайте таблицу, внесите в нее исходные данные задачи.

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

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

4. Подсчитайте сумму выручки от продажи всех видов товаров. Выделить столбец и нажать кнопку Автосумма на стандартной панели инструментов или установить курсор в последнюю ячейку столбца Е в строку «Итого сумма выручки» и воспользоваться кнопкой Встав­ка функции, расположенной также на стандартной панели, в окне Мас­тера функций следует выбрать СУММ из категории Математические.

Табл. 5.3.

А	В	С	D	E	F	G
	Наименование продукции	Цена за ед., долл.	Продано, шт.	Выручка от продажи, долл.	Выручка от продажи, долл.	Курс долл.
	Телевизоры			=C3*D3	=$E3*$G$3	27,1
	Видеомагнитофоны			=C4 + D4	=$E4*$G$3
	Музыкальные центры			=C5*D5	=$E5*$G$3
	Видеокамеры			=C6 + D6	=$E6*$G$3
	Видеоплееры			=C7*D7	=$E7*$G$3
	Аудиоплееры			=C8 + D8	=$E8*$G$3
	Итого сумма выручки			=СУММ(ЕЗ:Е8)	=CYMM(F3:F8)

Практическая работа 3

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

DivR(i) = NA(i)*Div(i);

DN(i)= Div(i);

DF(i) = DivR(i)/CP(i),

где i = [1,N], N— число рассматриваемых эмитентов.

2. На основании исходного документа «Доходность акций по от­дельным дивидендам» рассчитайте следующие значения:

средняя цена продажи акций по всем эмитентам (выделить стол­бец «Цена продажи» без заголовка, вызвать из стандартной панели Мастер функций —> категория Статистическая —> функция = СРЗНАЧ;

максимальная цена продажи акций по всем эмитентам (выде­лить столбец «Цена продажи» без заголовка, вызвать из стандарт­ной панели Мастер функций —> категория Статистическая —> функция = МАКС;

минимальная цена продажи акций (выделить столбец «Цена прода­жи» без заголовка, вызвать из стандартной панели Мастер функций —> категория Статистическая —> функция = МИН;

максимальная фактическая доходность акций по уровню дивиден­дов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций —> категория Статистическая —> функция =МАКС;

минимальная фактическая доходность акций по уровню дивиден­дов (выделить столбец «Фактическая доходность» без заголовка, вызвать Мастер функций —> категория Статистическая —> функция = МАКС;

3. Результаты расчетов оформите в виде табл. 5.5.

Табл. 5.5.

Расчетная величина	Значение
Средняя цена продажи акций
Максимальная цена продажи акций
Минимальная цена продажи акций
Максимальная фактическая доходность акций
Минимальная фактическая доходность акций

4. В исходной таблице отсортируйте записи в порядке возрастания фактической доходности по дивидендам (выделить таблицу без заго­ловков и строки «Среднее значение», выполните команду Сортировка меню Данные).

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

выделить данные таблицы с прилегающей одной строкой заго­ловка;

выполнить команду Фильтр — Автофильтр меню Данные;

в заголовке столбца «Фактическая доходность» нажать кнопку рас­крывающегося списка и выбрать Условие;

в окне пользовательского автофильтра задать условие >«среднее значение».

6. Результаты фильтрации поместите на новый рабочий лист,
включив в него следующие графы:

эмитент;

номинал акции;

цена продажи;

доходность по дивидендам фактическая.

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

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

выделить столбцы «Эмитент», «Номинал акции» и «Цена про­дажи»;

выполнить команду меню Вставка —> Диаграмма —> тип диаграм­мы Гистограмма;

для добавления линейного графика «Фактическая доходность по дивидендам» правой клавишей мыши активизировав Диаграмма —> Исходные данные —> во вкладке Ряд, выбрать кнопку Добавить, в поле Имя ввести название ряда «Доходность», в поле Значение ввести числовой интервал, соответствующий фактической доходности по дивидендам;

на полученной диаграмме курсор мыши установить на столбец соответствующий значению «Доходность», правой клавишей мыши активизировать контекстное меню, выбрать команду Тип диаграммы, где выбрать тип диаграммы — График;

9. Подготовьте результаты расчетов и диаграммы к выводу на печать (меню Файл команда Печать).

Работа № 4. Сортировка данных в списке

Задание.

1. Выполнить сортировку данных табл. 5.6 по возрастанию кода предмета, даты проведения занятия, номера группы.

2. Выполнить сортировку данных табл. 5.6 по возрастанию, используя сочетания признаков: код предмета и номер группы; номер группы и дата проведения занятия, а также сочетание всех трех признаков.

Практическая работа 4

1. Создайте новую рабочую книгу (меню Файл команда Создать) и сохраните ее подименем SORT.XLS в рабочем каталоге (меню Файл команда Сохранить как)

2. Сформируйте таблицу результатов занятий.

3. Отформатируйте шапку таблицы следующим образом: шрифт Times New Roman;

размер шрифта 12 пт., курсив;

выравнивание по горизонтали — По значению;

выравнивание по вертикали — По верхнему краю;

установите ключ «Переносить по словам» (выделить соответст­вующие ячейки и выполнить команду Формат — Ячейки).

4. Выполните сортировку по столбцу «Код предмета», расположив коды предметов по возрастанию. Для этого нужно:

выделить таблицу с одной строкой заголовка;

Табл. 5.6.

А	В	С	D	E	F	G	H
	№ группы	№ зачетной книжки	Код предмета	Таб. № препод.	Вид занятия	Дата	Оценка
			П1	A1	Практика	26.05.03
			П2	A2	Лекция	26.05.03
			П1	A1	Лекция	11.06.03
			П1	A2	Лекция	11.06.03
			П2	A1	Практика	16.05.03
			П2	A3	Лекция	20.05.03
			П1	A1	Лекция	16.05.03
			П1	A3	Лекция	16.05.03
			П1	A2	Лекция	26.05.03
			П2	A1	Лекция	11.06.03
			П1	A2	Практика	20.05.03
			П2	A1	Лекция	26.05.03
			П2	A2	Практика	11.06.03
			П1	A3	Лекция	20.05.03
			П1	A1	Практика	16.05.03
			П2	A2	Лекция	20.05.03

выполнить команду меню Данные —> Сортировка;

в окне Сортировка диапазона в строке Сортировать по «коду предмета».

5. Результат сортировки скопируйте на Лист 2:

выделите всю таблицу, выполнить команду Правка —> Копировать;

затем на Листе 2 установите курсор в ячейку А1 и выполните ко­манду Правка —> Вставить.

6. Переименуйте Лист 2, дав ему имя — Сортировка:

указатель мыши установите на ярлычке Лист 2;

правой клавишей мыши вызовите контекстное меню;

выполните команду Переименовать.

7. Выполните сортировку по столбцу «Дата», расположив данные по возрастанию. Для этого следует:

установить курсор в любую ячейку поля «Дата» и ввести команду Сортировка из меню Данные. При этом должна выделиться вся область списка, а в окне Сортировка Диапазона в строке Сортировать по — столбец G. Если этого не произошло, то предварительно выделите весь вписок, а затем выполните указанную команду.

8. Выполните сортировку по сочетанию признаков «Дата», «№ группы», «Код предмета». Дляэтого следует выделить всю таблицу и в диалоговом окне Сортировка установить:

в строке Сортировать по — поле «Дата» по возрастанию;

в строке Затем — поле «№ группы», по возрастанию;

в следующей строке Затем — поле «Код предмета» по возрастанию;

установите флажок Строка меток столбцов.

Результат сортировки скопировать на Лист 3 и переименовать его в Сортировка 2.

Работа № 5.Фильтрация записей

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

Методика выполнения работы:

1. Создайте новую рабочую книгу с названием «Фильтрация».

2. Скопируйте в новую рабочую книгу таблицу, созданную в работе № 4 (табл. 5.6)

3. Переименуйте Лист 1присвоив ему имя «Автофильтр №1».

4. Чтобы применить Автофильтрацию, установите курсор в область списка и выполните команду Данные —> Фильтр —> Автофильтр.

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

в столбце Таб № препод. нажмите кнопку Фильтр, из списка условий отбора выберите А1;

в столбце Оценка нажмите кнопку Фильтр, из списка условий отбора выберите Условие и в диалоговом окне сформируйте условие отбора >2;

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

6. Результат фильтрации скопируйте на новый лист, присвоив ему имя — «Автофильтр №2»

7. На листе «Автофильтр №1» результат автофильтрации отмените, установив указатель мыши в область списка и выполнив команду Данные —> Фильтр —> Автофильтр.

8. Сформулируйте выборку — для группы 133 получите сведения о сдаче экзамена по предмету П1 на оценки 3 и 4.

9. Результат сохраните на новом листе, присвоив ему имя «Автофильтр №3».

10. Скопируйте исходную таблицу на новый рабочий лист, пере­именовав его в «Форма данных».

11. Установите курсор в область списка и выполните команду Дан­ные —> Форма.

12. В окне Форма данных просмотрите записи списка и внесите не­обходимые изменения по своему усмотрению с помощью кнопок <Предыдущая> и <Следующая>.

13. С помощью кнопки <Создать> добавьте новые записи.

14. В окне Форма данных сформируйте условия отбора записей. Для этого нажмите кнопку <Критерии>, название которой поменяется на <Правка> В пустых строках имен полей списка введите критерии:

в строку Таб. № препод. введите А1;

в строку вид занятия введите Лекция;

в строку оценка введите условие > 2.

15. Просмотрите отобранные записи нажатием на кнопку <Предыдущая> или <Следующая>.

16. По аналогии сформулируйте условия отбора записей, указанные в пункте 8.

Работа № 6. Создание базы данных

Цель работы: создание базы данных средствами Excel. Сортировка данных, выборка по различным критериям, поиск записи. Автома­тическое подведение итогов.

Методика выполнения работы

1. Создайте таблицу по предложенному образцу (табл. 5.7).

Таблица 5.7.

№ п\п

Фамилия

Имя

Отчество

Дата рождения

Адрес

Телефон

Оклад

Налоги

Сумма к выдаче

Город

Улица

Дом

Корпус

Квартира

Профсоюзные

Пенсионные

Подоходные

Для ячеек «Дата рождения» установите формат Дата {Формат— Ячейка — Число).

Для ячеек «Дом», «Квартира» установите числовой формат.

Для ячеек «Телефон» установите формат Номер телефона (Фор­мат —> Ячейка — Дополнительный —> Номер телефона).

Для ячеек «Оклад», «Налоги», «Сумма к выдаче» установите Де­нежный формат.

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

В ячейку столбца «Налоги пенсионные» внесите соответствую­щую формулу.

В ячейку столбца «Налоги подоходные» внесите формулу «12 % от оклада за вычетом минимальной заработной платы и пен­сионного налога». Минимальную заработную плату принять равной 140 руб.

Внесите первую запись. Начиная со второй записи, заполните таблицу, используя команду меню Данные —> Форма. Перед исполь­зованием команды выделите первую запись таблицы и прилегаю­щую к ней строку заголовка. Таблица должна содержать не менее 20 записей.

Выполните сортировку данных по фамилии (Данные —> Сорти­ровка), результат сортировки сохраните на Листе 2.

Отсортируйте исходные данные по возрастанию окладов, ре­зультат сохраните на Листе 3.

Получите список людей, проживающих_? например, по улице Мира (Данные —> Фильтр —> Автофильтр), результат сохраните на Листе 4.

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

Работа № 7. Использование логических функций

Практическая работа 5

1. На новом листе рабочей книги создайте таблицу по образцу табл. 5.8.

2. Заполните данными столбцы: первый, второй, третий и четвертый.

Таблица 5.8

/А В lDlP0Ht / Ша ж № Фам., имя, № зач. Оценка Кол-во Кол-во Кол-во Кол-во Неявка я п/п отчество книжки5 4 3 2 1 Демидов МИ. 119 5 3 Иванов И. П. 120 4 4- Кукушкин В.Л. 121 3 S Орлов А.П. 131 4 4* Петров К.Н 145 5 1 Сидоров В.О. 149 2 Ш Фролов В.А. 156 н/я

3. В шестой, седьмой, восьмой, девятый и десятый столбцы введи­те формулы, для этого воспользуйтесь Мастером функций из панели инструментов Стандартная:

установите курсор в первую ячейку столбца отличных оценок (D2) и активизируйте Мастер функций;

в первом диалоговом окне выберите категорию функции и назва­ние функции;

Категория:Логические функции

Имя функции:ЕСЛИ

щелкните на кнопке <Готово>;

во втором диалоговом окне установите курсор в поле Логическое выражение и щелкните мышью в рабочей области Excel на ячейке D2 (Оценка «5»);

с клавиатуры введите < = 5 >;

в поле Значение_если_истина введите<1>;

в поле Значение_если_ложь введите< 0>;

нажмите кнопку <Готово>;

методом протягивания скопируйте формулу по столбцу «Кол-во 5».

С помощью Мастера функций аналогичным способом введите формулы в столбцы «Кол-во 4», «Кол-во 3» и т. д., изменяя соответственно значение поля Логическое_выражение, соответственно на «D2 = 4», «D2 = 3» и т. д.

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

выделите блок ячеек Е2.Е8 столбца «Количество 5»;

выполните команду меню Вставка —> Имя —> Присвоить;

в диалоговом окне Присвоение имени в строке Имя введите слово Отлично и щелкните на кнопке Добавить;

далее выделите ячейки F2:F8 столбца «Количество 4» и выполните команду Вставка —> Имя —> Присвоить;

в диалоговом окне Присвоение имени в строке Имя введите слово Хорошо;

аналогичные действия выполните с остальными столбцами табл. 8, создав имена блоков ячеек: Удовлетворительно, Неудовлетворитель­но, Неявка.

6. Создайте таблицу Итоги сессии (табл.5.9).

Таблица 5.9.

ИТОГИ СЕССИИ
Количество отличных оценок
Количество хороших оценок
Количество удовлетворительных оценок
Количество неудовлетворительных оценок
Неявки
итого

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

установите курсор в ячейку подсчета количества отличных оценок;

щелкните по кнопке Мастера функций на панели инструментов Стандартная;

в первом диалоговом окне выберите категорию функции Матема­тические, имя функции Сумм и щелкните на кнопке <ОК>;

во втором диалоговом окне установите курсор в строку Число1 и введите команду Вставка —> Имя —> Вставить,

в диалоговом окне Вставка имени выберите имя блока ячеек От­лично и щелкните на кнопке <ОК>;

повторите аналогичные действия для подсчета количества других оценок.

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

Задание №2.Определить, в какой из заданных интервалов попадает зарплата каждого сотрудника НИИ, представленная в табл. 5.10.

Практическая работа 6

Создайте новую рабочую книгу.

Создайте таблицу из восьми столбцов, в которой содержатся сведения о семи сотрудниках НИИ: № п/п, Ф.И.О., ежемесячная зарплата (табл. 5.10).

Создайте таблицу, содержащую четыре интервала числовых значений зарплат: 1000 — 2000, 2000 — 3000, 3000 — 4000, 4000—6000 (табл. 5.11).

Чтобы определить, попадает ли значение зарплаты из столбца С в заданный интервал, нужно использовать логическую функцию ЕСЛИ с заданием сложного условия И (рис. 6.2). Для этого необходи­мо выполнить следующее:

установить курсор в ячейку D2;

щелкнуть на значке Вставка функции Стандартной панели инст­рументов;

в окне Мастера функций выбрать Категорию функции Логические, в окне Вид функции — выбрать функцию ЕСЛИ, нажать кнопку <ОК>;

в адресной строке рабочего окна в раскрывающемся списке вы­брать функцию И;

установить курсор в поле Логическое 1,

на рабочем поле Excel щелкнуть на ячейке С2;

с клавиатуры ввести >;

на рабочем поле Excel щелкнуть на ячейке А10;

установить курсор в поле Логическое 2;

на рабочем поле Excel щелкнуть на ячейке С2;

с клавиатуры ввести <;

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

на рабочем поле Excel щелкнуть на ячейке В10;

не закрывая окно Функции И, щелкнуть на слове Если в адресной сроке рабочего окна — откроется окно функции Если;

в поле Значение _если _истина с клавиатуры ввести <1>;

в поле Значение _если _ложь с клавиатуры ввести <0>; нажать кнопку <ОК>

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

Таблица 5.10.

Табтца № 6 II

/ А | В / Интервалы 10 1нн 1000 2000 11 2ин 2000 3000 12 Зин 3000 4000 13 4ин 4000 6000

5. Формулу из ячейки D2 операцией автозаполнения скопировать по столбцу
D, ссылки на ячейки АЮ и В10 нужно сделать абсолютными.

6. Аналогичным образом введите формулы в столбцы Е, F, G.

7. Для подсчета числа попаданий в каждый интервал выполните
следующие действия:

выделите блок D2:D6;

нажмите кнопку Автосумма на Стандартной панели инструментов;

повторите это действие для каждого столбца.

Значения столбца Проверка получите, используя операцию Автосумма для значений блоков строк D2:G2, D3:G3 и т. д.

Значение ячейки Итого столбца Проверка должно совпадать с количеством сотрудников.

Задания для самостоятельной работы

Задание № 1

Продукцией городского молочного завода являются молоко, кефир и сметана. На производства 1 т молока, кефира и сметаны требуется соответственно 1010, 1020 и 9450 кг молока.

Прибыль от реализации 1 т молока, кефира и сметаны соответст­венно равны 300, 220 и 1360 руб. Было изготовлено молока 123 т, ке­фира 342 т, сметаны 256 т.

Требуется:

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

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

расход молока (сырья);

б) построить диаграмму по расходу сырья для каждого вида изде­лия.

Задание № 2.

На книжную базу поступили 3 наименования книг: словари, книги по кулинарии и пособия по вязанию. Они были распределены по трем магазинам: «Книжный мир», «Дом книги» и «Глобус». В «Книжный мир» поступило словарей — 1400 экземпляров, кулинарных книг — 23650 экземпляров, пособий по вязанию — 1500 экземпляров; в «Дом книги» — 10300 словарей, 22950 кулинарных книг и 1990 пособий по вязанию; в «Глобус» соответственно 9100, 73320 и 2500 экземпляров.

В первом магазине было продано словарей — 8945 экземпляров, кулинарных книг — 19865 экземпляров, пособий по вязанию — 873 экземпляра; во втором магазине было продано словарей — 9300 экзем­пляров, кулинарных книг — 21900 экземпляров, пособий по вязанию ___ 1020 экземпляра; в третьем магазине соответственно было продано 8530, 18100 и 2010 экземпляров. Требуется:

а) при помощи электронной таблицы рассчитать:

общее количество книг каждого наименования поступивших на книжную базу;

процент продажи каждого наименования книг в каждом магазине; количество книг, оставшихся после реализации;

б) построить диаграмму по распределению книг в магазинах.

Задание №1

Кондитерская фабрика для производства трех видов карамели А, В и С использует три вида сырья: сахарный песок, патоку и фруктовое пюре.

Нормы расхода сырья на 1 т карамели соответственно равны (т):

_ Карамель Вид сырья ABC Сахарный песок 0.6 0 5 0.6 Патока 0.2 0.4 0.3 Фруктовое пюре 0.2 0.1 0 1

Общее количество сырья каждого вида, которое может быть исполь­зовано фабрикой, соответственно равно 1500, 900 и 300 тонн. За месяц фабрика изготовила карамели вида А — 820, В — 900, С — 400 (т).

Требуется:

а) при помощи электронной таблицы рассчитать:
расход сырья каждого вида;

количество оставшегося сырья;

количество карамели вида А, на производство которого хватит ос­тавшегося сахара.

б) построить диаграмму по расходу сырья каждого вида для про­изводства карамели А, В, С.

Задание№ 6

фирма «Новый путь» совершила закупку новых автомобилей: ВДЗ-21093, ГАЗ-31029 и ВАЗ-2106. Автомобилей марки ВАЗ-21093 — было закуплено 35 штук по цене 2000$; автомобилей марки ВАЗ-2106 — было закуплено 21 (шт.) по цене 1600$; автомобилей марки рдЗ-31029 — было закуплено 10 (шт.) по цене 2100$.

На машины были поставлена сигнализация и врезаны люки. После чего они были проданы по цене ВАЗ-21093—45000 руб., ВАЗ-2106—39000 руб. и ГАЗ-31029— 8000 руб.

Требуется:

а) при помощи электронной таблицы рассчитать:

сумму затрат на покупку каждой марки автомобилей; общую сумму затрат на покупку всех автомобилей; полученную после продажи машин прибыль;

б) построить диаграмму по объему продаж автомобилей всех марок.

Задание№ 7

Часовой завод изготовил в январе часы вида А— 150 шт., вида В — 230 шт., вида С — 180 шт. В феврале производство продукции вырос­ло: вида А на 5%, вида В на 3%, С на 2 %. В марте рост составил соот­ветственно 1,5; 1,6 и 2%. Затраты на изготовление каждого вида часов составляют А — 85 руб., В — 73 руб., С — 84 руб. Продажная стои­мость каждого вида изделий составляет соответственно 120 руб., 100 руб. и ПО руб.

Требуется:

а) при помощи электронной таблицы рассчитать в рублях и долла­рах:

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

ежемесячные затраты на производство каждого вида изделий;

б) построить диаграмму по прибыли каждого вида изделия.

Задание №9

Производственная единица изготавливает изделия трех видов А, В и С. Затраты на изготовление единицы продукций А, В и С составля­ют 5, 10 и 11 (руб.) соответственно.

Прибыль от реализации одного изделия данного вида соответст­венно равны 10, 14 и 12 (руб.). План производства изделий А—148265, В—543292, С—463012. Было изготовлено А—135672, В—608712, С—456732.

Требуется:

а) при помощи электронной таблицы рассчитать:

прибыль от реализации каждого вида изделий;

общую прибыль;

процент выполнения плана по каждому виду изделия;

б) построить диаграмму, отражающую прибыль от реализации ка­ждого вида изделий;

в) распечатать документ.

Задание № 10

Годовой доход семьи из четырех человек составляет в среднем 150000 денежных единиц. Основные затраты состоят из: коммунальные услуги — 13700 (ден. ед.); плата за телефон — 9600 (ден. ед.); пита­ние — 64000 (ден. ед.); плата за детские учреждения — 5800 (ден. ед.)-Остальная сумма тратится, исходя из нужд и потребностей семьи.

Требуется:

а) представить данные в виде таблицы;

б) рассчитать:

среднемесячный доход семьи;

сумму основных затрат;

оставшуюся сумму;

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

Задание №15

Рассчитайте будущую стоимость облигации номиналом 100 тыс. руб. выпущенной на семь лет, если в первые три года проценты начис­ляются по ставке 17%, а остальные четыре года — по ставке 22% го­довых.

Задание № 16

Определите текущую стоимость обязательных ежемесячных плате­жей размером 120 тыс. руб. в течение четырех лет, если годовая про­центная ставка — 14%.

Задание № 17

Допустим, рассматривается проект стоимости 100 млн. руб.; ожи­дается, что ежемесячные доходы по проекту составят 16, 25, 36, 49 млн. руб. за четыре месяца. Определите чистую текущую стоимость проекта, если годовая норма процента 195.

Задание № 18

Для покупки компании была взята ссуда 97 млн. руб. под 13% годовых. Доходы от приобретения составили 15, 18, 29, 50 млн. руб. за четыре года и были реинвестированы под 15% годовых. Найдите модифицированную внутреннюю скорость оборота инве­стиции.

Задание № 19

Допустим, проект стоимостью 9 млн. руб. будет в течение сле­дующих трех лет приносить доходы — 4,4; 3,2; 5,9 млн. руб. ежегод­но, а на четвертый год предполагается убыток в 1, 6 млн. руб. Оцените целесообразность принятия проекта, если рыночная норма процен­та 13%.

Задание № 20

Рассчитайте, какая сумма окажется на счете, если 27 тыс. руб. по­ложены на 33 года под 13,5% годовых. Проценты начисляются каждые три года.

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

Назначение электронной таблицы.

Как называется документ в программе Excel? Из чего он состоит?

Особенности типового интерфейса табличных процессоров.

Какие типы данных могут содержать электронные таблицы?

Какие данные называют зависимыми, а какие независимыми?

По какому признаку программа определяет, что введенные данные являются не
значением, а формулой?

Что в Excel используется в формулах в качестве операндов?

Что такое формула в электронной таблице и ее типы? Приведите примеры.

Что такое функция в электронной таблице и ее типы? Приведите примеры.

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

Что такое автозаполнение?

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

Как можно «размножить» содержимое ячейки?

Как посмотреть и отредактировать формулу, содержащуюся в ячейке?

Какой тип адресации используется в Excel по умолчанию?

В чем состоит удобство применения относительной и абсолютной адресации при
заполнении формул?

Что такое диапазон, как его выделить?

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

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

Какие способы объединения нескольких исходных электронных таблиц в одну
вам известны?

Какие особенности печати документов в Excel?

Как использовать электронную таблицу для моделирования по типу решения за­
дачи «Что будет, если...».

Как выделить смежные и несмежные блоки ячеек?

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

Что такое консолидация таблиц?

Что такое макросы и для чего они используются?

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

Какие вы знаете типы аргументов функции?

Что такое Мастер функции?

Что такое Мастер диаграмм?

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

Как осуществляется сортировка списков?

Как осуществляется фильтрация списков?

В каких случаях используют структурирование и группировку данных?

Как формируются итоги в списках по заданным критериям?

лектронной таблицы.

Как называется документ в программе Excel? Из чего он состоит?

Особенности типового интерфейса табличных процессоров.

Какие типы данных могут содержать электронные таблицы?

Какие данные называют зависимыми, а какие независимыми?

По какому признаку программа определяет, что введенные данные являются не
значением, а формулой?

Что в Excel используется в формулах в качестве операндов?

Что такое формула в электронной таблице и ее типы? Приведите примеры.

Что такое функция в электронной таблице и ее типы? Приведите примеры.

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

Что такое автозаполнение?

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

Как можно «размножить» содержимое ячейки?

Как посмотреть и отредактировать формулу, содержащуюся в ячейке?

Какой тип адресации используется в Excel по умолчанию?

В чем состоит удобство применения относительной и абсолютной адресации при
заполнении формул?

Что такое диапазон, как его выделить?

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

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

Какие способы объединения нескольких исходных электронных таблиц в одну
вам известны?

Какие особенности печати документов в Excel?

Как использовать электронную таблицу для моделирования по типу решения за­
дачи «Что будет, если...».

Как выделить смежные и несмежные блоки ячеек?

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

Что такое консолидация таблиц?

Что такое макросы и для чего они используются?

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

Какие вы знаете типы аргументов функции?

Что такое Мастер функции?

Что такое Мастер диаграмм?

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

Как осуществляется сортировка списков?

Как осуществляется фильтрация списков?

В каких случаях используют структурирование и группировку данных?

Как формируются итоги в списках по заданным критериям?