Простейшие манипуляции

3.1.1. Выделите на экране столбцы от А до L. Установите масштаб окна Excel так, чтобы на экране помещались эти столбцы (команда Вид ® Масштаб ® По выделению).

3.1.2. Введите на Лист1 информацию, указанную на рис. 3.1.1. Установите в ячейке В2 следующий формат: размер шрифта – 26 пт., цвет шрифта – красный, цвет заливки – желтый, выравнивание по центру, разрешен перенос по словам в пределах ячейки (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание). Скопируйте этот формат (но не содержание ячейки) на блок С3:Е4 (команды Правка ® Копировать, Правка ® Специальная вставка). Разместите в ячейках D4 и Е4 пятые степени чисел из D3 и Е3 соответственно.

Рис. 3.1.1

3.1.3. Присвойте ячейкам А5 и F3 (см. рис. 3.1.1) имена соответственно Делимое и Делитель (команда Вставка ® Имя… ® Присвоить…). Введите в ячейку В5 формулу: =Делимое/Делитель. Укажите ячейки, влияющие на значение в В5 (команда Сервис ® Зависимости… ® Влияющие ячейки). Введите делитель, при котором в В5 результатом будет 50.

3.1.4. Скопируйте блок А2:Е5 (см. рис. 3.1.1) на Лист2, начиная с ячейки Y12345 (для быстрого перехода в удаленную ячейку следует после команды Правка ® Копировать ввести в адресное поле адрес нужной ячейки и нажать <Enter>).

3.1.5.Введите информацию с рис. 3.1.2. на Лист1. Отформатируйте таблицу: А9:D9 – жирный шрифт, выравнивание по центру; В10:В16 и D10:D16 – денежный формат; В10:D16 – выравнивание по центру; D10:D16 – красный шрифт, разлиновка – как на рис. 3.1.2 (команда Формат ® Ячейки…, вкладки Шрифт, Выравнивание, Число, Границы). Присвойте имена ячейкам F10 и G10 (команда Вставка ® Имя… ® Создать…) и блокам В10:В15 и С10:С15 (команда Вставка ® Имя… ® Присвоить…). Заполните столбец Выплата по формуле:

="Премия" * "Коэффициент премии" –
"Ошибки" * "Штраф за ошибку"

Введите формулы итогов в ячейки В16 и D16 (кнопка Автосумма или команда Вставка ® Функция…).

Рис. 3.1.2

3.1.6. Присвойте листам, на которых выполнялись задания 3.1.1–3.1.5, названия Формат и Копия, свободному листу Вашей рабочей книги – имя Сложение.

3.1.7. Введите на лист Сложение данные, указанные на рис. 3.1.3. Разместите в В10 их сумму, используя только заполненные числами ячейки (кнопка Автосумма или команда Вставка ® Функция…, выделение группы блоков через <Ctrl + буксировка).

Рис. 3.1.3.

3.1.8. Разлинуйте таблицу в соответствии с рис. 3.1.4 (команда Формат ® Ячейки…, вкладка Границы). Заполните таблицу сложения с масштабом в соответствии с формулой: ((Х+ Y) *Масштаб) (пользуйтесь частичным и полным закреплением адресов). Масштабный коэффициент – в ячейке В19. Сосчитайтесумму диагональных элементов таблицы.

Рис. 3.1.4

3.1.9. Повторите задание 3.1.8, присвоив имена Х и Y блокам аргументов (через адресное поле или командой Вставка ® Имя…) и используя их в формулах вместо частичного закрепления адресов. Новую таблицу расположите, начиная с ячейки А21. Сравните вид формул и результаты в обоих заданиях.

3.1.10. Составьте на новом листе две таблицы умножения по аналогии с рис. 3.1.4. Заполните одну из них, используя частичное закрепление адресов, а другую – имена блоков аргументов (для аргументов следует использовать имена, не совпадающие с теми, которые были использованы в задании 3.1.9). Сосчитайте сумму диагональных элементов в полученной таблице.

3.1.11. Присвойте чистому листу имя Заполнение. Введите в ячейки А1 и В1 число 2, в ячейки А2 и В2 – число 2,5. Заполните блок А1:А10 по арифметической, а блок В1:В10 по геометрической прогрессии (следует выделить нужный блок и далее воспользоваться командой Правка ® Заполнить ® Прогрессия…). Выделите значения, которые больше пяти, голубой заливкой (команда Формат ® Условное форматирование…).

3.1.12. Петров работает в марте по четным дням, Иванов – по нечетным, Сидоров – каждый день. Составьте и красиво оформите календарный график их работы (автозаполнение протяжкой). Выделите условным форматированием рабочие дни в период школьных каникул (с 24 марта).

3.1.13. Первоначальный запас рыбы в озере 1200 тонн. Естественный ежегодный прирост составляет 15 %. Ежегодный план отлова – 300 т. Порог, после которого запас рыбы невосстановим, составляет 400 т. Рассчитайте запас рыбы в озере на ближайшие 15 лет. С помощью условного форматирования выделите красной заливкой годы, в которые запас рыбы окажется меньше критического порога. Для лет, в которые запас рыбы полностью иссякнет (отрицательные числа), дополнительно закажите красный шрифт. Составьте вспомогательную таблицу, отражающую естественное восстановление рыбного запаса при условии запрета на отлов, начиная с года, предшествующего критическому. С помощью условного форматирования отметьте зеленой заливкой годы, в которые запас рыбы полностью восстановится.

3.1.14. Перед началом строительства была рассчитана стартовая цена квартиры в долларах и собраны начальные взносы пайщиков. Впоследствии оказалось, что для завершения стройки не хватает 0,5 млн рублей. Рассчитайте долги пайщиков с учетом этого факта. Расположите и отформатируйте информацию в соответствии с рис. 3.1.5, введите недостающие константы и формулы.

Рис. 3.1.5

3.1.15. Оформите ведомость по закупке товаров, включающую 8–10 наименований (рис. 3.1.6). Введите свои значения в графы 2 и 3, расчетные формулы – в графу 4 и строки "Итого" и "Разница". Добавьте с помощью форматирования указатели единиц измерения (шт., пачки, рубли и т. п.). Подберите количество каждого товара так, чтобы итоговая сумма не превышала заданной, но была максимально близка к ней.

Рис. 3.1.6

3.1.16. Заказчик оценил всю работу в 10000 долларов и выдал аванс в размере 4000 долларов. Этот аванс был распределен между работниками произвольным образом (кто сколько попросил). Требуется по окончании работы распределить оставшиеся 6000 долларов с учетом ранее выданного аванса, коэффициента трудового участия (КТУ) и коэффициента профессионального класса.

Подсказка

Расположите и отформатируйте информацию в соответствии с рис. 3.1.7, введите недостающие формулы, смысл которых описан ниже:

"Заработано" = "Всего заработано" * ("КТУ работника" / "Итого КТУ") * "Коэффициент за класс"

"Корректировка" = "Заработано" * ("Всего заработано" / "Итого заработано")

Формулы в строке "Итого" и столбце "Выдать остаток" составьте сами.

Рис. 3.1.7

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

Формулы для расчетов (S₀ – начальная сумма вклада, a – годовой процент): через i месяцев сумма вклада составит при простом проценте S_i = S_{i –1} + (a / 12)S₀, при сложном S_i = S_{i –1} + (a / 12)S_{i –1}.

Рис. 3.1.8

3.1.18.На рис. 3.1.9 представлены сведения, необходимые для начисления зарплаты сотрудникам фирмы. С помощью функции ВПР() перенесите в блок Е3:Е7 тарифы, соответствующие разряду работников, а также заполните графу "Начислено". Тарифы занесены в справочную таблицу (блок А2:В6).

Подсказка

Функция ВПР() находится в Мастере функций в категории Ссылки и массивы. Ее следует ввести в ячейку Е3 со следующими значения аргументов:

· Искомое_значение – D3.

· Табл_массив (где искать строку, начинающуюся со значения из D3) – $А$3:$В$6.

· Номер_столбца (в каком столбце найденной строки искать нужное данное) – 2.

· Диапазон_просмотра – 0 (в блоке $А$3:$В$6 ищется строка, в которой первое значение точно совпадает с D3).

Далее формула копируется на остальные ячейки этой графы.

Рис. 3.1.9

3.1.19. На рис. 3.1.10 изображен рабочий график сотрудников фирмы. Требуется найти в нем людей, которые работали в интересующие нас дни.

Рис. 3.1.10

Подсказка

В этой задаче удобно использовать команду Формат ® Условное форматирование… для выделения интересующих нас дней. При задании образца для форматирования в параметрах этой команды следует использовать функцию ГПР().

1. Установите курсор на ячейку В4 и вызовите команду Формат ® Условное форматирование...

2. В первой строке окна команды три текстовых поля. Установите в них соответственно:

Формула; Равно; =ГПР(B4;$D$1:$G$1;1;0)

Формулу наберите вручную, так как Мастер функций в этом окне недоступен. Аргумент В4 не должен быть закреплен. Смысл аргументов функции ГПР():

· Искомое_значение – В4 (какую дату ищем).

· Таблица –$D$1:$G$1 (таблица, в которой ведется поиск столбца, начинающегося с того же значения, что и в В4).

· Номер_строки – 1 (из какой строки найденного столбца следует брать значение функции ГПР()).

· Диапазон_просмотра – 0 (определяется тип поиска: 0 – в первой строке ищется точное совпадение с В4, 1 – в первой строке ищется ближайшее значение, которое меньше значения в В4 или равно ему).

Формат ячеек, удовлетворяющих этому условию, задайте сами.

3. С помощью команд Правка ® Копировать и Правка ® Специальная вставка… скопируйте формат из В4 на всю область Графика работ.

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