Простейшие манипуляции
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 составьте расчетную схему для определения ежемесячных доходов по вкладам в банке при простом и сложном проценте. Формулы для расчетов (S0 – начальная сумма вклада, a – годовой процент): через i месяцев сумма вклада составит при простом проценте Si = Si –1 + (a / 12)S0, при сложном Si = Si –1 + (a / 12)Si –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 на всю область Графика работ. После того, как условный формат будет создан, поменяйте даты в списке искомых дат (их можно ввести меньше первоначального количества). Посмотрите, как будет меняться вид рабочего графика. ©2015 arhivinfo.ru Все права принадлежат авторам размещенных материалов.
|