МУНИЦИПАЛЬНОЕ БЮДЖЕТНОЕ 

ОБЩЕОРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ

«СРЕДНЯЯ ОБЩЕОБРАЗОВАТЕЛЬНАЯ ШКОЛА №4»

(г. НОВОКУЗНЕЦК)

 

 

 

 

 

 

 

 

 

 

 

 

Новокузнецк, 2014

 

  

Задание 1. «Прод_магазин»  (Создание и форматирование таблиц)

Задание 2. Абсолютная  ссылка         

              (Создание формул, копирование формул, использование функций СУММ, СЧЕТЕСЛИ)

Задание 3 «Поход» 

(Эл. формулы, функция СУММ)

Задание 4. «Прокат машин»  (Создание, форматирование таблиц, эл. формулы, СУММ)

Задание 5. «Бюджет семьи», лист 1

«Расходы

(Форматирование, форматы чисел, функция СУММ) Задание 6 «Вклад»    

(Эл. формулы)

Задание 7 «Ремонт»  

(Эл. формулы) Самостоятельно

Задание 8 «Соревнования»     

(Автозаполнение, функции СУММ, MIN, MAX)

Задание  9 «Числа»  (Использование различных функций) Самостоятельно

Задача 10 «Доход предприятия»        

(Редактирование, эл. формул) Самостоятельно

Задание  11 Абсолютная ссылка. Смешанная ссылка

Задание 12 «Шпаргалка», лист 1 «Раскладка меню»  

(Формулы с абсолютной адресацией)

Задание 13«День рождение»  (Форматирование, формулы с абсолютной адресацией)       

Задание  14 «Бюджет семьи», лист 2 «Доля»  (Формулы с абсолютной адресацией)

Задание 15 «Шпаргалка», лист 2

«Мороженое»  (Формулы с абсолютной адресацией, формат числа) Самостоятельно

Задание 16 «Квитанции», лист 1 «Свет» (Формулы с абсолютной адресацией) Самостоятельно

 

 

Содержание

 

 

Задание17. «Квитанции», лист 2 «Ед.квитанция»

(Форматирование, формулы) Самостоятельно

Задание 18 «Товарный счет» 

(Оформление таблиц, создание шаблона)

Задание 19 «Ариф_прогр»      

(Создание арифметической прогрессии)

Задание 20 «Табл_подстановок», лист1

                   «Шпаргалка»             

(Таблица подстановок, использование прогрессии)

Задание 21 «Бюджет семьи», лист 3 «Бюджет за 6 мес»

(Эл. формулы и построение диаграммы)

Задание 22 «Бюджет семьи», лист 4 «Расходы»        (Построение диаграмм)

Задание 23 «Успеваемость», лист 1 «По годам»      

(Построение диаграммы)

Задание 24 «Успеваемость», лист 2 «Абсол и

Относит успеваемость» (Форматирование, эл. формулы и построение диаграмм)

Задание 25 «Успеваемость», лист 3 «Анализ»          

(Функции ЕСЛИ, СРЗНАЧ)

Задание 26 «Заявки»

               (Эл.формулы, функции Сумма, ЕСЛИ)

Задание 27 «Успеваемость», лист 4 «Отличники» 

(Функции ЕСЛИ, СУММА, СЧЕТЕСЛИ)

Задание 28 «Отчет по тесту»

            (Функции ЕСЛИ, СУММА, МАКС, СРЗНАЧ) Задание 29 Вложенные функции            

(Функции ЕСЛИ, СРЗНАЧ,  СУММ)

Задание 30 «Расчетная квитанция»  (Функции ЕСЛИ, СЕГОДНЯ, Присвоение имени ячейки)

Задание 31 «Успеваемость», лист 5 «Аттестат»

(Вложенные функции, ЕСЛИ, СРЗНАЧ) Задание 32 «Стипендия»    

(Вложенные функции, функции ЕСЛИ, MIN)

Задание 33 «Налоги США»  (Функция СУММ, постоянная ячейка,

диаграммы)

Задание 34 «Метео» 

(Функции СЧЕТЕСЛИ, ЕСЛИ, диаграммы) Самостоятельно

 

 

 

 

 

Задание 35 «Успеваемость», лист 6

«Проходной балл» (Вложенные функции, постоянная ячейка, функции ЕСЛИ, СУММА, И) Задание 36 «Тест 1»  (Вложенные функции: ЕСЛИ, И; постоянная ячейка)

Задание 37 «Тест 2»  (Вложенные функции: ЕСЛИ, И; постоянная ячейка)

Задание 38 «Тест 3»  

(Вложенные функции, функция ЕСЛИ)

Задание 39 «Биоритмы»          (Форматирование, эл. формулы, функция ПИ, график)

Задание 40 «Ремонт», лист 2 «Обои»             (Форматирование, эл. формулы, постоянная ячейка, функция ЦЕЛОЕ)

Задание 41 «Графики функций», лист 1

                     «График 1» f (x)                 x2 3x 2

Задание 42 «Графики функций», лист 2

«График 2»

 

f (x)

5.

при -10 < х < 10 с шагом h = 0,5.

Задание 43 «Графики функций», лист 3

«График 3»

                      f (x)                  1 x            

                                  1          2 x       x²

Задание 44 «Графики функций», лист 4 «График 4»

                                            3               sin( x)                                  

, x 0, f (x) 1 x²

2x² cos² (x), x 0.

Задание 45 «Графики функций», лист 5

«График 5»

1 x ²x² , x 0, 1          x

2x

                      f (x)                   1                       ₂ , x           [0,1)

1        x

2        | 0.5       sin( x) |, x      1

Задание 46 «Графики функций», лист 6

«График 6»

f (x)cos² xcos x

  на

интервале [-7;7] с шагом 0,2

Задание 47 «Графики функций», лист 7 «Уравнение»

Z=x²+y² при x,y  [-1,1

 

 

 

Задание 48 «Графики функций», лист 8

«Уравнение» 

У = Х³ + sin Х - 4е^Х при Х = 1,58.

Задание 49 «Справочник»

(Работа с формой, поиск и замена данных, СЕГОДНЯ, ЦЕЛОЕ, СЧЕТЕСЛИ, фильтр, расширенный фильтр)

Задание 50 «Товаровед»

(Использование вычисления ИТОГОВ через ДАННЫЕ, )

Задание 51 «База данных»       (Эл. формулы, сортировка, фильтр, диаграмма)

Задание 52 «Расчет зарплаты»           

(Работа с базой данных) Самостоятельно

Задание 53 «Склад» 

(Работа с базой данных) Самостоятельно

Задание 54 «Ведомости»        

(Формулы, постоянная ячейка, связь м/у листами, диаграммы) Задание 55 «Амортизация»  (Функция АСЧ)

Задание 56 «Консолидация»

Задание 57 «Конверт»             

(Функция СЦЕПИТЬ)

Задание 58 «Импортирование данных»  (Импортирование таблицы из текстового

документа)

Задание 59 «ФОТО» 

(Оптимизация данных через Поиск решения)

Задание 60 «Хлебный магазин» (Оптимизация данных через Поиск решения)

Задание 61 «Покраска пола»               (Оптимизация данных через Поиск решения)

Задание 62 «Скорость течения»          (Оптимизация данных через Поиск

решения) 

Задание 63 «Анкета» 

                 (Создание Формы) Задание 64 Макросы. 

Создание макроса с помощью кнопок Использование макросов при

                   использовании Формы         

Задачи 65 - 88

Задание 1 Файл «Прод_магазин»

 

Мы будем создавать следующую таблицу:

1)         Если тяжело определить с какой ячейки в первой строке начинается текст ПРИХОД, РАСХОД и ОСТАТОК, начинайте не с первой строки.  Мы начнем создавать шапку в таблице, со второй строки.

a)      Встаньте в ячейку А2, и наберите  №, затем перейдите кнопкой → в ячейку В2 – наберите Отдел. Снова перейдите в ячейку справа, наберите Наименование товара, далее Ед.изм., Цена прихода, Кол-во прихода и т.д. У вас текст не входит в ячейку, пока на это не обращайте внимания.

b)      Выделите все ячейки второй строки А2 : J2. Встаньте в ячейку А2  и не отпуская ЛКМ двигаемся до конца блока J2. 

c)      Теперь зададим перенос по словам в выделенных ячейках: Формат – Ячейки – Выравнивание -

Перенос по словам. 

d)      Продолжайте работать с этим же блоком. Выделение не убирайте.       Сделайте        так,             чтобы каждая            фраза, располагалась в центре своей ячейки. Формат –

Ячейки – Выравнивание – По горизонтали и по вертикали – по центру. 

e)      Фразу Ед.изм. – ячейка D2, переверните. Формат – Ячейки – Выравнивание – Ориентация – 90⁰.

2)         Измените ширину каждого столбика. 

a)      Столбики А, B, C, D – измените по вашему усмотрению. В строке заголовков раздвиньте границы, ↔ . 

b)      Столбики E, F, G, H, I, J – сделайте одного размера. Выделите эти столбики по строке заголовков . Измените любую из внутренних границ столбца (автоматически изменятся все).

3)         Теперь оформите первую строку.

a)      Встаньте в ячейку E1 – напишите ПРИХОД, в ячейке G1 – РАСХОД, в ячейке I1 – ОСТАТОК.

b)      Выделите ячейки E1 : F1 и сделайте фразу в центре выделенного, по кнопке Формат – Ячейки – Выравнивание.

c)      Повторите всѐ тоже самое с ячейками G1 : H1   и   I1 : J1.

4)         Сделайте нумерацию в столбце А. 

a)      Встаньте в ячейку А3 – введите 1, нажмите Enter; в ячейку А4 – введите 2, зафиксируйте ячейку (нажмите Enter).

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

5)         Обрисуйте таблицу.

Выделите всю таблицу: A1 : J8  и задайте границу кнопкой    6) Проверьте, входит ли ваша таблица в лист формата А4.

a)      Файл – Предварительный просмотр или кнопка  . 

b)      Если информация не входит, то либо измените, параметры страницы или переверните лист в Альбомный. Можно здесь же в Предварительном просмотре нажать на кнопку  - вы попадете в Параметры страницы.

c)      Чтобы выйти из этого режима, кнопка .

Задание 2 Файл можно не сохранять. Относительные ячейки. Создание формул. Использование функций.

 

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

                                                                                                                                                                                       СОЗДАНИЕ ФОРМУЛЫ

Начислите всем сотрудникам  двойной оклад.

а) ЛКМ щелкните в той ячейке, где будет формула (D2);

б) поставьте знак   = ;

в) ЛКМ нажмите на адрес ячейки, который будет участвовать в формуле (С2);

г) поставьте математический знак умножить ( * );

д) далее с клавиатуры наберите постоянное число 2;

е) формула закончена, зафиксируйте еѐ (нажмите значок

ввода  в строке формул)

 

КОПИРОВАНИЕ ФОРМУЛЫ

Если в последующих ячейках для расчетов, относительно знака равно используются одни и те же адреса ячеек, то еѐ повторно не вводят, а заполняют соответствующие ячейки протаскиванием маркера заполнения (копирование). 

 

Скопируем формулу расчета двойного оклада  на остальных сотрудников

а) ЛКМ встанем в ячейку с формулой (D2)

б) поймаем маркер заполнения в виде креста (    ) в правом нижнем углу ячейки D2 и тяните вниз до последней ячейки формулы (D5)

в) встаньте ЛКМ в последнюю ячейку D5 и проверьте, те ли ячейки используются в формуле. (= С5 * 2)

 

СОЗДАНИЕ ФОРМУЛЫ С ПОМОЩЬЮ ФУНКЦИИ

Рассчитайте двойной оклад сотрудников

в) запустилась функция суммирования, в скобках отражен блок который будет просуммирован (ячейки с2, с3, с4, с5) они же обведены пунктирной линией

(выделенный блок всегда можно изменить, протащив ЛКМ по _{новому блоку}); 

г) выделите блок D2:D5. Встаньте ЛКМ в ячейку D2,

вид мышки  и перемещайтесь вниз до ячейки D5;

д) у вас получилась формула  =СУММ (D2:D5)

г) зафиксируйте формулу ( или Enter)

 

Подсчитайте, сколько сотрудников имеют оклад  более 1 000 рублей Данную задачу можно решить, используя, мастер функции. а) ЛКМ встаньте в ячейку, где будет результат (С7)

б) в сроке формул выберите

Статистические (описание функций читайте в нижней стоке

НАША ФУНКЦИЯ СЧЁТЕСЛИ

 

 

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

 

1.      Введите  данные в соответствии со следующей таблицей:

 

 

2.      Рассчитайте стоимость товара, используя копирование формул:

ü  в ячейку Е3 введите формулу =C3*D3; 

ü  зафиксируйте результат, нажав на кнопку , в строке формул; ü скопируйте содержимое ячейки Е3 в ячейки  е4, е5, е6. 

Для этого встаньте в ячейку Е3, подведите курсор к правому нижнему краю ячейки (вид мышки черный крестик) и, не отпуская ЛКМ, копируем формулу вниз. Изменяются ссылки при копировании.

3. Рассчитайте общие расходы на поход:

Встаньте в Е8. Нажмите на кнопку Автосумма . Проверьте, тот ли блок  указан в формуле СУММ, если нет, то ЛКМ выделите необходимый блок.

Должно быть. = СУММ(E3:E6)

Задание 4. Файл «Прокат машин»

1.               В          ячейку            А1    введите           текст: Выручка    от    проката     машин

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

Если текст длиннее, чем ширина столбика, то он будет показан как будто в следующих ячейках, если они пусты, но это не так. Проверьте. Встаньте в ячейку В2 – она в строке формул пуста .

2.               В ячейке А2 введите     Лето 2000 год

3.               В ячейке В4 введите Май. 

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

 

5.    Сделаем столбцы B, C, D, E, F – одинаковой ширины: 

 выделяем по названиям столбцов столбцы: B, C, D, E, F и раздвигаем границу любого столбца, внутри таблицы, все столбцы принимают одинаковый размер

6.    Расположим         заголовок        по        центру            таблицы,        необходимо: а) выделить блок ячеек A1:F1

 

Формат - Ячейки – Граница (Рамка) 

Выберите тип линии, затем какие границы обрисовать (внешние, внутренние)

8. Можно сделать цветные таблицу, буквы и заливку у ячеек. 

Примените денежный формат к ячейкам: выделить блок, Формат – ячейки – Число – Денежный формат – рубли.

1.       Посчитаем сумму выручки за май: Встаем в ячейку В8, ставим знак =, щелкаем ЛКМ по ячейке В5 (она отражается в строке формул), ставим знак +, щелкаем ЛКМ по ячейке В6 и + В7, нажимаем Enter или ―галочку‖  в строке формул.  

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

3.       В ячейке G4 наберите Итого.

4.       В ячейке G5 посчитайте сумму по месяцам за первые 10 дней. Это можно сделать Автосуммированием. Для этого встаньте в ячейку G5, нажмите на кнопку Автосумма  и зафиксируйте формулу.

5.       Теперь эту формулу можно скопировать для ячеек G6, G7. 

Задание 5. Файл «Бюджет семьи», лист 1 – «Расходы» Создать таблицу своих расходов в течение недели, аналогичную таблице на  рисунке. Рассчитать итоговое значение по каждой статье расходов и по каждому дню недели.

 

1.      Напишите ПОНЕДЕЛЬНИК, и Автозаполнением заполните ячейки до ВОСКРЕСЕНЬЯ 

     ЛКМ           

 

2.      В ячейках, где прописаны деньги, набирается только число 10 без запятой и копеек, т.к.

копеек нет, рубли тоже не пишутся

			РАСХОДЫ
	понедельник	вторник	среда	четверг	пятница	суббота	воскресенье	Общий итог
Проезд	10 ,00р.		18,00р.		14,00р.	10,00р.	20 ,00р.
буфет	20 ,00р.	15,00р.	18,00р.	23,00р.	16,00р.	34,00р.
Канц.товары	18 ,00р.		16,00р.	22,00р.
Книги						78,00р.	36 ,00р.
Итого:

 

3.      Примените денежный формат к числам,  выделите блок с числами, выберите денежный  на панели инструментов (если формат применился $, то отформатируйте через Формат – Ячейки – Число – Денежный формат– рубли),  и округлены до сотых .

4.      Вычислить итоговое значение по каждой статье расходов (Общий итог блок ячеек I3:I6) и по каждому дню  недели (Итого блок ячеек B7:H7), используя  автосумму   копирование ячеек.

 

Задача 6.  Файл «Вклад» 

Сумма в 200 денежных единиц (д. е.) помещена в банк на 5 лет. Банковская ставка - 6% годовых. Проценты начисляются раз в год. Определить величину вклада в конце срока.

 

1.      В ячейку А1 ввести фразу: Начальная сумма (S₀)

                                     А2                 Срок хранения (n)

                                      А3                Процентная ставка (p)

                                      А4                Величина вклада в конце срока (S_n)

2.      Соответственно в ячейки В1 – 200

В2 – 5

В3 – 0,06

3.      В ячейку В4 формулу = S₀* (1+p)ⁿ

= В1*(1+В3)^В2

 

Для того чтобы написать степень ^– перейдите на ан.язык Shift/6

В результате выполнения описанной выше технологии лист Excel будет иметь вид:

 

 

Задание  7 Файл «Ремонт» 

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

1.        Рассчитайте необходимое количество обоев для каждой отдельной комнаты (ячейки выделены серым цветом)

При подсчете необходимого количества рулонов используйте коэффициент = 0,5 на отходы.

В ячейках F6, G7,    H5,    I4,    J9,    H8     введите     соответствующие     формулы     - 

=(длина + ширина) * 2 * высота / длина рулона * 0,5

2.        Рассчитайте  количество рулонов

В ячейку F10 введите формулу для подсчета общего количества данного вида обоев,  сумма ячеек F4:F9. Данную формулу можно скопировать на остальные виды обоев.

3.        Введите цену за один рулон.

4.        Рассчитайте стоимость.

В ячейку F12 введите   = кол-во рулонов * цену рулона Данную формулу можно скопировать 

5.        Подведите итог: Сколько рулонов вам необходимо на каждую отдельную комнату? В ячейку L4 введите сумму ячеек F4:K4. И скопируйте формулу.

6.        Вставьте рисунки (Вставка – Рисунок – Картинки) и шаблоны обоев (с помощью п.и. Рисование нарисовать прямоугольник, а затем Цвет заливки - Способы заливки - Текстура)

7.        Проверьте, правильно ли вы написали все формулы, для этого, в детскую комнату и в спальню выберите 1 рулон любых обоев, для бордюра. 

У вас должны измениться результаты в 10, 12 строке и в столбце L.

Задание 8 Файл «Соревнования»

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

ü  выступление каждого спортсмена оценивают N судей;

ü  максимальная и минимальная оценки отбрасываются (если их несколько, то отбрасывается по одной такой оценке);

ü  в зачет спортсмену идет среднее арифметическое оставшихся оценок. Требуется определить итоговые оценки всех спортсменов и оценку победителя. 1. В ячейки В1:Е1 введите фамилии спортсменов, а в ячейки А2:А6 – фамилии судей. Если вводить фамилии спортсменов не будете, то можно воспользоваться Автозаполнением– написав только фразу Спортсмен 1 и скопировать вправо , тоже самое сделать с фразой Судья 1

2.      Заполните блок В2:Е6 оценками судей для каждого спортсмена (оценки проставляются по десятибалльной системе).

3.      В ячейку А8 введите текст «Мах оценка», в ячейку А9 - «Min оценка». 

4.      В ячейку В8 введите формулу для нахождения максимального числа, в ячейку В9  - минимального числа. Данные функции можно найти в категории Статистические, воспользовавшись мастером функций  или раскрыв на панели инструментов  кнопку  

5.      Скопируйте содержимое ячейки В8 в ячейки С8:Е8 (обратите внимание, что формула при копировании изменяется).

6.      Скопируйте содержимое ячейки В9 в ячейки С9:Е9 (обратите внимание, что формула при копировании изменяется).

7.      В ячейку А11 введите текст «Оценка спортсмена».

8.      В ячейку В11 введите формулу =(СУММ(В2:В6)-В8-В9)/3.

9.      Скопируйте содержимое ячейки В11 в ячейки С11:Е11 (обратите внимание, что формула при копировании изменяется).

10.  В ячейку А13 введите текст «Оценка победителя».

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

12.  Оформите таблицу

 

Задание  9 Файл «Числа»

Дана последовательность чисел: 25; -61; 0; -82; 18; -11; 0; 30; 15; -31; 0; -58; 22. 

Заполните ячейки К5:К14 соответствующими формулами

 

 

функция - Сегодня). Вы увидите Выделите блок (A1:M1) и объедините ячейки . 

Здесь перечислены все функции которые вам будут необходимы (Категория Статистические СЧЕТ, СЧЕТЕСЛИ, МИН, MАКС, СРЗНАЧ, и Математические СУММ, СУММЕСЛИ). 

Рассмотрим применение функции СЧЕТ для нахождения общего количества чисел:

1.    Встаньте в ячейку K5.

2.    Вызовите мастер функций  и в категории Статистические, выберите функцию СЧЕТ.

3.    Мастер вам предложит посчитать данные, стоящие в диапазоне K3:K4.

4.    Необходимо заменить диапазон на A3:M3. Для этого выделите в таблице эти ячейки, не закрывая окна функции СЧЕТ и нажмите OK.

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

 

Задание 10 Файл «Доход  предприятия»

 

1.          Создайте следующую таблицу:

 

Название	Доход	НДС	Зар. плата	Соц. страх	Чистый доход
Банк	1 000 000 р
Фабрика	990 000 р
Частное предприятие	5 900 р
Завод	7 000 000 р

Здесь НДС – налог на добавленную стоимость, равный 21% от доходов предприятия; заработная плата составляет 30% от доходов предприятия, страховой взнос составляет 37% от заработной платы.

2.          Произведите все необходимые расчеты. Результаты поместите в соответствующие столбцы таблицы.

3.          Подсчитайте чистый доход каждого предприятия по формуле: 

Чистый доход = Доход – НДС – Зар.плата – Соц.страх

4.          Вставьте пустую строку после строки с названиями столбцов.

5.          Введите в нее проценты на отчисления.

6.          Представьте процентные ставки в процентном формате.

7.          Измените доход Частного предприятия с 5900 на 11900. Проверьте пересчет формул.

8.С помощью функций ЭТ определите максимальную сумму чистого дохода среди предприятий. Запишите ее в пустую строку под столбцом «Чистый доход».

9. С помощью ЭТ определите общую сумму налога на добавленную стоимость (НДС) со всех предприятий. Результат запишите в пустую ячейку под столбцом НДС.

 

Задание 12 Файл «Шпаргалка», лист 1 – «Раскладка меню»

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

 

1.    Оформите таблицу. Подготовьте определенную ячейку, в которой будет вводиться количество порций. (С1 = например 5)

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

 

3.    В ячейке С4 введите = В4*C1

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

Встаньте в ячейку С4 и в строке формул  отразилась наша формула. 

ЛКМ щелкните в строке формул  у адреса С1 и нажали на клавишу F4. Ячейка С1 стала постоянной.  

Зафиксируйте формулу и скопируйте. 5. Поизменяйте в ячейке С1 общее количество порций,. Общая раскладка продуктов изменяется.

 

Задание 11 Файл «Шпаргалка», лист 1 – «Раскладка меню»

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

 

1.    Оформите таблицу. Подготовьте определенную ячейку, в которой будет вводиться количество порций. (С1 = например 5)

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

 

3.    В ячейке С4 введите = В4*C1

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

Встаньте в ячейку С4 и в строке формул

 отразилась наша формула. 

ЛКМ щелкните в строке формул  у адреса С1 и нажали на клавишу F4. Ячейка С1 стала постоянной.         

Зафиксируйте формулу и скопируйте.

5.    Поизменяйте в ячейке С1 общее количество порций,. Общая раскладка продуктов изменяется.

 

      Задание 11        Файл можно не сохранять. Абсолютная ссылка  

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

Shift / 4

 

Пример: Найдем долю вклада каждого сотрудника от общей суммы

1.      Введите данные в таблицу (№; Фамилия, Сумма вклада)

2.      В ячейку С8 введите формулу для нахождения общей суммы, через Автосумму.

3.      В   ячейку            D3       введите           формулу         для      нахождения    доли    от        общего       вклада 

= Сумму вклада Абрамова / ИТОГО * 100

=   С3 /  С8  *  100

4.      Если данную формулу будем копировать, то при копировании еѐ вниз должно быть:  Сумму вклада Михайлова / ИТОГО * 100,

               а у вас получается               =   С4 /  С9  *  100

 

Т.к. вы копируете вниз, ячейка С3 (Сумма Абрамова) заменилась на С4 (Сумма Михайлова) (это правильно), 

а ячейка С8 (Итого) опустилась на ячейку С9 (пустую) (по формуле это правильно, но нам этого не надо). 

Для того чтобы в формуле всегда делилось на общий итог: данную ячейку ИТОГ ячейку С8 – необходимо зафиксировать (сделать постоянной). 

F4

Этого можно добиться, если в строке формул , у адреса ячейки С8 щелкнуть ЛКМ  поставить знак постоянства, нажав на клавишу             .

Зафиксируйте формулу.

В формуле появились значки $ ($C$8)- это и есть знак постоянства, данная ячейка зафиксирована и при копировании не будет изменяться.

 

Смешанная ссылка 

используется, когда при копировании формулы может изменяться только какая-то одна часть ссылки – либо буква столбца, либо номер строки. При этом символ $ ставится перед той частью ссылки, которая должна остаться неизменной.

Пример: Смешанные ссылки с неизменяемой буквой столбца: $C8, $F12; смешанные ссылки с неизменяемым номером строки: A$5, F$9.

 

 

 

Задание 13    Файл «День рождение»

Имеется сумма денег S. Приглашено N гостей. Составьте меню не менее чем из трех продуктов, учитывая стоимость и порционное распределение продуктов на каждого гостя.

 

1. Заполните лист приблизительно такими данными:

Данную формулу копировать нельзя. 

Необходимо ячейке В2    (Количество      гостей)      задать     абсолютную     адресацию 

. 

Зафиксируйте результат.

3. Скопируйте формулу на бананы и мороженое. 

 Встаньте в ячейку D5, подведите курсор к правому нижнему углу ячейки, и мышкой в виде черного креста  , протяните вниз. 4. Подсчитайте общий итог в ячейке D9 (используя автосумму).

 

5.  Содержимое ячейки D9 должно быть меньше, чем содержимое ячейки Е2 

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

6.  Отформатируйте таблицу по своему усмотрению (отцентрируйте заголовок, обрисуйте таблицу, примените различное выравнивание в ячейках).

 

Задание 14 Откройте файл «Бюджет семьи», лист 2 – «Доля» Рассчитайте долю каждого раздела от общей суммы затрат за неделю.

 

1.    Введите данные, используя автозаполнение при написании дней недели

                        

2.    Автосуммированием подсчитайте ИТОГ по разделам (транспорт, продукты, одежда, развлечения). Формулу можно копировать. 

3.    Получите результат в ячейке – ВСЕГО за неделю (сумма итого по разделу транспорт, итого по разделу продукты, итого по разделу развлечение, итого по разделу одежда)

4.    Для того чтобы подсчитать ДОЛЮ приходящуюся на транспорт, относительно всех денег потраченных за неделю необходимо, =ИТОГО по разделу транспорт / ВСЕГО за неделю.

5.    При копировании этой формулы вправо на продукты, развлечения и одежду мы результата не получим, т.к. формула относительно знака ―=‖ пишется неодинаково. (!попробуйте!) 

6.    Ячейка ВСЕГО за неделю – должна стоять на месте и не двигаться за ―=‖. Данную ячейку необходимо задержать на месте, используя знак постоянства $ - клавиша F4 – если стоять курсором около адреса этой ячейки. Например, = B9 / $B$10.

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

 

 

Задание 15 Откройте файл «Шпаргалка», лист 2 – «Мороженое»

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

 

1.    При оформлении таблицы примите различные            типы   выравнивания. Стоимость мороженного выражена в рублях (выделите весь блок ячеек, в которых будут отражены деньги (Формат    –          Ячейки           –          Число –

денежный или кнопка . Округлите деньги до сотых – кнопки

2.    При создании формулы не забудьте использовать абсолютные ссылки. (= Цена одного мороженного * на количество штук. =В2*$С$1). Скопируйте вниз. Напишите формулу для покупки 3 штук и т.д.

 

Задание 16 Файл «Квитанции», лист 1 – «Свет»

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

 

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

К некоторым заголовкам столбцов примените возможность «Перенос по словам», все заголовки центрируйте по горизонтали и вертикали.

 

 

Задание 17 Откройте файл «Квитанции», лист 2 – «Ед.квитанция»

 

Подготовьте ЕДИНУЮ КВИТАНЦИЮ. 

Заполните еѐ своими данными, произведите подсчет необходимых платежей  за 2

месяца.

 

 

 

Пример: В ячейке H9 будет формула = Е9 * K14

 H10 = Е10 * $F$4  и данную формулу можно скопировать для холодной воды

 

Задание 18 Файл «Товарный счет»

Создайте и заполните бланк товарного счета. Выполнение лучше всего разбить на следующие этапы: 

1.  Создание таблицы бланка счета.

2.  Оформление бланка.

З. Сохранение шаблона счета.

4. Заполнение таблицы.

 

l-й этап:

Основная задача уместить таблицу по ширине листа. Для этого:

ü  предварительно установите поля, размер и ориентацию бумаги ([Файл - Параметры страницы...]),

ü  выполнив команду [Сервис - Параметры...], в группе переключателей Параметры окна (вкладка Вид) активизируйте переключатель Авторазбиение на страницы.

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

ü  Создайте таблицу по предлагаемому образцу.

Грузоотправитель и адрес Грузополучатель и адрес Дата получения "__"_______ 200_г. К Реестру № Счет № ___ от __.__.__г. Поставщик Торговый Дом Пресненский Адрес 123456, Москва, Рочдельская ул., 4 Дополнения:
№	Наименование	Ед.измерения	Кол-во	Цена	Сумма
1
2
3
4
5
6
Итого
Руководитель предприятия Чижов Е.Ю. Главный бухгалтер        Стасова А.И.

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

ü  Установите денежный формат числа в тех ячейках, в которых будут размещены суммы. Для этого выполните команду [Формат - Ячейки...], выбрав вкладку Число и выбрать категорию

Денежный. Не забудьте установить число знаков после запятой для отображения копеек ü Введите формулы для подсчета суммы.

ü  Разлинуйте таблицу используя линии разной толщины (по образцу).

ü  На этом этапе желательно выполнить команду [Файл - Предварительный просмотр], чтобы убедиться, что таблица целиком вмещается на листе по ширине и все линии обрамления на нужном месте.

 

2-й этап:

1. Для оформления счета (информация выше таблицы) - вставьте дополнительные строки перед таблицей.

ü Для этого выделите несколько первых строк таблицы и выполните команду [Вставка – Строки). Вставиться столько же строк, сколько вы выделили.

2.  Наберите необходимый текст до и после таблицы. Следите за выравниванием.

Обратите внимание, что текст «Дата получения «__»______200_г.» и фамилии руководителей предприятия внесены в тот же столбец, в котором находится столбик таблицы «Сумма» (самый правый столбец таблички), только применено выравнивание вправо.

3.  Текст «Счет №» внесен в объединенные ячейки по ширине таблицы, и применено выравнивание по центру.

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

5.  Выполните просмотр.

 

3-й этап:

ü   Чтобы в дальнейшем использовать подготовленный рабочий лист в качестве шаблона, желательно оставить в рабочей книге только один лист. Затем выполним команду меню [Файл – Сохранить как). В списке Каталоги выберите каталог XLSTART или альтернативный стартовый каталог. Введите имя данному шаблону (ИМЯ файла) и в нижней части диалога (Тип файла) из списка выберите пункт Шаблон.

ü   Нажмите сохранить.

ü   Закройте файл.

 

 

4-й этап:

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

ü   В меню Файл выберите Создать.

ü   В списке Создать диалогового окна <Создать> выделите шаблон, на основе которого хотите создать новую рабочую книгу.

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

ü   Заполните столбцы «Наименование», «Ед.измерения», «Кол-во» и «Цена» по своему усмотрению. 

ü   Отсортируйте записи по алфавиту.

Для этого выделите все строки таблицы, кроме первой (заголовка) и последнего (Итого), можно не выделять и нумерацию. Выполните команду [Данные – Сортировка], выберите столбец,            по             которому        нужно             отсортировать           данные            (Наименование),       и             установите переключатель в положение «По возрастанию».

ü   Выполните просмотр Файл – Предварительный просмотр

Задание 19 Файл «Ариф_прогр»

Подсчитайте сумму первых 10 членов арифметической прогрессии, общий член которой  а_n = 3 + 2_n.

 

1.    В ячейки А1 и А2 введите названия строк: «n» и «a_n»,  в ячейку А4 – название «СУММА».

2.    В ячейки В1 и С1 введите числа О и 1. 

3.    Выделите блок В1:С1 и Автозаполнением заполните до числа 9

4.    В ячейку В2 введите число 3 (т.к. у нас а_n = 3 + 2_n), нажмите клавишу Enter.

5.    Установите курсор в ячейку В2 и с помощью команды Правка – Заполнить – Прогрессия заполните вторую строку элементами арифметической прогрессии с шагом 2 до предельного значения 21.

6.    Установите курсор в ячейку В4, щелкните по пиктограмме Автосуммирование, выделите блок В2:К2, нажмите клавишу Enter.

Задание 20 Файл «Табл_подстановок», лист 1 – «Шпаргалка»

У продавца сломался калькулятор. Для облегчения расчетов с покупателями составьте для него «шпаргалку» - таблицу стоимости товара от 200 г до 1 кг включительно с интервалом в 100 г.

1.               Введите исходные данные: в столбец А – наименование товара, в столбец В – стоимость 1 кг товара (строку 1 оставьте свободной):

2.               В           ячейки            C1:G1 с          помощью        авто     заполнения     занесите         числа 

0,2; …; 1 следующим образом:

ü  в ячейку С1 введите число 0,2;

ü  выбрав команду Правка – Заполнить – Прогрессия, установите следующие параметры: Прогрессия – по строкам; Тип – арифметическая; Шаг – 0,1; Предельное значение – 1; ü закройте диалоговое окно, щелкнув на кнопке ОК.

3.    В ячейку В1 введите формулу =А10*В10 (адреса ячеек могут быть произвольными, но не входящими в область заполнения).

4.    Выделите блок Bl:K5.

5.    Выбрав команду Данные – Таблица Подстановки, установите следующие параметры: Подставлять значения по столбцам в – А10; Подставлять значения по строкам в – В10 (подставляемые адреса видны в строке формул).

6.    Используйте формат Денежный для ячеек B2:K5 (предварительно выделите указанный блок).

 

Задание 21 Откройте файл «Бюджет семьи», лист 3 – «Бюджет за 6 месяцев»

Заполните таблицу доходов и расходов на пол года.

2.  Просчитайте сальдо (разница между доходами и расходами). Формула начинается со знака = В8 – В15, формулу можно скопировать на остальные месяцы.

 

3.  Постройте график доходов. 

а) Для этого выделите месяца и доходы. Выделяем блок A2:H2; затем, удерживая клавишу  Ctrl  , выделяем второй блок A8:H8.

б) Нажмите кнопку Мастер диаграмм  или

Вставка – Диаграмма

              в)     Выберите      тип      диаграммы      и      вид

(Гистограмма, обычная). Далее.

г) На 2-ом шаге из 4-х в Диапазоне данных:

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

д) На 3-ем шаге из 4-х окно с 6 папками. Мы сделаем изменения только в папках: Заголовки

– название диаграммы и ось Y – руб. Легенда – скинуть галочку с Добавить легенду.

Подписи данных – значения (это делать не обязательно). Далее.

е) Расположить на имеющемся листе.

4.    Если необходимо сделать изменения в диаграмме: ПКМ по изменяемой области: Формат области.

5.    Постойте еще один график, в котором будут отражаться доходы, расходы и Сальдо по месяцам. 

а) Для этого выделите следующие ячейки: месяцы, доходы, расходы, сальдо.      (А2:Н2;            А8:Н8;             А15:Н15; А16:Н16). Блоки ячеек выделяются, удерживая клавишу Ctrl.

б) Тип диаграммы ГРАФИК

в) Название диаграммы: Отчѐт. 

г) По оси Y –  рубли.

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

6.    Сделайте предварительный просмотр и исправьте недостатки.

 

Задание 22 Откройте файл «Бюджет семьи», лист 4 -«Расходы»

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

РАСХОДЫ

	понедельник	вторник	среда	четверг	пятница	суббота	воскресенье	Общий итог
Проезд	10 ,00р.		18 ,00р.		14 ,00р.	10,00р.	20 ,00р.	72,00р.
буфет	20 ,00р.	15 ,00р.	18 ,00р.	23 ,00р.	16 ,00р.	34,00р.		126,00р.
Канц.товары	18 ,00р.		16 ,00р.	22 ,00р.				56,00р.
Книги						78,00р.	36 ,00р.	114,00р.
Итого:	48,00р.	15 ,00р.	52 ,00р.	45 ,00р.	30 ,00р.	122,00              р.	56 ,00р.

1.  Для выделения двух несмежных диапазонов ячеек удерживать нажатой  клавишу 

Ctrl. 

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

Расходы по статьям расходов 1 4 0 ,00р.            1 2 0 ,00р. 1 0 0 ,00р. 8 0 , 00р. 6 0 , 00р. 4 0 , 00р. 2 0 , 00р.          -   р . 126,00 1 1 1 4 ,00р. Проезд буфет Канц.товары Книги

 

Задание 23 Файл «Успеваемость», лист 1 – «По годам» Представьте в наглядном виде следующие показатели успеваемости:

 

 

 

При размещении данных (года) целесообразно воспользоваться возможностью Excel по автозаполнению.

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

Важным этапом построения гистограммы является подпись данных по оси абсцисс. Для этого необходимо во втором шаге построения диаграмм выбрать папку Ряд; затем в поле Подписи по оси Х – выделить ячейки, в которых прописаны года.

 

        

Задание 24 Откройте файл «Успеваемость», лист 2 – «Абсолютная и относительная успеваемость»

4. Создайте таблицу по образцу

 

Успеваемость 8 "А"

Предмет	"5"	"4"	"3"	"2"		Абсолютная успеваемость (в %)	Качественная успеваемость (в %)	Уровень обученности
Химия	2	8	11	1
Физика	3	8	8	1
Математика	4	7	7	0
Биология	3	8	10	1
География	8	9	4	0
Информатика	5	9	8	0

 

2.    Используя формулы, вычислить Кол-во учащихся, Абсолютную успеваемость, Качественную успеваемость, и Уровень обученности класса.

Абсолютная успеваемость – класса считается по формуле:

[(кол-во уч-ся, у которых ‖3‖) + (кол-во уч-ся, у которых ‖4‖) + (кол-во уч-ся, у которых ‖5‖] * 100 / (кол-во уч-ся в классе)

 

Качественная успеваемость – класса считается по формуле:

[(кол-во уч-ся, у которых ‖4‖) + (кол-во уч-ся, у которых ‖5‖)] * 100 / (кол-во уч-ся в классе)

 

Уровень обученности – класса считается по формуле:

[(кол-во уч-ся, у которых ‖2‖) * 2 + (кол-во уч-ся, у которых ‖3‖) * 3 + (кол-во уч-ся, у которых ‖4‖) * 4 + (кол-во уч-ся, у которых ‖5‖) * 5  / (кол-во уч-ся в классе)

 

3.    Постройте гистограмму «Качественная успеваемость», круговая диаграмма – «Абсолютная успеваемость, график – «Уровень обученности» по следующему образцу:

 

 

Задание 25 Откройте файл «Успеваемость», лист 3 – «Анализ»

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

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

 

Введите  в ячейку А2 фамилию ученика – Петров; 

в ячейку В2 фамилию ученика – Иванов. 

В блок ячеек А4:А13 – оценки из табеля успеваемости Петрова, а в блок ячеек В4:В13 – ученика Иванова.

 

1.    Сначала подсчитаем средний балл Петрова. Результат получим в ячейке А14. 

Запустим функцию среднего значения (открыв кнопку автосуммы и выбрав среднее значение или через мастер функций).

В запросе выделите блок ячеек, который участвует в подсчете среднего значения, это блок А4:А13.

(В ячейке А14 будет записана формула =СРЗНАЧ(А4:А13)

2.    Формулу, которую вы получили для подсчета среднего балла Петрова, можно скопировать и для Иванова. 

  У        вас           должно получится              в           ячейке         В14     следующее

=СРЗНАЧ(В4:В13). Встаньте в эту ячейку и проверьте.

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

  Для этого встаньте в эту ячейку, зайдите в Мастер функции, выберите – Категорию: Логические, Функция: ЕСЛИ, 

  В запросе – в первую строку вводится условие, которое будем проверять: А14>=В14,  во второй строке, если это условие ИСТИНА – что хотим увидеть, т.е. фраза - 

«Успеваемость Петрова выше»,

в третьей строке, что должно выйти, если условие ЛОЖЬ. «Успеваемость Иванова выше или такая же».

 

 

 

Общий вид формулы: = 

ЕСЛИ (лог_выражение;значение_если_истина;значение_если_ложь)

 

Вы можете поменять оценки ученикам, результат в ячейка  А14, В14, В16 изменяется.

 

Задание 26  Файл с именем «Заявки»  

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

 

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

 

Е2     –           общая            суммы   поступлений,      Е4      –           Е11                –           стоимость     покупки, 

Е12 – Всего рублей за весь товар,  Е13 – можно ли приобрести весь список товара (денег хватает – можно, иначе – нельзя).

 

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

 

 

3.  Примените денежный формат к необходимым ячейкам (B2:E2; D4:E11).

4.  Введите сумму, которую школа заработала ячейка В2. Сколько денег выделено школе – С2. Внесите спонсорскую помощь – D2. 5. Сделай необходимые расчеты.

 

Если вы введете: Заработано – 500р., Выделено 500р., Спонсоры – 1000р., то вам денег не хватит. Если увеличить сумму заработанных денег до 1000р., то денег будет достаточно. Проверьте.

 

Задание 27 Откройте файл «Успеваемость», лист 4 – «Отличники»

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

 

Фаимлия	Оценка по информатике	5	4
Яковлева	5
Хорзов	4
Степанов	3
Смирнов	3
Петров	5
Морозова	5
Коржавина	4
Иванов	3
Демидова	3
Володин	5
	Кол-во "5"
	Кол-во "4"
	Кол-во "3"

1.  Введите в ячейки А1:D1 названия столбцов:

«Фамилия», «Оценка по информатике», «5», «4».

2.  Введите в ячейки А2:В11 исходные данные (для 10 человек).

3.  Введите в ячейку С2 формулу, вызовите Мастер формул, категория Логические, функция ЕСЛИ,  заполните диалоговое окно:  =ЕСЛИ (В2=5; при

 

4.  Скопируйте формулу ячейки С2 в ячейки С3:С11.

5.  В ячейке С12 просуммируйте столбик полученных чисел.

6.  Все то же самое повторите, чтобы сделать подсчет «4».

7.  Для подсчета количества «3», воспользуйтесь без вспомогательной функции ЕСЛИ. В ячейке С14 используйте функцию СЧЕТЕСЛИ

 

 

Задание 28 Файл «Отчет по тесту»

Группа учащихся писала тест, результаты которого приведены в таблице (тест содержит пять блоков по 10 вопросов в каждом). Баллы начислены за правильные ответы. Помогите преподавателю составив алгоритм, автоматически обрабатывать данные. Полученная таблица должна автоматически решать следующие подзадачи:

1.       Подсчитать, сколько баллов набрал за весь тест каждый ученик. (заполните блок Н2:Н9)

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

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

 

 

воспользуйтесь формулой:  ЕСЛИ 

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

ЕСЛИ (Н2 = $С$11; В2; “ ”) 5. Построить гистограмму, по данным полученным в п.1.

 

Задание 29 Файл можно не сохранять. Вложенные функции.

В бригаде 4 человека, они заработали деньги. Задача: если средний заработок бригады > 50 рублей, то на всю бригаду дается вся премия.

 

 

1.  Встаньте в ячейку, где необходимо ввести формулу. 

2.  Для того чтобы начать формулу с функции, нажмите кнопку Вставка функции   3. Выберите функцию, которую нужно использовать. (ЕСЛИ – категория логические)

4. Введите аргументы. 

ü Чтобы ввести функцию в качестве Логического выражения,  в поле функцию ЕСЛИ:  - встать в это поле

-  вставить вложенную функцию, которую можно вызвать кнопкой встроенных функции на панели формул , нажмите ЛКМ по треугольнику у

ЕСЛИ, раскроется меню

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

-  нам необходимо вернуться в функцию ЕСЛИ, для дальнейшего ее написания, в строке формул ЛКМ щелкните на функции ЕСЛИ  

-  дописываем в логическое выражение > 50

-  переходим в Значение_если_истина клавишей  ТАБ) 

-  вводим функцию суммы диапазона ячеек (G2:G3) (см. выше) или G2+G3 - Значение _ если _ ложь ставим 0. ОК.

 

З

адание 30 Файл «Расчетная квитанция»

 

Магазин продает товары, указанные в прайс – листе. Стоимость товара указана в долларах. Если стоимость товара превышает 1300 руб., покупателю предоставляется 5 % скидка.

В ячейке А1 набираем название фирмы. В ячейке А2 – Расчетная квитанция. В ячейке А3 –

Фамилия Имя покупателя. В ячейке В3 – будем писать конкретного покупателя. В ячейке Е4 – сегодняшнее число (Вставка – Функция – Дата и время – Сегодня) В ячейку В6 вводим курс доллара – 28,5. 

 

!!!Можно этого и не делать!!!! Присвоим имя ячейке В6- Вставка – Имя – Присвоить – Курс_доллара. В поле, где прописывается адрес ячейки, вместо адреса В6, теперь Курс_доллара.

 

Вводим оставшиеся данные.  D12  считаем цену товара – Цена ($) * Курс_доллара. Формулу копируем на остальной товар. (Ячейка «курс_доллара» постоянна)

(Постоянство ячейки достигается или знаками $ или присвоением имени ячейки) E12  считаем стоимость товара – Цена (руб) * Количество.

E19- сумма всего купленного товара.

E20 – сумма к оплате с учетом скидки 5 %. Воспользуйтесь мастером функций. Если  Итого

> Сумме для учета скидки, то 

Итого * (1-скидка), в противном случае оставить ячейку Итого.

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

 

 

Задание 31 Откройте файл «Успеваемость», лист 5 –  «Аттестат»

У учащегося необходимо вывести оценку за год. Затем выставить оценку за экзамен и вывести оценку в аттестат, которая считается следующим образом: если оценка за год выше или равна оценке за экзамен, то ставится оценка за год, в противном случае выводится среднее четвертных оценок и экзамена.

 

1. Наберите таблицу, приведенную на рисунке. Вручную внесены оценки за четверти и экзамен 2. В ячейке F4 (оценка за год по русскому) – это формула среднего значения четвертных оценок. Можно поставить =, затем просуммировать оценки и разделить на 4, но ЛУЧШЕ воспользоваться мастером функций СРЗНАЧ (среднее значение). Скопируйте полученную формулу во все остальные ячейки столбца. Уменьшите разрядность, до целых единиц.

3. В ячейке Н4 (средний итоговый балл по русскому языку) – считается по следующей формуле: 

Запустите мастер функций, выберите функцию ЕСЛИ;

 

Если оценка за год выше или равна оценке за экзамен,  то ставится оценка за год,  иначе ставится среднее значение, (вычисленное по оценкам за четверти и за экзамен).

 

Как подсчитать среднее значение,  которое будет записано в третьей строке: В строке формул раскрыть функцию и выбрать в списке СРЗНАЧ, в ней отметить те ячейки, которые участвуют в подсчете среднего значения. И нажать ОК. 

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

Задание 32 Файл «Стипендия»

Произведите вычисление стипендии при условии, что если у студента  есть минимальная оценка»3», то стипендии нет;  если есть минимальная оценка «4», то стипендия 300 рублей если все «5», то стипендия 500 рублей.

 

Оформите таблицу по образцу.

 

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

Рассмотрим подробно формулу: Условием здесь является минимальная оценка по всем предметам, поэтому применяется функция нахождения минимального значения МИН(В3:Е3), где аргумент охватывает интервал значений с оценками. 

Сначала проверяется условие МИН(В3:Е3)<4, т. е. наличие 3.  В этом случае стипендии нет, поэтому после условия стоит 0. 

Если условие МИН(В3:Е3)<4 не выполняется, то идет проверка на наличие 4. Поэтому на место значение_если_ложь опять введена функция ЕСЛИ, но с условием МИН(В3:Е3)>4. 

Если оценки все 5, т. е выполняется условие, значение_если_истина равно 500, если не выполняется, получает обычную стипендию 300.

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

 

Задание 33 Файл «Налоги США»

Рассчитайте долю каждого налога в отдельности, от общего поступления денег.

 

1.    Создать таблицу

получается суммированием

 

2.    Вычислить итоговое значение в B9 (применить автосуммирование).

3.    Ввести в D4 формулу для вычисления доли подоходного налога в общей сумме налогов.

4.    Скопировать формулу в D4 на ячейки D5–D8.  ()

(Примечание: во избежание ошибки применить  там, где нужно, абсолютные  ссылки.)

Доля в денежном поступлении в % - равна 100% получается суммированием 5. Построить круговую диаграмму и гистограмму.

Задание 34 Файл «Метео»

Выполните задание «Обработка данных метеостанции».

 

1. Откройте файл или создайте самостоятельно

 

                                               Количество осадков (мм)                                                                              _{Данные за 2003}

	2 001	2 002	2 003
январь	37,2	34,5	8
февраль	11,4	51,3	1,2
март	16,1	20,5	3,8
апрель	19,5	26,9	11,9
май	11,7	45,5	66,З
июнь	129.1	71,5	60
июль	57,1	152,9	50,6
август	43,8	96,6	145,2
сентябрь	85,7	74,8	79,9
октябрь	86	14,5	74,9
ноябрь	12,5	21	56,6
декабрь	21,2	22,3	9,4

январь	8
февраль	1,2
март	3,8
апрель	11,9
май	66,З
июнь	60
июль	50,6
август	145,2
сентябрь	79,9
октябрь	74,9
ноябрь	56,6
декабрь	9,4

прогноз

Данные за 2001-2003 годы

Мак. кол-во осадков   Миним.  кол-во осадков   Суммарное  кол-во осадков   Среднее  кол-во осадков   Кол-во засушливых месяцев

Максимальное количество осадков за 3 года (мм)   Минимальное количество осадков за 3 года (мм)   Суммарное количество осадков за 3 года (мм)   Среднемесячное количество осадков за 3 года (мм)   Количество засушливых месяцев за 3 года (мм)

 

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

3.     Заполните столбец Прогноз:

ü  засуха, если количество осадков < 15мм; ü          дождливо, если количество осадков > 70мм;  ü         нормально (в остальных случаях).

4.     Представьте данные таблицы Количество осадков (мм) графически, расположив диаграмму на Листе2. Выберите тип диаграммы и элементы оформления по своему усмотрению.

5.     Переименуйте Лист1 в Метео, Лист2 в Диаграмма. Удалите лишние листы рабочей книги.

6.     Подготовьте документ к печати:

ü  выберите альбомную ориентацию страницы; 

ü  подберите ширину полей так, чтобы все три таблицы умещались на странице; ü уберите сетку;

ü  укажите в верхнем колонтитуле (Вид, Колонтитулы) свою фамилию, а в нижнем - дату выполнения работы.

 

Задание 35 Откройте файл «Успеваемость», лист 6 –  «Проходной балл»

Необходимо проследить, кто из учащихся поступил на выбранное им направление. Для  определения, удовлетворяют  ли полученные на экзаменах оценки условиям поступления в 10 класс соответствующего направления, выглядит так: сумма оценок за первые два профилирующих предмета  должна быть больше или равна 9, а за два вторых предмета больше или  равна 7. Если оба условия выполняются, то в столбце Результат должно  появиться сообщение «прошѐл», иначе – «не прошѐл».

 

1.      Создать таблицу 

 

2.      Ввести в столбец Результат (Е5) формулу для  определения, удовлетворяют  ли полученные на экзаменах оценки условиям поступления в 10 класс соответствующего направления, 

3.      Скопировать формулу в E9, E13, E17. 

(Замечание: во избежание ошибки примените  там, где нужно абсолютные ссылки.) 4. Вводя в столбец D оценки, проверить, что результат им соответствует.

 

1)  Е5  = ЕСЛИ(И(СУММ(D5:D6)>=9;СУММ(D7:D8)>=7);$F$21;$F$22) 2)  . Можно решать задачу не используя постоянную ячейку.

 

Задание 36 Файл «Тесты», лист 1 «Тест_1»

Составьте этот тест или  тест похожий на данный 

 

ЕСЛИ сумма 5 вопросов = 0, то ПРОТЕСТИРУЙТЕСЬ; 

в противном случае 

ЕСЛИ 1 вопрос=0 И СУММА 2-5=4,  то Вы справились с тестом, поздравляю!,  в противном случае: Увы, Вы не справились с тестом!

 

=ЕСЛИ(СУММ(F8:F12)=0;E18;ЕСЛИ(И(F8=0;СУММ(F9:F12)=4);E19;E20))

 

Задание 37 Откройте файл «Тесты», лист 2 -  «Тест_2»

Составьте этот тест или  тест похожий на данный 

ЕСЛИ вопрос1=1 И вопрос2=3 И вопрос3=1 И вопрос4=2 И вопрос5=1, 

то Вы справились с тестом, поздравляю!,  в противном случае 

ЕСЛИ вопрос1=1 И вопрос2=3 И вопрос3=1, 

Кое-что Вы знаете, а информацию о Красной книге поищите в библиотеке,  в противном случае Увы, Вы не справились с тестом!

=ЕСЛИ(И(F8=1;F11=3;F14=1;F17=2;F20=1);D31;ЕСЛИ(И(F8=1;F11=3;F14=1);D33;D32))  

Задание 38 Откройте файл «Тесты», лист 3 - «Тест_3»

Психологический практикум. Идете ли вы на компромиссы?

 

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

С помощью этого теста определите. Умеете ли вы быть дипломатом, идти на компромиссы, или же вы упрямы и до конца настаиваете на своем.

Ответьте на вопросы теста, подсчитывая очки по следующей системе:  никогда – 1 очко,          часто – 4 очка,                      всегда – 5 очков.

редко – 2 очка,                       иногда – 3 очка,

 

№	Вопрос теста		Ответ	Очки
	Ваш одноклассник просит вас «прикрыть» его, так как ему нужно уйти с урока по личной причине. Вы не можете ему отказать?			Формула 1
	Вы считаете, что уступчивость и гибкость – очень важные качества?			Формула 1
	Ваш родственник, который вам не очень симпатичен, приглашает вас на свой праздник. Вы считаете своим долгом его поздравить и чтонибудь подарить?			Формула 1
	Ваш друг активно рекомендует вам музыкальный магазин. Придя туда, вы что-нибудь покупаете, чтобы не обидеть его?			Формула 1
	Вам случается просить помощи у людей, которые вам не очень приятны?			Формула 1
	Вас считают человеком вдумчивым и дельным?			Формула 1
	Вам случается выступать в роли примирителя ваших друзей?			Формула 1
	Девиз «живи и дай жить другим» мог бы быть и вашим девизом?			Формула 1
	Всего			Формула 2
Интерпретация
8-18		«Умру, но не склонюсь» – вот ваш девиз. Если уж вы с кем-то не разговариваете, то сами никогда не нарушите молчание. Вы способны поссориться со всеми по принципиальным соображениям. Вы вечно в оппозиции и твердо стоите на своем. Вы человек твердых правил, но ваша излишняя стойкость иногда мешает вам в отношениях с близкими, друзьями и коллегами.
19- 31		Вы человек достаточно гибкий. Вы понимаете, что в жизни нужно уметь приспосабливаться к обстоятельствам, и стараетесь это делать. Однако вы способны сказать твердое «нет», если вас что-то не устраивает.
32- 40		Да, вы настоящий дипломат. Вы изворотливы и по этому редко попадаете в бедственные ситуации. Вы дружите со всеми и очень редко ссоритесь. Вас все считают очень милым, но до поры, пока не раскусят вашу тактику. И тут оказывается, что на самом деле у вас нет настоящих друзей. Поверьте, никогда не бывает так, чтобы «и волки сыты и овцы целы». Чем-то нужно пожертвовать.

ОТВЕТ

№	Вопрос теста	Ответ	Очки
1	Ваш одноклассник просит вас "прикрыть" его, так как ему нужно уйти с урока по личной причине. Вы не можете ему отказать?		ЕСЛИ(C2="всегда";5; ЕСЛИ(C2="часто";4; ЕСЛИ(C2="иногда";3; ЕСЛИ(C2="редко";2; ЕСЛИ(C2="никогда";1; "такого ответа нет")))))
2	Вы считаете, что уступчивость и гибкость - очень важные качества?		такого ответа нет
3	Ваш родственник, который вам не очень симпатичен, приглашает вас на свой праздник. Вы считаете своим долгом его поздравить и что-нибудь подарить?		такого ответа нет
4	Ваш друг активно рекомендует вам музыкальный магазин. Придя туда, вы что-нибудь покупаете, чтобы не обидеть его?		такого ответа нет
5	Вам случается просить помощи у людей, которые вам не очень приятны?		такого ответа нет
6	Вас считают человеком вдумчивым и дельным?		такого ответа нет
7	Вам случается выступать в роли примирителя ваших друзей?		такого ответа нет
8	Девиз "живи и дай жить другим" мог бы быть и вашим девизом?		такого ответа нет
	всего		СУММ(D2:D9)

В ячейке А13

на листе 8-18 б, в ячейке А1

на листе 19-31 б, в ячейке А1

 

Задание 39  Файл «Биоритмы» Выполните задание «Изменение биоритмов человека».

1. На Листе 1 создайте таблицу по образцу.

 

А2 – дата рождения (число, месяц, год)

АЗ – текущая дата

А4 – формула для расчета прожитых дней (категория Дата и время)

А7 – текущая дата (число, месяц)

А8, А9 и т.д. – последующие даты (число, месяц)  В7, В8 и т.д. – количество прожитых дней

 

3.      Вычислите биоритмы физической, эмоциональной и интеллектуальной активности по формуле:

2 (t t⁰) F sin ,где

T

t – время (дата исследований) t_o – дата рождения

Т – соответствующий период активности (23, 28 или 33 дня).

4.      Отформатируйте таблицу по своему желанию.

5.      Создайте колонтитулы (Вид – Колонтитулы):

в верхнем укажите фамилию и имя (по центру),  в нижнем – дату и время выполнения работы (справа).

6.      Переименуйте Лист 1 в Таблицу.

7.      На Листе2 постройте график изменения ваших биоритмов. Установите минимальный размер шрифта для подписей.

8.      Проанализируйте полученные результаты. По графику определите, в какие дни ваша физическая, эмоциональная и интеллектуальная активность достигает максимумов и минимумов (в отдельности). Есть ли критические дни (совпадение значений всех Функций)? Укажите их в произвольной форме под графиком.

9.      Задайте альбомную ориентацию данного листа. 

10.  Переименуйте Лист2 в Диаграмму.

11.  Удалите лишние листы.

Задание 40 Откройте файл «Ремонт», лист 2 – «Обои»

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

 

Площадь = Длину * Ширину, но необходимо отбросить обрезки (10%):

Площадь рулона:  Sð                 (1 Обр)*l*d

В прямоугольной комнате 

2 стены Площадь = Длину * Высоту,  и 2 стенки Площадь = Ширину * Высоту.

S_стен2*(a*h)2*(b*h)2*h*(ab)

 но необходимо учитывать неоклеиваемую площадь (НП) окон и дверей (15%):

 

                         S_стен2*h*(a b)*(1-НП)

 

Количество рулонов             N ^Sстен 1

                                                     S_ç

добавлен один запасной рулон, но необходимо учесть что N – целое число Заполним по образцу расчетную таблицу. 

Введем формулы в ячейки. 

Ячейка	Формула
В9	= 2 * $В$5 * ($B$6 + $B$7) * (1 - $B$8)
D14	= B14 * C14 * (1 - $B$12)
E14	= ЦЕЛОЕ ($B$9 / D14) + 1

Примечание: Функция ЦЕЛОЕ() округляет до ближайшего целого числа, меньшего, чем заданное. Но поскольку количество рулонов нельзя округлять в меньшую сторону, то к заданной функции + 1 для округления в большую сторону и получаем 1 запасной рулон.

Задание:

ТЕСТИРОВАНИЕ: Провести тестовый расчет компьютерной модели по данным, приведенным в таблице.

ЭКСПЕРИМНТ 1: Провести  расчет количества рулонов обоев для помещений вашей квартиры.

ЭКСПЕРИМЕНТ 2: Изменить данные некоторых образцов обоев и проследить за пересчетом результатов.

ЭКСПЕРИМЕНТ 3: Добавить строки с образцами и дополнить модель расчетом по новым образцам.

 

Задание 41 Файл «Графики функций», лист 1 – «График 1»

 

Дана функция

         f (x)                x² 3x 2

Используя       электронные таблицы,        вычислить      значения         функции  у = f(х) для значений аргумента х = -5; -4,5; -4; …; 7; 7,5; 8.

Построить график функции у = f(х) и определить (приблизительно), при каких значениях аргумента х значение функции у = 0.

1.    Ввести заголовки столбцов: в ячейку А1 ввести текст «Х»; в ячейку В1- «f(x)»

2.    Ввести начальные данные: в ячейку А2 ввести число -5; в ячейку  В2 – формулу   = А2^2/ 2 – 3 * А2+ 2 

3.    С помощью автозаполнения занести в столбец А значения переменной Х: 

а) Установить курсор мыши в ячейку А2.

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

в) В окне Прогрессия установить: Расположение – по столбцам; Тип – арифметическая; Шаг – 0,5; Предельное значение – 8

4.    Вычислить значения f(x) для заданных значений Х: скопировать содержимое ячейки В2 в ячейки В3:В28

5.    Построить график: Выделить блок А1:В28. Вызвать Мастер диаграмм.

6.    Выбрать тип диаграммы: ХУ – точечная, тип: со значениями соединенными линиями 7. Определить приблизительно те значения Х, при которых график пересекает ось ОХ

y=1/2x^2-3x+2 x    f(x)

 

 

Задание 42 Откройте файл «Графики функций», лист 2 – «График 2»

Постройте график функции

9      при  5 x 5; при -10 < х < 10 с шагом h = 0,5. f (x)

                 3,5  при  х     5,  x    5.

 

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

Пусть в ячейке А2 находится значение х = - 10. В ячейку В2 формулу можно занести,

 

Далее формула копируется, и строится график.

 

Задание 43 Откройте файл «Графики функций», лист 3 – «График 3»

 

 

Задание 44 Откройте файл «Графики функций», лист 4 – «График 4»  

f (x)

 

 

 

Задание 45 Откройте файл «Графики функций», лист 5 –  «График 5»

x		f(x)
	-5		0,8
	-4		0,8
	-3		0,7
	-2		0,6
	-1		0,5
	0		1,0
	1		2,7
	2		2,8
	3		1,3
	4		0,5
	5		0,9

Постройте график функции

 

Задание 46 Откройте файл «Графики функций», лист 6 – «График 6»

 

Задание 47 Откройте файл «Графики функций», лист 7 – «График 7» Постройте график функции

 

Задание 48 Откройте файл «Графики функций», лист 8 –  «Уравнение»

Вычислите значение функции У = Х³ + sin Х – 4е^Х при Х = 1,58. Размещение данных в числовом и формульном видах:

 

Следует обратить внимание на вызов функций синуса и экспоненты:

 

 

 

Задание 49 Файл «Справочник»

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

 

2.          Для удобства создадим небольшой справочник. Сначала определимся, какие данные нам необходимы.

 

3.          Далее необходимо таблицу заполнить. У вас уже есть несколько введенных сотрудников. Заполним у них вычисляемые поля:

 

ВЫЧИСЛЯЕМЫЕ поля: Возраст, Сколько лет до пенсии

Возраст: а) В категории Математические берем функцию ЦЕЛОЕ, т.к. нам необходимо вычислить, сколько полных лет.

б) Раскрываем список вложенных функций, в строке формул и выбираем функцию СЕГОДНЯ (или Другие функции …- Категория

- Даты и времени)

в) Т.к. в функции СЕГОДНЯ аргументов нет,       в функцию ЦЕЛОЕ. В строке формул по щелкнуть мышкой.

г) В строке аргументов функции ЦЕЛОЕ, дописываем формулу: от функции СЕГОДНЯ вычитаем дату рождения. 

д) Разность делим на среднюю продолжительность года (365 дней)   (СЕГОДНЯ()-D2)/365

Формат этих ячеек должен быть числовым Сколько лет до пенсии: 

Для заполнения этого столбца, необходимо воспользоваться следующей информацией:  ЕСЛИ в столбце пол стоит ―М”,  то от 60 лет отнять возраст, 

в противном случая 55-возраст (т.к. это женщина, а она  идет на пенсию в 55 лет)

3.    Заполнить             таблицу          еще      несколькими   сотрудниками.          Данные           можно        вводить непосредственно в столбцы, но т.к. информации очень много воспользуемся другим способом заполнения базы данных:

а) выделить всю таблицу (шапку и данные о сотрудниках);

б) выбрать Данные – Форма, в результате чего получим форму, которую и будем заполнять;

в) т.к. у нас есть в таблице сотрудники, нажать кнопку Добавить;

Tab

г) перемещение по полям – клавиша Табуляции . Завершив ввод информации об одном сотруднике, нажмите кнопку Добавить. Кнопка Закрыть завершает работу с формой;

д) обратите внимания, что есть поля, которые необходимо считать, недоступны для заполнения  (Возраст, Сколько лет до пенсии)

 

 

4.    Необходимо отсортировать по алфавиту и применить новую нумерацию по порядку. Выделить всю таблицу. Данные – Сортировать – Фамилия, имя – по возрастанию.

Выделить 1 и 2 и автозаполнение протянуть до последнего сотрудника, кол-во: 18 человек.

5.    Посчитайте количество служащих по трем возрастным категория: до 25 лет; от 25 до 40; после 40 лет.

Е20

Е21

Е22

Количество служащих  возрастной группы от 25 до 40 определяется вычитанием из общего количества слушателей (используется функция СЧЁТ, категория Статистические) тех, кому <25 и > 40. 

6.    Посчитайте средний возраст слушателей.

 

Поиск и замена данных

Прежде чем приступить к замене данных, необходимо осуществить их поиск: 

1.     Правка – Найти. Необходимо либо выделить фрагмент, в котором будем искать (например, столбец) или поиск распространяется на весь рабочий лист.

2.     Найдем сотрудников живущих в Новокузнецке. Найти далее и т.д.

3.     Найдем служащих родившихся в мае. 

Т.к. нам неважно число и год рождения – воспользуемся символом            *.       Он       служит            для      указания         любой последовательности символов. 

4.     Найдем служащего живущего в 44 квартире. Больше ничего о нем вы не помните. Грамотнее будет, если вы выделите столбец, в котором искать: Адрес. В окне поиска установить галочку на Ячейка целиком 

5.     По номеру телефона вам необходимо определить его обладателя, но вы забыли 2-ую цифру. Набираем известные цифры, а на месте неизвестной ставим  знак вопроса. 6 ? 9 8 7 8

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

6.     Найдите сотрудников живущих в Ленинграде и Замените на С-Петербург

а) Выделить столбец поиска, 

б) Правка – Найти, 

в) Ввести Ленинград, 

г) Активизировать папку Заменить 

д) Вписать С-Петербург 

е) Заменить все

 

Есть еще возможность находить информацию через Фильтры.

1.     Выделите шапку в таблице A1:K1;

2.     Выбери Данные–Фильтр-Автофильтр 

3.     Создайте список Новокузнечан: раскройте фильтр и выберите нужный город.

 

Если необходим список из 2-х городов Новокузнецк и Кемерово:

1.      Раскройте список города;

2.      Выберите Условие

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

4.      Далее выбираем математическое ИЛИ, т.к. нам нужен и Новокузнецк и Кемерово.

5.      Аналогично заполняем последнюю строку с городом Кемерово.

 

Все данные возвращаются: раскрыть фильтр (он голубой)– выбрать ВСЕ.

 

Задание: Нам нужен список сотрудников: живущих в России, до пенсии >= 20 лет и с контрактом > 25 000 рублей.

У вас должен остаться список из 2-х сотрудников: Орлов, Петров.

 

Задание: Выведите список сотрудников с годом рождения с 1960 по 1970. У вас должен остаться 6 сотрудников.

 

Воспользуемся расширенным фильтром.

Задание: Нам нужен список мужчины с годом рождения с 1960 по 1970 и выходом на пенсию не раньше чем  через 20 лет.

1. Перед первой фамилией вставьте 2 строки и внесите критерий для фильтрации: 

Одним из способов обработки и анализа базы данных состоит в подведении различных итого. С помощью команды Данные – Итоги можно автоматически вставить строки итогов в списки. В книжном магазине товаровед ведет учет товарооборота. Необходимо в конце месяца подвести итог отдельно по каждому покупателю.

 

1.

2.

3.

4.

5.

столбец

       6.  В     поле

7.

вычисления)

8.

Задание: Отсортируйте по дате и выдайте, сколько товара и на какую сумму было продано 

 

Задание 51 Файл «База данных» 1. Откройте файл или создайте самостоятельно таблицу.

 

2.         Посчитайте зарплату в ячейку К2: оклад / 21день*кол-во выходов

= H2 / 21 * I2 и скопируйте для остальных сотрудников.

3.         Отсортируйте: Сначала по Отделам, затем по Профессиям, затем по Фамилиям Выделить всю таблицу А1:К17; Данные – Сортировка

4.         Вставьте столбец о количестве детей перед Годом рождения: ПКМ по названию столбца Е – Добавить ячейки

У 7 человек детей нет (поставьте 0 или просто пропустите).

5.         Встать в ячейку В20 написать – ср.зарплата

6.         Подсчитать Среднюю зарплату по предприятию в ячейке С20

 

Нажать f(x), найти функцию СРЗНАЧ, Далее, Выделить блок L2:L17

 

7.         Встать в ячейку М1 написать – Не отработано дней

8.         В ячейке М2 посчитать сколько дней в месяце не отработано, если должно быть 21 выход: 21 – ―Кол-во выходов‖  М2 = 21 – J2

 

9.         Формулу скопировать на всех сотрудников:

Встать в ячейку М2, установить мышку в правом нижнем углу этой ячейки в виде черного крестика и не отпуская ЛКМ тащить вниз.

 

10.     В ячейке М18 просуммируйте, сколько всего не отработано дней по предприятию:

Встать в ячейку М18, нажать кнопку автосумма, проверить блок М2:М17, зафиксировать результат.

 

11.     Установить в таблице Автофильтр:

Выделить шапку А1:М1, Данные – Фильтр – Автофильтр

 

12.     Поочередно устанавливать условия: Раскрывать фильтр на необходимом столбце; выбирать УСЛОВИЕ.

Для того чтобы вернуть всех сотрудников: Раскрывать фильтр, (он выделен цветом) –

Выбирать ВСЕ

a)      Выберите всех сотрудников, у которых есть дети Кол-во детей – УСЛОВИЕ: >0 (верните всех сотрудников₎

b)      Выберите всех руководителей

Категория – УСЛОВИЕ =1 (верните всех сотрудников)

c)      Выберите тех сотрудников у которых Оклад от 6 000 руб. до 12 000руб. 

Оклад – УСЛОВИЕ  >= 6000                        И         <= 12000

13.     На основе последней фильтрации постройте диаграмму, в которой отражались бы Фамилии сотрудников и их Оклады

Задание 52 Файл «Расчет зарплаты»

1.  Откройте файл или создайте сами Лист назовите Зарплата.

Следующий лист назовите Сортировка. Файлу дайте имя расчет зарплаты.

 

2.  Заполните пустые ячейки  (удержание составляет 14%).

3.  Для данных столбцов Начислено, Удержано, К выдаче          примените      денежный       формат         с

разделителем тысяч. 

 

5.         Найдите, используя Автофильтр, записи о сотрудниках с зарплатой ниже 4000 р. 

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

7.         Найдите записи о сотрудниках 1-го отдела. Скопируйте результаты поиска и поместите их после результатов выполнения пункта 6.

8.         Найдите записи о сотрудниках 2-го отдела, у которых сумма к выдаче находится в пределах от 2000 р. До 4000 р. Скопируйте результаты поиска и поместите их после результатов выполнения пункта 7.

9.         Найдите все записи об инженерах, фамилии которых начинаются на букву «П».  Скопируйте результаты поиска и поместите их после результатов выполнения пункта 8.

 

В пунктах 10 – 12 

скопируйте полученные результаты сортировки на лист Сортировка. Оставляйте между таблицами 2 строки.

10.     Отсортируйте данные таблицы по двум ключам: Отдел (по возрастанию), Ф.И.О. (по возрастанию).

11.     Отсортируйте данные по двум ключам: Отдел, Начислено (по убыванию).

12.     Отсортируйте данные по трем ключам:  Отдел, Должность, Ф.И.О.;

13.     Скройте на листе Зарплата содержимое столбцов:  С, D, Е, F, G.

 

Задание 53 Файл «Склад»

1.      Заполните таблицу.

2.      Заполните пустые ячейки с учетом формата данных.

(на некоторые продукты специально укажите просроченную дату реализации).

3.      Переименуйте Лист1 в Склад, Лист2 – в Сортировка, Лист3 – в Поиск.

4.      Выполните форматирование и обрамление таблицы по образцу.

5.      Отсортируйте записи в порядке убывания Срока реализации. Скопируйте результат на лист Сортировка.

6.      Отсортируйте данные по двум ключам: Продукты (по возрастанию), Цена (по убыванию). Скопируйте результат на лист Сортировка.

7.      Найдите все продукты с истекшим сроком реализации. Скопируйте результат на лист Поиск.

8.      Найдите все продукты, для которых общая сумма не меньше 300 р. И не больше 1000 р. Скопируйте результат на лист Поиск.

9.      Найдите все продукты, количество которых больше 300, а срок реализации еще не истек. Скопируйте результат на лист Поиск.

10.  Придумайте свои критерии поиска (не менее двух). Запишите эти условия и скопируйте результат поиска на лист Поиск.

11.  На всех листах в верхнем колонтитуле укажите свою фамилию и дату выполнения работы, в нижнем – названия листов

 

Задание 54 Файл «Ведомости»

Подготовь ведомость на выдачу заработной платы (упрощенный вариант)  1. Для выполнения упражнения нам понадобятся только четыре листа:

ü   на первом разместим сведения о начислениях

ü   на втором- диаграмму

ü   на третьем – ведомость на выдачу зарплаты

ü   на четвертом – ведомость на выдачу компенсаций на детей

Чтобы  вставить (удалить) лист надо выделить ПКМ ярлык листа и выбрать соответствующую команду  ЛКМ.

2. Таблица должна выглядеть так

ü  Примем, профсоюзный и пенсионный налоги составляют 1% от оклада. Не забудь про абсолютные скидки, т.к. проф. и пенс. Налоги нужно брать от оклада, т.е. ссылаться только на столбец «Оклад». Примерный вид формулы: =$С3*1% или =$С3*0,01 и распространи еѐ вниз а затем вправо на столбец.

ü  Подоходный налог подсчитываем по формуле: 13% от Оклада за вычетом минимальной зарплаты и пенсионного налога. Примерный вид формулы:

               = (С3 – Е3 – 850)*0,13            или               = (С3-Е3 -850)*13%.

ü  Сумма к выдаче вычисляется как разность оклада и налогов.

ü  Выполни сортировку данных по фамилиям по алфавиту.

ü  Назовем наш первый лист «Начисления». Для этого выбери [Формат – Лист – Переименовать . . .] или ПКМ по ярлыку – Переименовать

3.    Построй диаграмму, отражающую начисления каждого сотрудника. Надо выделить два столбика «Фамилия, имя)} и «Сумма к выдаче». (Удерживай клавишу Ctrl можно одновременно выделять ячейки в разных местах таблицы). Выполни команду [Вставка – Диаграмма]. Выбери тип диаграммы – объѐмная круговая, подтип второй (с метками данных). Построй диаграмму на отдельном листе (следуя по стрелке Далее).

4.    Создай ведомость на получение компенсации на детей на основе таблицы начисления.

ü  Перейди на следующий лист и пере именуй его в «Детские».

ü  Оформи три столбца «ФИ О», «Сумма» и «Подпись»

ü  В графу «ФИО» нужно поместить список сотрудников, который есть на листе «Начисления». Для этого. Выдели ячейку А2 листа «Детские» и введите формулу = перейти на лист «Начисления», выделить ячейку, содержащую первую фамилию и нажать Епtеr (не возвращаясь к листу «Детские»).

ü  Перейдите на лист «Детские», проверь полученную формулу и распространи еѐ вниз. 

ü  В графе «Сумма» нужно аналогично ввести формулу, ссылаясь на лист «Начисления». 

5.    Выполни обрамление таблицы.

6.    Для того чтобы список состоял только из сотрудников, имеющих детей: Данные- Фильтр – Автофильтр, в раскрывающемся списке выберите условие Число детей >0.

7.    Оформи последний лист – ведомость на выдачу зарплаты. Таблица должна содержать столбцы: «ФИО», «Сумма к выдаче» и «Подпись». 

ü  Переименуй лист из «Лист 4» в «К выдаче».

ü  Вставь фамилии сотрудников со ссылкой на лист «Начисления». Заполни формулу вниз.

ü  Вставь суммы, ссылаясь на лист «Начисления»

ü  Выполни обрамление таблицы,

8. Нижняя строка с названием ЛИСТОВ будет выглядеть примерно так:

 

 

Задание 55 Файл «Амортизация»

Рассчитайте годовую сумму амортизации для указанного периода времени

В мастере функции выберем Категория: Финансовые, функция АСЧ-Возвращает величину амортизации актива за данный период, рассчитанную методом «суммы (годовых) чисел».

Синтаксис

АСЧ (нач_стоимость;ост_стоимость;время_эксплуатации;период) Нач_стоимость — та сумма, за которую вы купили товар.

Ост_стоимость —стоимость, которой имущество достигает по окончагии срока службы (например списали).

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

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

 

Задание 56 Файл «Консолидация»

Консолидация – процедура получения итоговых данных по одной или нескольким категориям. Данные из нескольких исходных областей можно обработать и отобразить в одной итоговой таблице. Источники данных могут находиться как в одной книге (файлах) на разных листах, так и в разных книгах. При подведении итогов при консолидации используется функции, например, СУММ

 

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

1.      Переименуйте лист 1 в январь, и т.д.

2.      На первом листе – январь, заполняем следующую таблицу:

 

3.      Продано за месяц = остатки + колво – СУММА за месяц

4.      а) На листе Январь выделите блок

А1:Р1 и скопируйте его

б) Перейдите на лист февраль, встаньте в ячейку А1

в) Правка – Специальная вставка – Вставить – ширины столбцов, а за тем Правка – Вставить

г) Скопируйте с листа Январь название товара

д) На листе Февраль встаньте в ячейку С3 поставьте =  ЛКМ щелкните по ярлыку Январь, встать в ячейку Р1 .  Зафиксируйте формулу  е) Скопируйте вниз до последнего товара

5.      а) Введите количество товара поступившего в этом месяце б) Введите проданный товар

в) Формулу: На конец месяца можно скопировать с листа Январь

6.      На листе Отчет подготовьте таблицу: 

7.      Встаньте в ячейку В2, выполните команду Данные – Консолидация..        .

Появится диалоговое окно.

а) в строке Функция выберите Сумма

б) установите курсор в строку Ссылка и выделите исходную область

(кол-во товара купленного в январе). В нашем случае: на листе

Январь D3:D7

в) нажмите кнопку Добавить

г) т.к. у нас еще один интервал, то выделить лист Февраль и блок D3:D7, нажать кнопку

Добавить

д) создавать связи с исходными данными – отметить галочкой. ОК.

 

Задание 57 Файл «Конверт»

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

Для этого:

Заполните таблицу по образцу, кроме столбца «Наклейка на конверт».

В ячейке F2 запущена функция СЦЕПИТЬ (категория: Текстовые)

Элементами текста с 1 по 30 могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку. Нельзя в один элемент текста вводить текст с клавиатуры и ссылку на ячейку (необходимо задавать в разных строках текста). Д.т.ч. увидеть следующую строку текста воспользуйтесь линейкой прокрутки.

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

 

Задание 58 Файл «Импортирование данных»

Получение данных из текстового файла.

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

 

1.          Создайте маленькую учебную базу в текстовом формате. Запустите Блокнот и создайте базу, разделяя столбцы клавишей ТАВ. (если таблица уже есть в т.р.  Word, то ее можно скопировать и вставить в Блокнот).

 

2.          Сохраните файл с именем пример_база.txt

3.          В Excel   выберете        Данные          –  

              Импорт                 внешних                 данных                 – 

Импорт данных.

4.          На экране первое диалоговое окно. Это обычное окно открытия файла с расширением *.txt. Найдите свой файл пример_база.txt   и откройте.

 

5.          Второе окно мастера импорта содержит элементы

управления для распознавания формата данных. ДАЛЕЕ.

6.          Второй шаг – анализ данных. 

7.          Следующий шаг – уточнение формата каждого столбца

8.          Последний шаг – уточните размещение вашей таблицы

 

 

Задание 59  Файл «ФОТО»

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

 

Пусть в классе N учеников. Каждый из них отдал (N – 1) фотографию. Следовательно, всего отдано N * (N – 1) фотографий.

Получаем уравнение: N * (N – 1) = 600.

1. В ячейку А1 введите текст «Учеников в классе:».  В ячейку А2 введите текст «Уравнение:». 2. В ячейку В2 введите формулу: =В1 * (B1 – 1).

3.    Выберите команду Сервис -  Поиск решения. Установите требуемые реквизиты в следующем виде: целевую ячейку В2 приравняйте к значению 600, отметьте изменяемую ячейку В1

4.    Введите ограничение В1>=0. Для этого щелкните на кнопке Добавить и в полученном окне установите параметры. 

5.    Закройте окно Добавить ограничение. ОК. 

6.    Решите задачу: окно Поиск решения, кнопка Выполнить.  7. Проверьте полученный в ячейке В1 ответ. Ответ. В классе 25 человек.

 

Задание 60 Файл «Хлебный магазин»

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

Вам необходимо получить максимальную прибыль и оптимально заполнить склад. Составьте и заполните данную таблицу:

 

 

1.    Т.к. необходимо получить максимальную прибыль, целевой ячейкой будет G8. 

2.    Вызываем  Сервис – Поиск решения.

3.    Изменяемая часть Кол-во лотков. На изменяемую часть необходимо наложить некоторые ограничения: кол-во лотков – число положительное; кол-во лотков – число целое; кол-во лотков – больше заказа; кол-во лотков – меньше поставки; общее количество лотков – равно объему склада. 

4.    Выполнить. Сохраните данное решение. 

Задание 61 Файл «Покраска пола»

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

Сначала измеряют длину а (18,118,3) и ширину b (7,67,7) пола. 

Реальный объект  – пол зала – заменяют прямоугольником, для которого S = аb.

При покупке краски выясняют, какую площадь S1 можно покрыть содержимым одной банки (предположим меньше 10 м²), вычисляют необходимое количество банок _n ab .

S₁ а, b, S₁ – поисковые переменные, значения которых можно изменять.  Необходимо задать ограничения: а  18,1;   а  18,3;   

                                                         b      7,6;    b      7,7;   S₁  10.

Критерий оптимизации: количество банок должно быть минимальным, т.е. _n ^ab =min.

S₁

Решение на компьютере:

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

2.  Найти оптимальное решение, для этого: 

ü  выделить целевую ячейку В7;

ü  выбрать Сервис, Поиск решения;

ü  установить целевую ячейку, равную минимальному значению;

ü  указать мышью диапазон изменяемых ячеек;

ü  выбрать кнопку Добавить для записи ограничений; ü после записи ограничения нажать Добавить (для последнего ограничения – ОК);

ü  нажать кнопку Выполнить;

ü  выбрать Тип отчета, Результаты и нажать ОК.

На новом листе Отчет по результатам 1 можно увидеть:

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

 

Задание 62 Файл «Скорость течения»  

На путь по течению реки катер затратил 3 ч, а на обратный путь – 4,5 ч. Определите скорость течения реки, если скорость катера относительно воды 25 км/ч.

 

Пусть V км/ч – скорость течения реки. Тогда по течению катер прошел [(25 + V) * 3] км, а против течения – [(25 – V) * 4,5] км. Учитывая, что эти расстояния равны, имеем: (25 + V) * 3 = (25 – V) * 4,5

 

Разместим данные на рабочем листе Excel. Ниже приведены числовой и формульный виды размещения:

Воспользуемся командой Сервис – Поиск решения. Диалоговое окно этой команды имеет вид: Таким образом, решением задачи является число, размещенное в ячейке Bl, и оно равно 5. Ответ. Скорость течения составляет 5 км/ч.

 

Задание 63 Файл «Анкета»

Создание Формы

Создадим анкету, используя панель инструментов – Форма.

1.          В ячейке А1 напишите АНКЕТА

2.          В ячейке А3 – Фамилия И.О.

3.          Теперь нам нужна панель инструментов Форма:  Вид – Панель инструментов – Формы  

 

4.          Допустим, что список детей мы знаем, и он находится в этом же файле на Листе 2 (Создайте такой список).

Т.к. список у нас фиксированный, На панели Формы выберете кнопку Поле со списком  и растяните еѐ.

Щелкните по полю ПКМ – Формат объекта - Элемент управления – Форматировать список по диапазону – Выделите список детей на Листе 2 – Количество строк можно поставить больше, чем есть для того, чтобы можно было список пополнять.

5.          В поле Возраст текст будет вноситься самостоятельно ребенком.

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

6.          Далее у нас идет поле Пол. Здесь удобно взять кнопку Переключатель , т.к. он выбирает один ответ из нескольких. 

Поля муж. и жен. Работают в паре, удобно их сгруппировать. Растяните кнопку Группа , и дайте ей имя Пол. 

Растяните поле Переключатель и дайте им надписи

(муж., жен.)

7.          В следующем    группе            идут    любимые       предметы. 

Здесь может быть выбрано много вариантов, значит, воспользуйтесь кнопкой Флажок  и дайте надписи, но сначала растяните кнопку Группа  (для удобства), дайте имя: Любимые предмет.

8.          Далее вопрос – С кем проживаете?

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

Растяните кнопку Группа  и дайте ей имя.

В данном случае снова необходимо создать, где  ни будь список (Лист 2) (мама, папа, …)

Внутри группы растяните кнопку Список . ПКМ – Формат объекта или кнопка Свойства  - Элемент управления – Форматировать список по диапазону – Выделите список на Листе 2 (можно выделить чуть больше, для дальнейшего добавления) -  Возможен выбор – Набор значений.

 

Задание 64 Файл «Макросы»

Задача 1 У нас будет зажигаться, и потухать лампочка или любая геометрическая фигура. 

 

1. Откройте MS Excel. 2. Проверьте, присутствует ли у вас панель инструментов Рисование. (Вид – панели инструментов).

3.         Нарисуйте любую геометрическую фигуру, например овал. (Разместите в левом верхнем углу).

4.         Выберите команду Сервис – Макрос – Начать запись. В окне имя макроса (Макрос1) Зажечь. ОК. На экране будет выведена панель остановки записи макроса.

5.         Выделите овал, затем в палитре инструмента Цвет заливки выберите любой цвет, например синий.

6.         На панели инструментов Записи макроса щелкните по кнопке Остановить запись. Вы написали макрос, закрашивающий овал красным цветом.

7.         Выберите команду Сервис – Макрос – Начать запись. В окне имя макроса (Макрос2) Потушить. ОК. На экране будет выведена панель остановки записи макроса.

8.         Выделите овал, затем в палитре инструмента Цвет заливки выберите любой цвет, например, светло серый.

9.         На панели инструментов Записи макроса щелкните по кнопке Остановить запись. Вы написали макрос, по которому ваш овал потухает. 

10.     Для того чтобы увидеть, что представляют собой записанные макросы, нужно выбрать команду Сервис – Макрос – Макросы – откроется диалоговое окно Макрос. В списке из макросов выберите поочередно эти макросы и нажмите на кнопку Выполнить.

 

Согласитесь, что не очень удобно ходить постоянно в меню и выбирать так макросы. Наверно будет удобнее, если мы эти команды (Зажечь и Погасить) поместим в доступные кнопки, которые поместим тут же, рядом с овалом.

11.     На панели инструментов Рисование воспользуемся WordArt. Создадим первое слова Зажечь и второе Потушить

12.     Щелкните ПКМ по слову Зажечь, в контекстном меню выберите команду Назначить макрос – Зажечь. ОК. 

13.     То же самое повторите со словом Потушить (созданным в ), которая выполняет функцию «Потушить».

 

Обратите внимание на то, что перед выполнением макросов, объект (овал), к которому применяется он (макрос) был выделен.

1.        Выделите овал.

2.        Поочередно понажимайте на слова Зажечь и Потушить.

 

Самостоятельно:

1.  Создайте еще один макрос, который будет менять цвет овала, например на жѐлтый. И создайте слово, которое будет выполнять этот макрос.

2.  Попробуйте макрос назначить кнопке. 

 

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

1.      Создать макрос (Сервис – Макрос – Начать запись), дать имя. Сделать те шаги, которые будет выполнять макрос. Остановить запись.

2.      Запустить панель инструментов Формы. Щелкните на элементе Кнопка и растяните еѐ. 

3.      Если выходит окно сразу Назначить макрос объекту, то это и сделать,  а если нет, то ПКМ по Кнопке – Назначить макрос – ОК.

4.      Не забудьте переименовать Кнопку. ПКМ – Изменить текст.

Алгоритм:

1.      Создаем документ и в нем объекты, к которым будем применять макросы. (фигуры, таблицы, ...)

2.      Создать макрос (Сервис – Макрос – Начать запись), дать имя. Сделать те шаги, которые будет выполнять макрос. Остановить запись.

3.      Создать объект, который будет выполнять роль макроса.

4.      Назначить этому объекту макрос (ПКМ – Назначить макрос – Выбрать имя макроса)

 

Создание макроса с помощью кнопок. Задача 2

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

 

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

 

Начислено = оклад / раб.дни * отраб.дни Удержано = начисл. * 0,12 Сумма на руки = начисл. – удерж.

 

 

Создадим кнопку.

1.      Вид – Панели инструментов – Настройка – Команды – Макросы

2.      ЛКМ переносим Настраиваемую кнопку на панель инструментов. 

3.      Изменить вид объекта, выбрать

-   Основной стиль

-   Значок 4. Закрыть.

 

Будем создавать Макрос:

5.         Поместить курсор в ячейку А1;

6.         Сервис – Макрос – Начать запись.

7.         Дать имя, например Ведомость.

 

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

 

изменить заголовок таблицы; скрыть вторую строку (перемещая ЛКМ); скрыть столбцы С, D, E, F (перемещая ЛКМ); ввести дополнительный столбец для росписи в получении денег;  предусмотреть место для подписей директора и главного бухгалтера.

 

8.         Остановить запись.

 

Назначим кнопке, макрос - Ведомость

9.         ЛКМ щелкнуть по кнопке Макроса, в появившемся диалоговом окне Назначить макрос Ведомость кнопке.

 

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

 

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

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

 

Алгоритм:

1.        Создали документ.

2.        Создать кнопку: Вид – Панели инструментов – Настройка – Команды – Макросы.  ЛКМ перенести кнопку на панель инструментов.

3.        Создать макрос (Сервис – Макрос – Начать запись), дать имя. Сделать те шаги, которые будет выполнять макрос. Остановить запись.

4.        Назначим действия макроса кнопке: ЛКМ по кнопке -  Назначить макрос – Имя макроса)

 

Использование макросов при создании тестов.

Задача 3

1.      Запустить панель инструментов           Формы. Можно убрать Сетку.

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

3.      В Окне группы напишите свой вопрос, например:

Заголовок в тексте можно выделить с помощью тега:

4.      Выбрать кнопку Переключатель и растянуть еѐ в окне группы. Записать ответ.

5.      Повторить необходимое число раз. Записать ответы.

Установить связь Переключателей с ответами с любой ячейкой.

Выделить ПКМ Переключатель1 (А у нас это уже 1-ый ответ) –  Формат объекта - Элемент управления – Связь с ячейкой (например Н5). 

Переключатели 2,3 (Ответ 2,3) автоматически связались с ячейкой Н5

Если не уверенны, то проверти.

Теперь выбирая ЛКМ правильный ответ, в ячейке Н5 будет меняться цифра от 1 до 3. Попробуйте

7.      Ячейке Н8 запишем формулу: 

Если в ячейки Н5 стоит 2; то дать 1 балл; если нет, то 0 баллов.

Почему 2? 

Т.к. правильный ответ в этом вопросе занимает 2-ую строку.

8.      Сделайте по аналогии ещѐ два вопроса.

 

Далее будем подводить результат

 

Создадим кнопку, нажимая на которую происходит подсчет баллов и результат о сдачи зачета (создадим макрос)

9.      Создать макрос (Сервис – Макрос – Начать запись), дать имя РЕЗУЛЬТАТ. Сделать те шаги, которые будет выполнять макрос. 

Выделить ячейки и задать им черный цвет шрифта

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

10.  Запустить панель инструментов Формы. Щелкните на элементе Кнопка и растяните еѐ. 

11.  Если выходит окно сразу Назначить макрос объекту, то это и сделать,     а если нет, то ПКМ по Кнопке – Назначить макрос – РЕЗУЛЬТАТ – ОК.

12.  Не забудьте переименовать Кнопку. ПКМ – Изменить текст.

13.  Создадим еще одну кнопу, которая будет стирать этот результат – кнопка ОЧИСТИТЬ Очистки результатов добьемся, изменяя цвет шрифт на белый

 

 

Решение:

1.      Удержано: G11 = F11 * 14%,     Формулу скопировать вниз

2.      К выдаче: H11 = F11 – G11,                   Формулу скопировать вниз

3.      Выделить блок ячеек F11:H28  Формат – Ячейки  – Число – Денежный – рубли.

4.      Выделить заголовок в таблице А10:Н10                      Данные – Фильтр – Автофильтр

Условие -  

Ctrl

5.      Выделить ячейки содержащие ФИО, удерживая   выделить блок К выдаче. Вставка – Диаграмма. Дать название диаграмме.

 

 

Решение:

1.      Цена розничная: Е10 = С10 * $D$10,                Формулу скопировать вниз

2.      Прибыль от реализации: F10 = (E10 – C10) * B10 Формулу скопировать вниз

3.      Итого: F15 = СУММ (F10:F14)

Ctrl

4.      Выделить блок ячеек С10:С14, удерживая   выделить поочередно блоки (E10:E14) и (F10:F15). Формат – Ячейки  – Число – Денежный – рубли.

5.      Выделить заголовок в таблице А9:F9  Данные – Фильтр – Автофильтр – Условие – 

 

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

 

 

Решение:

1.      Прибыль от реализации: F11 = (E11 – D11) * C11 Формулу скопировать вниз

2.      Итого: F16 = СУММ (F11:F15)

3.      Анализ: G11 =   Формулу скопировать вниз

Ctrl

4.      Выделить блок ячеек F11:F15, удерживая   выделить блок B11:B15.  Вставка – Диаграмма. Дать название диаграмме.

 

 

Решение:

1.      Остаток: G11 = E11 – F11                      Формулу скопировать вниз

2.      Выручка: H11 = (D11 – C11) * F11       Формулу скопировать вниз

3.      Примечание: I 11 =   Формулу скопировать вниз

Ctrl

4.      Выделить блок ячеек С11:D16, удерживая   выделить блок G11:H16. Формат – Ячейки  – Число – Денежный – рубли.

Ctrl

5.      Выделить блок ячеек H11:H16, удерживая   выделить блок B11:B16.  Вставка – Диаграмма. Дать название диаграмме

 

 

Решение:

 

1.      Щелкнуть ПКМ по любой ячейке 12 строки – Добавить ячейки … –  Строку. Введите свои данные

2.      Выделить блок ячеек A10:F15. Данные – Сортировка –  

3.      Оклад + Премия: G10 = F10 + $G$5.     Формулу скопировать вниз

4.      Выделить блок ячеек F10:G15. Формат – Ячейки  –  Число – Денежный – рубли.

5.      Выделить блок ячеек F10:F15, удерживая Ctrl  выделить блок B10:B15.  Вставка – Диаграмма. Дать название диаграмме

Задача 70

 

 

 

1.      Щелкнуть ПКМ по любой ячейке 12 строки – Добавить ячейки … –  Строку. Введите данные

2.      Рубли: D13 = C13 * $D$9.                      Формулу скопировать вниз на ячейки D14 и D15.

       D18 = C18 * $D$9.                   Формулу скопировать вниз на ячейки D19 –  D21.

3.      Итого доходы: С16 = СУММА(С13:С15),   D16 = CУММА(D12:D15)

Итого расходы: С22 = СУММА(С18:С21) Формулу скопировать вправо на ячейку D22 Остаток: C23 = C16 – C22. Формулу скопировать вправо на ячейку D23.

Ctrl

4.      Выделить блок ячеек С13:С16, удерживая   выделить блок С18:С23. Формат – Ячейки  –  Число – Денежный – $ Английский.

Ctrl

Выделить блок ячеек D13:D16, удерживая   выделить блок D18:D23. Формат – Ячейки  –  Число – Денежный – рубли

5.      Выделить блок ячеек D13:D15, удерживая Ctrl  выделить блок B13:B15.  Вставка – Диаграмма. Дать название диаграмме

 

 

Решение:

 

1.      Средний балл: F8 = СРЗНАЧ(B8:E8)                            Формулу скопировать вниз

2.      Сумма баллов по предмету: В14 = СУММА(В8:В13)            Формулу скопировать вправо

3.      Максимальный балл: В15 = МАКС(F8:F13)

4.      Кол-во «5» по всем предметам: B16 =  

Ctrl

5.      Выделить блок ячеек F8:F13, удерживая   выделить блок А8:А13.  Вставка –

Диаграмма. Дать название диаграмме

72

 

1.      Общий балл: G18 = СУММА(С18:F18)                                    Формулу скопировать вниз

2.      Средний балл: С14 = СРЗНАЧ(С18:С20)                                 Формулу скопировать вправо

3.      Отметка о зачислении: H18 =  

 

1. Выделить блок ячеек A13:F18. Данные – Сортировка – 2. Сумма: С21 = СУММА(F13:F18)

Ctrl

3.      Выделить блок ячеек F13:F18, удерживая   выделить С21. Формат – Ячейки  –  Число – Денежный – рубли

4.      Сумма на отчисления: С24 =  

Задание 74 Файл «Кредит»

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

Нужно найти работников,  у которых имеется либо задолженность по потребительскому кредиту, либо по кредиту на жилищное строительство, либо по обоим видам кредита сразу,  и удержать с них в счет погашения кредита 10% от начисленной им суммы.

 

№ п.п	Ф.И.О.	Начисленно руб.	Задолжность по кредитам		Удержано руб.
			Потреб. кредит	Жил. строит-во
1	Морозов И.	4000	2400
2	Кирилов К.	1600
3	Смирнова О.	4500	3000	25000
4	Смирнов С.	2800	6000
5	Водопьянов В.	3200		18000
6	Иванов И.	9000	4000	5000
7	Петров И.	7500	10000
8	Сидоров С.	3200		6000
9	Тимофеев Т.	2100
Итого

 

Задание 75  Файл «Тестирование» Создать ведомость учета тестирования. Оформить по предложенному ниже образцу. 

Тестирование проводится по 5 предметам. Оценка ведется по 10-бальной системе. 

Условия поступления в институт следующие: 

Если средний балл абитуриента > 8,5, то он считается зачисленным, при среднем балле от 7,5 – 8,5 абитуриент проходит  дополнительный письменный  экзамен и при уровне ниже 6,5 баллов абитуриент не поступает.

 

ТЕСТИРОВАНИЕ 2007 год

№	Ф.И.О.	Мате матика	Физика	Русский	Литература	Ин.яз	Средний балл	Данные о зачислении
1	Фролов Р.	10	9,5	8	9	10
2	Зотов С.	5	6,5	5	5	6,5
3	Маркина С.	9	9	8,7	9	9,5
4	Вербова К	7,5	6	5	5	6
5	Буров Т.	7	8	8	8,7	9
6	Буров А.	10	10	10	10	9,5

 

Отсортируйте записи по алфавиту

 

 

 

77

 

Задача 80

 

82

 

Задача 84

 

87

Вы – владелец издательства «Кузнецкие вести». Используя возможности Excel: 1.             составьте следующую таблицу:

 

Наименование	Год издания	Закупочная стоимость (р)	Имеется на складе (шт)	Общая стоимость (р)	Розничная цена (р)	Заявка (шт)	Продано (шт)	Сумма продажей (р)	Чистая прибыль (р)	Налог с чистой прибыли (р)	Доля прибыли (%)
Microsoft Windows 98 : наглядно и конкретно	1999
Microsoft Internet Explorer 4 : наглядно и конкретно	2001
Microsoft Office XP: наглядно и конкретно	2005
Microsoft Excel XP: наглядно и конкретно	2005
Microsoft Access XP: наглядно и конкретно	2005
Microsoft Word XP: наглядно и конкретно	2005
									Сумма

 

2.              заполните столбцы ˝Закупочная стоимость˝, ˝Имеется на складе˝ - случайными числами:

50 руб. 00 коп. ≤ ˝Закупочная стоимость˝ ≤ 100 руб. 00 коп.,  ˝Имеется на складе˝ ≤ 1000 штук. 

Столбец ˝Заявка˝ - случайными числами, ЕСЛИ на складе не 0: 1 ≤ ˝Заявка˝ ≤ 1000 штук;  в противном случае заявка не принимается

(----). 

Для того чтобы задать случайные числа воспользуйтесь формулой СЛЧИС. (ВНИМАТЕЛЬНО ознакомьтесь с формулой).

3.              заполните формулой столбец ˝Общая стоимость˝;

4.              ЕСЛИ заявка, на какой либо товар превышает количество этого товара на складе, то продается весь данный товар;

5.              розничная цена превышает закупочную стоимость на 25%;

6.              при расчете суммы  продаж учтите льготы покупателям:

ü   за покупку более 100 единиц товара предоставляется  скидка 10% 

ü   за покупку более 20 единиц – скидка 5%

7.              в примечании к столбцу ˝Розничная цена˝ укажите условия скидок;

8.              рассчитайте чистую прибыль;

9.              налог с чистой прибыли составляет 15%;

10.           рассчитайте долю прибыли каждого печатного издания в %;

11.           переименуйте ˝Лист1˝ на ˝Склад˝.

 

12.           На ˝Листе 2˝ по данным листа ˝Склад˝, используя связь между листами, создайте и заполните до конца таблицу и назовите еѐ  ˝Остаток˝:

 

Наименование	Имеется на складе (шт)	Продано (шт)	Остаток (шт)

 

13.           Для заказа отсутствующих товаров оформите на ˝Листе 3˝ таблицу ˝Заявка,˝ используя данные с таблицы листа ˝Остаток˝ (т.е. сохраняя связи с листами) и назовите лист ˝Заявка˝,

 

Наименование	Остаток

 

14.           На ˝Листе 4˝ постройте гистограмму, показывающую доли прибыли каждого из товаров.

Переименуйте ˝Лист 4˝ в ˝Прибыль˝. Оформите диаграмму идеально; если сможете, вместо столбиков в диаграмму вставьте любой рисунок.

15.           Результаты работы сохраните D: \ Мои документы \ Олимпиада \ КОД \ Склад.xls

Для заметок

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

  

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Сборник задач по курсу “Microsoft EXCEL”

 

 

Разработала Князева Э.В. – учитель информатики 

МБОУ «СОШ №4» 

 

 

Кемеровская обл., г. Новокузнецк, ул. Тольятти, 30А,    т. (3843) 77-32-39