Указания

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

2 . Введите в ячейку С4 формулу = В4/С2. Для того, чтобы ввести формулы без изменения адреса С2 в другие ячейки заполнением или копированием необходимо в формуле относительный адрес ячейки С2 заменить на абсолютный. Для этого установите курсор в строке формул после адреса С2 и нажмите клавишу F4. После этого формула поменяет вид на следующий: =В4/$С$2. Нажмите клавишу Enter.

Введите формулы в ячейки С3:С12 заполнением. Не забудьте определить формат – денежный, обозначение – евро, число десятичных знаков – два.

3. Для построения гистограммы выделите ячейки, значения в которых войдут в гистограмму – ячейки А3:А12 и ячейки С3:С12.

Для того, чтобы выделить несмежные ячейки сначала выделите ячейки столбца А, а затем при нажатой клавише Ctrl – ячейки столбца С.

Нажмите кнопку Вставка – Гистограмма на панели инструментов. Постройте ее и сохраните на существующем листе.

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

Задание 17

Создайте на новом листе таблицу и переименуйте его в задание 17. Постройте гистограмму прибыли и определите прогноз прибыли в ноябре и декабре.

Указания

Постройте гистограмму "Месяц-Прибыль".

Выполните на столбцах гистограммы команду контекстного меню Добавить линию тренда.

В открывшемся окне выберете линию тренда "Полиноминальная" и установите показатель степени "3", а в поле "Прогноз" установите значение "вперед на 2". Нажмите кнопку Ок.

Задание 18

Создайте на новом листе таблицу и переименуйте его в задание 18.

Выполните следующие условия.

1. Для столбца "Дата рождения" установите проверку вводимых значений: интервал вводимых значений - от 01.01.20 до 01.01.98.

При ошибке установите вывод сообщения "Дата рождения не верна!"

2. Для столбца "Разряд" примените условное форматирование:

4 сигнала светофора.

У казания

1. Выделите столбец В и выполните команду меню Данные-Проверка данных. Установите значения согласно рисунку.

2. Перейдите на вкладку Сообщение об ошибке и в поле "Сообщение" введите текст "Дата рождения не верна!". Нажмите кнопку Ок.

Проверьте работу проверки даты, введя неверную дату, например: 01.01.01.

3 . Выделите столбец С и выполните команду меню Главная-Условное форматирование. Установите в полях значения согласно рисунку

4. Проверьте работу условного форматирования, меняя разряды работников.

Задание 19

Создайте на новом листе таблицу переименуйте его в задание 19.

Значения в столбцах с С по G рассчитайте по приведенным ниже формулам.

Столбец С:

если Оклад<4000, то Премия=1600,

иначе Премия=Оклад*0,4

Столбец D: =(Оклад+Премия)*0,15

Столбец E: =Оклад+Премия+Уральские

Столбец F: =Всего*0,13

Столбец G: =Всего-Подох. Налог

У казания

1. Для расположения надписи в первой строке выделите первую строку и выполните на выделении команду контекстного меню Формат ячеек. Перейдите на вкладку Выравнивание и установите ориентацию текста 90 градусов. Нажмите кнопку Ок.

2 . Для ввода формулы в ячейку С используется логическая функция Если.

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

В окне "Мастер функций" установите категорию "Логические" и выберите функцию ЕСЛИ

Введите выражения в поля "Лог_выражение" и "Значение" согласно приведенному ниже рисунку.

Нажмите кнопку Ок. Затем введите остальные формулы.

Задание 20

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

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

На следующем листе создайте аналогичную таблицу продаж товаров во 2 квартале и переименуйте его в задание 20-2.

На новом листе создайте таблицу по продажам за 1 полугодие и переименуйте его в задание 20-3.

Значения в столбцах "1 кв." и "2 кв." рассчитайте по формулам. Текст в ячейках D2-D5 – результат использования формул с текстом.

Указания

1. Значения в столбце G рассчитайте по формуле МАКС, которая находит максимальное значение из нескольких ячеек.

Для этого выделите ячейку G3, нажмите кнопку Вставка функций, в категории "Статистические" выберите функцию МАКС и нажмите кнопку Ок.

После открытия окна "Аргументы функции" введите диапазон ячеек, из которых надо выбрать максимальное значение (B3-D3), для чего выделите мышью эти ячейки (их адреса появятся в поле "Число1").

Нажмите кнопку Ок.

2. Значения в столбце F рассчитайте, используя вложенную логическую функцию. Логика формулы может быть следующей: Если значение Количество=значению в январе, то значение январь, иначе, если Количество=значению в феврале, то значение февраль, иначе значение март.

Для ввода формулы выделите ячейку F3, нажмите кнопку Вставка функций, в категории "Логические" выберите функцию ЕСЛИ и нажмите кнопку Ок.

После открытия окна "Аргументы функции" введите в поля элементы формулы согласно рисунку.

В поле логическое выражение (1) введите условие G3=B3 (щелкните ячейку G3, наберите с клавиатуры знак равенства, затем щелкните ячейку B3).

В поле Значение_если_истина (2) введите абсолютный адрес ячейки с названием месяца. Для этого щелкните по ячейке B1, затем нажмите клавишу F4.

Установите курсор в поле Значение_если_ложь (3), и снова вызовите функцию ЕСЛИ, нажав по кнопке с надписью ЕСЛИ (4).

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

3. Растяните формулу ячейки F3 на диапазон F3:F6.

4. В ячейку H3 введите формулу =G3/E3. Установите для столбца H процентный формат, нажав кнопку Процентный формат на панели инструментов.

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

6. После создания листа задание 20-3 для заполнения таблицы продаж за 1 полугодие введите формулы в столбцы A, B, C и D.

Установите курсор в ячейку А2, введите знак "=", перейдите на лист задание 20-1, щелкните по ячейке А3 и нажмите клавишу Enter

Далее выделите ячейку В2, введите знак "=", перейдите на лист задание 20-1, щелкните по ячейке Е3 и нажмите клавишу Enter.

Введите аналогичные формулы в ячейку С2 с листа задание 20-2.

7. Формула в ячейке D3 должна иметь текст.

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

Соответственно формула в ячейке D3 должна иметь следующий вид: =A2&" - "&B2+C2&" шт.".

8. Скопируйте ячейки А2:D2 в ячейки А3:D5.

Задание 21

Определите для каких наборов значений переменных х и у Р будет истинно?

Р = х+у > х-у или х+2у > 0

х	-1	-1	1	1
у	1	0	-1	0

Указания

Введите в ячейки А1:Е2 нового листа с именем задание 21 приведенную таблицу.

Выделите ячейку В3 и вызовите функцию ИЛИ. Введите в поля выражения согласно рисунку и нажмите кнопку Ок.

Аналогично решите задачу для других наборов значений х и у (формулы в ячейки С3:Е3 можно ввести заполнением).

Результат решения показан рисунке:

Задание 22

Защитите от изменения ячейки А1 и А2 таблицы задания 21.

Указания

Выделите весь лист и выполните на выделении команду контекстного меню Формат ячеек. Перейдите на вкладку Защита, снимите флажок "Защищаемая ячейка" и нажмите кнопку Ок.

В ыделите ячейки А1 и А2 и выполните на выделении команду контекстного меню Формат ячеек. На вкладке Защита установите флажок "Защищаемая ячейка" и нажмите кнопку Ок.

Выполните команду меню Рецензирование-Защитить лист. При необходимости введите пароль и нажмите кнопку Ок.

Проверьте работу защиты, изменив содержимое ячейки А1.

Для снятия защиты выполните команду меню Рецензирование-Снять защиту листа.

Задание 23

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

Указания

Необходимо создать четыре макроса, каждый из которых вставляет в выделенную ячейку текст: "отлично", "хорошо", "удовлетворительно" и "неудовлетворительно". Макрос – это записанная последовательность команд, которая может быть воспроизведена.

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

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

1). Перейдите на вкладку Файл, а затем выберите пункт Параметры.

2). Во вкладке Настройка ленты в группе Основные вкладки установите флажок возле пункта "Разработчик", а затем нажмите кнопку ОК.

Теперь можно приступать к работе.

1. Выделите любую ячейку листа и выполните команду меню Разработчик- запись макроса.

В поле "Имя макроса" введите имя макроса, например, "отл" и нажмите кнопку Ок.

Наберите на клавиатуре текст "отлично", нажмите клавишу Enter и выполните команду меню Разработчик-Остановить запись.

Аналогично создайте другие макросы.

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

В открывшемся окне выделите имя макроса и нажмите кнопку Ок

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

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

Аналогично создайте другие кнопки.

Задание 24

Создайте таблицу по приведенному образцу и переименуйте лист в задание 24.

1. Вставьте со связыванием таблицу в документ Word.

2. Скопируйте таблицу на следующие три листа с именами задание 24-1, задание 24-2, задание 24-3.

3. На первом из них, используя автофильтр, выведите список работников, имеющих зарплату в январе больше 15000 руб.

4. На втором, используя расширенный фильтр, выведите список работников, старше 35 лет или имеющих зарплату за квартал менее 18000 руб.

5. На третьем, используя команду Итоги, рассчитайте зарплату по отделам, то есть выведите общую зарплату 1-го отдела , 2-го отдела и 3-го отдела по месяцам.

Указания

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

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

В открывшемся окне вставьте скопированную таблицу и нажмите в пустой области экрана.

2. Скопируйте таблицу, перейдите на лист задание 24-1 и на ячейке А1 выполните команду контекстного меню Вставить.

3. Выделите любую ячейку в таблице и выполните команду меню Данные-Фильтр.

Нажмите на кнопку раскрывающегося списка в столбце D и выберите значение пункт Числовые фильтры  больше… (см. рисунок).

Введите значения в поля согласно рисунку и нажмите кнопку Ок

4. Скопируйте таблицу на следующий лист с именем задание 24-2.

Добавьте два столбца "Квартал" и "Возраст".

Введите в столбец "Квартал" формулы суммирования зарплат =СУММ(D2:F2),

в столбец "Возраст" – формулы расчета возраста работника =ГОД(СЕГОДНЯ()-B2)-1900.

Задайте для столбца "Возраст" числовой формат с числом десятичных знаков равным 0.

5. Вставьте копию заголовков таблицы в строку 11. Теперь в строках 12 и 13 могут задаваться критерии фильтра.

В ячейку G12 введите: <18000, а ячейку Н13 введите: >35.

Е сли условия фильтра записываются в разных строках (как в данном примере), то это соответствует функции ИЛИ. Если условия фильтра записываются в одной строке, то это соответствует функции И.

Выполните команду меню Данные-Дополнительно и введите в поля адреса ячеек в соответствии с рисунком и нажмите кнопку Ок.

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

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

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

Выделите любую ячейку таблицы и выполните команду меню Данные-Промежуточные итоги.

Установите значения полей в окне Промежуточные итоги согласно рисунку.

Нажмите кнопку Ок.

Задание 25

Найдите корень уравнения Sin(b) + 0,21b - 1,1 = 0.

Указания

Пусть значение b будет в ячейке А1. Обратите внимание, что в ячейке должно быть значение, то есть число, а не имя переменной b.

В ячейку В1 введите формулу: = SIN(A1) + 0,21*A1 - 1,1

Выделите ячейку В1 и выполните команду меню Данные-Поиск решения.

Примечание: В том случае, если этой кнопки нет, выполните следующие операции:

В ыберите в меню Файл пункт Параметры. В открывшемся окне перейдите на вкладку Надстройки, Выберите в списке внизу Надстройки Excel и нажмите кнопку Перейти...

В появившемся окне поставьте галочку напротив “Поиск решения” и нажмите кнопку OK.

Введите в поля значения согласно рисунку и нажмите кнопку Найти решение.

После выполнения подбора параметра будет получен результат, приведенный на рисунке, то есть корень уравнения b = 1,067.

Задание 26

Найти все корни уравнения х² + 3х – 7 = 0

Указания

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

Для приближенного нахождения корней можно построить график функции у = х² + 3х – 7 и по графику определить приближенные значения х, при которых у близко нулю.

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

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

В данном примере значения х примерно равны -4 и 2.

Проведите подбор параметра при этих значениях х.

Для этого можете выделить ячейку В2 и для нее провести подбор параметра. Затем то же самое выполнить для ячейки В8.

Значения корней уравнения будут находиться в ячейках А2 и А8.