Пособие для самостоятельного изучения программ из пакета Microsoft Office Excel 2007 (Электронная таблица)

ГБОУ СПО ЖК № 52

Пособие

для самостоятельного изучения программ

изпакета Microsoft Office

Excel 2007

(Электронная таблица)

Автор – Вассергольц И.М.

г. Москва

2014 г.

Оглавление

Стр.

Часть 1. Начальное знакомство с программой Excel3

Окно программы Excel 20073

Перемещение по электронной таблице.4

Лист Рабочей книги5

Ширина столбцов и высота строк6

Выделение ячеек7

Перемещение и автозаполнение ячеек9

Относительные и абсолютные ссылки (адресация в таблицах)11

Построение графиков и диаграмм16

Группировка ячеек20

Часть 2. Задания для закрепления навыков работы с программойExcel21

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

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

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

Форматирование ячеек28

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

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

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

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

Часть 1. Начальное знакомство с программойExcel

Окно программыExcel 2007

Запустить программу MicrosoftOfficeExcel 2007 можно:

- кнопкой «Пуск» (на Рабочем столе), выбрав ее в списке программ MicrosoftOffice;

- открыв документ, созданный этой программой - (листMicrosoft OfficeExcel) или предыдущими версиями программы (лист программы MicrosoftOffice Excel 97-2003 - ).

Запустите программу Excel 2007 через кнопку «Пуск», выбрав ее в списке программ MicrosoftOffice;.

После запуска программы MicrosoftOfficeExcel 2007, открывается окно программы, основными элементами которого являются:

- строка заголовка, где появляется название открытого файла (книги). По умолчанию (автоматически) для вновь создаваемого файла это название – «Книга N..»;

-

Строка формул

- кнопка Office, которая позволяет вызвать команды для работы с документами;

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

-строка формул;

-окно документа – лист Рабочей книги;

-прокрутки (на горизонтальной прокрутке расположены ярлыки рабочих листов книги);

-строка состояния, которая позволяет выбрать режим просмотра документа (в том числе – масштаб);

- лента команд, связанных с одним из пунктов программного меню или командой (лента может сразу находится в развернутом виде в окне программы после ее запуска).

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

Ч тобы свернуть ленту команд надо установить «флажок» около команды «свернуть ленту» в диалоговом окне «Настройка панели быстрого доступа».

На ленте команд команды сгруппированы по разделам (вкладкам). Внизу раздела всегда естькнопка для вызова диалогового окна этой группы команд.

Сверните ленту команд (если она была развернута).

Вызовите ленту команд пункта программного меню «Главная», щелкнув по названию правой кнопкой «мышки».

Вызовите диалоговое окно вкладки «Выравнивание».

Примечание. При вызове диалогового окна, лента команд свернется.

Отмените вызов диалогового окна.

Разверните ленту команд, щелкну «мышкой» на команде «Свернуть ленту», чтобы погасить установленный «флажок».

Перемещение по электронной таблице.

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

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

Из любой ячейки рабочего листа можно вернуться к первой – А1, нажав комбинацию клавиш Ctrl+Home.

Чтобы узнать имя последней ячейки рабочего листа, надо найти номер последней строки таблицы (комбинация клавиш Ctrl+стрелка вправо) и имя последнего столбца (комбинация клавиш Ctrl+ стрелка вниз).

Размер листа Рабочей книги – 16384 столбца и 1048576 строк. Т.е. пользователю доступны 17.179.869.184 ячейки.

Найдите последнюю ячейку на рабочем листе.

Вернитесь к первой ячейке – А1.

Лист Рабочей книги

Л ист Рабочей книги - электронная таблица. Количество рабочих листов в книге ограничено объемом доступной оперативной памяти.По умолчанию новая Рабочая книга содержит 3 листа.

Листы книги можно удалять, вставлять, перемещать (внутри одной книги или в другую книгу – файл), копировать и переименовывать, используякоманды контекстно-зависимого меню, которое появляется при щелчке правой кнопкой «мышки» на ярлыке листа Рабочей книги.

Удалите из Рабочей книги лист 2.

Вставьте в книгу четыре новых листа после листа 1.

Переместите лист 1 в конец книги (лист 1 должен быть последним в открытой книге, после листа 3).

Скопируйте лист 1 два раза.

Расположите копии по порядку после листа 1 (копии листа имеют порядковый номер, указанный в скобках).

Переименуйте лист 1(2) на имя «новый лист 2».

П ереименуйте лист, стоящий первым в книге, дав ему имя «Начало», и сделайте его ярлык красным.

Сохраните файл (не закрывая его) под именем «Начало» в папке «Мои документы» (или в другом, выбранном для этого месте).

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

Ширина столбцов и высота строк

Ширина столбца (максимально возможная) – 255 знаков, высота строки (максимально возможная) – 409 пунктов. Общее количество знаков, которые можно разместить в ячейке – 32767.

Все ячейки первоначально имеют одинаковый размер. Часто вводимая информация в ячейке «не умещается» - ее не видно полностью, если соседняя ячейка (справа) не пустая.

Чтобы изменить ширину столбца, надо выделить этот столбец, и выбрать команду меню Главная\ Ячейки\ Формат\ Ширина столбца.

Для автоподбора ширины столбца надо воспользоваться командой Главная\ Ячейки\ Формат\ Автоподбор ширины столбца или щелкнуть дважды на правой границе заголовка выделенного столбца.

Чтобы изменить высоту строки, надо выделить эту строку и выбрать команду меню Главная\Ячейки\Формат\Высота строки.

Д ля автоподбора высоты строки надо воспользоваться командой Главная\ Ячейки\ Формат\ Автоподбор высоты строки или дважды щелкнуть на нижней границе строки в столбце заголовков.

Введите в ячейку А2 текст: «Это пример выравнивания текста по высоте», установив размер шрифта ArialCyr – 14.

Введите в ячейку В3 текст: «Это пример выравнивания текста по ширине», установив размер шрифта ArialCyr – 14.

Введите в ячейку С4 текст: «Оригинальный пример», установив размер шрифта ArialCyr – 14.

Выполните выравнивание текста в ячейке А2 по высоте (чтобы текст разместился полностью в этой ячейке), не меняя ее ширину.

Для этого, активизировав ячейку с введенным текстом, вызовите контекстно-зависимое меню, нажав правую кнопку «мышки», и выберите команду «Формат ячеек…». После появления диалогового окна «Формат ячеек», выберите вкладку «Выравнивание» и установите флажок в группе «Отображение» - «переносить по словам». Автоматически будет изменена высота ячейки при неизмененной ширине: слова будут введены вниз по столбцу.

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

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

Разверните текст в ячейке С4 на 90 градусов и выполните автоподбор по ширине.

Д ля того, чтобы развернуть текст на 90 градусов, надо, вызвав контекстно-зависимое меню и выбрав команду «Формат ячеек…», в диалоговом окне «Формат ячеек» выбрать вкладку «Выравнивание» и в окне «Ориентация» развернуть надпись на 90 градусов.

Выделение ячеек

Для выделения одной ячейки необходимо на ней щелкнуть «мышкой».

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

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

Чтобывыделять разнесенные области, удерживайте нажатой клавишу Ctrl при выделении областей, следующих за первой.

Для выделения отдельных строк и столбцов щелкайте «мышкой» на серых заголовках строк и столбцов.

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

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

Переименуйте рабочий лист, следующий после листа «Начало», дав ему имя «Форматирование».

Сделайте ярлык рабочего листа «Форматирование» - синим.

Выделите ячейки А3-Н10. Сделайте обрамление выделенного блока ячеек двойной синей линией, а сетку (внутренние линии) – одинарной оранжевой.

Для этого, не снимая выделения с блока ячеек, вызовите контекстно-зависимое меню, нажатием правой кнопки «мышки», и выберите команду «Формат ячеек…». В диалоговом окне «Формат ячеек», выберите вкладку «Граница». На вкладке «Граница» выберите и установите нужные линии и цвета (Прочтите текст на вкладке!), подтвердив свой выбор нажатием кнопки ОК.

Залейте ячейки А3, В4, С5 и так далее по диагонали разными цветами.

Для этого, выделив ячейку, выполните ее заливку цветом через команду Главная\ Шрифт\ Цвет заливки.

Объедините ячейки Н3-Н8. Введите текст «Пример границ и заливки», сделав выравнивание по центру.

Для этого, выделив ячейки и не снимая выделение, вызовите контекстно-зависимое меню, нажатием правой кнопки «мышки», и выберите команду «Формат ячеек…». Вызвав диалоговое окно «Формат ячеек» выберите вкладку «Выравнивание». В группе «Отображение» установите флажок «объединение ячеек», а в диалоговом окошке «выравнивание по вертикали» выберите «выравнивание по центру».

Выделите таблицу и скопируйте ее на рабочий лист «Начало» в ячейку Е12.

Копирование и вставка таблицы на другом листе осуществляется командами пункта программного меню «Главная» (копировать, вставить).

Перемещение и автозаполнение ячеек

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

Если подвести курсор к активной ячейке, он может приниматьразный вид:

- маленький тонкий крестик – на черном квадрате;

- большой тонкий крест – «краб» - на границах ячейки.

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

Тонкий крестик позволяет выполнять автозаполнение (копирование).

Для автозаполнения ячеек необходимо выполнить следующие действия:

1. Ввести данные в начальную ячейку или ячейки.

2. Установить курсор «мышки» на маркере заполнения, маленьком квадратике в правом нижнем углу активной ячейки.

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

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

Переименуйте лист, идущий после рабочего листа «Форматирование», дав ему имя «Автозаполнение».

Сделайте ярлык рабочего листа «Автозаполнение» оранжевым.

Введите в ячейку А2 текст «Ячейка 1».

Активизируйте ячейку А2 и выполните автозаполнение строки до ячейки Е2. Номера ячеек будут увеличиваться (на единицу).

Активизируйте ячейку В2 и выполните автозаполнение до ячейки В10.

Номера ячеек будут увеличиваться (на единицу).

Выделите ячейки В2-В10, и протяните этот блок ячеек до столбца Н.

Произойдет копирование выделенного блока ячеек.

Пояснение.

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

Когда был выделен блок ячеек, произошло просто копирование: никакая зависимость тут не определялась.

Выделите блок ячеек С4-Е9 и переместите их на новое место, установив верхний левый угол блока в ячейке Е12.

Активизируйте ячейку А20.

Через кнопку Office\ Параметры Exel\ Изменить списки откройте диалоговое окно «Списки» и создайте новый список с элементами: роза, астра, лилия, пион. Добавьте этот список к другим.

Введите в ячейку А20 слово «роза» и выполните автозаполнение вправо до ячейки Е20. Выполните автозаполнение вниз от ячейки С20 до ячейки С25.

Автозаполнение ячеек идет в соответствии со списком, который имеется в программе (создается пользователем).

Введите в ячейку Н20 текст «январь» и выполните автозаполнение до текста «декабрь» вниз (или вправо).

Сохраните файл «Начало», щелкнув по пиктограмме с изображением дискеты.

Относительные и абсолютные ссылки (адресация в таблицах)

Переименуйте рабочий лист, следующий после листа
«Автозаполнение», дав ему имя «Формулы».

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

Установите через команду Разметка страницы \ Параметры страницы \ Ориентацияальбомную ориентацию страницы.

Активизируйте ячейку A3 и начните ввод таблицы «Товарооборот за полугодие», установив шрифт Arial Суг, размер 11.

Выделите таблицу и сделайте внешние границы - двойной синей линией, а внутренние - одинарной черной.

Введите заголовок таблицы в ячейку А1, установив шрифт Arial Суг, размер 11, полужирный курсив. Выделите все ячейки над таблицей (в т.ч. и ячейку А1) и, не снимая выделение, вызовите контекстно-зависимое «Формат ячеек…». Выберите в диалоговом окне «Формат ячеек» вкладку «Выравнивание», и установите в диалоговом окошке «Выравнивание по горизонтали» - «по центру выделения».

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

Адрес ячейки составляется из заголовка столбца и заголовка строки.

В формулах всегда есть ссылка на имя:

- ячейки (в формуле участвуют данные, содержащиеся в этой ячейке);

-столбца или строки (заголовков), т.е.на данные, содержащиеся под этими именами в столбце или строке.

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

Когда копируется формула, содержащая относительные ссылки, происходит изменение формулы для поддержания относительности ссылок.

Абсолютная ссылка на ячейку или область ячеек всегда будет ссылаться на один и тот же адрес.

Для создания абсолютной ссылки при наборе формулы можно:

При наборе адреса набрать знак $ (например, абсолютная ссылка на ячейку С5 будет $C$5).

Ввести ссылку, как обычно, и нажать клавишу абсолютной ссылки - F4 (в строке формул сразу после ввода ссылки).

Когда копируется формула с абсолютными ссылками, то абсолютные ссылки не меняются.

Для преобразования существующей ссылки в абсолютную:

1. Выберите ячейку, содержащую формулу.

2. Установите курсор в строке формул после ссылки и нажмите клавишу F4 (при многократном нажатии клавиши F4 происходит изменение ссылки на смешанную – абсолютная ссылка только по столбцу или только по строке).

Переименуйте рабочий лист, следующий после листа «Формулы», дав ему имя «Задачи».

Сделайте ярлык рабочего листа «Задачи» голубым.

Введите в ячейку В2 текст: «Пример сцепления». Нажмите клавишу «пробел», а затем клавишу Enter.

Введите в ячейку С5 текст: «строк». Нажмите клавишу «пробел», а затем клавишу Enter.

Введите в ячейку С3 текст: «в программе Excel».

Активизируйте ячейку Е4 и введите формулу:

=b2&c5&c3

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

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

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

Введите в ячейки В8 – С10 (блок ячеек обозначаетсяименами первой и последней ячеек – по диагонали) числа: 17, 32, 26, 14, 49, 55 (в любом порядке).

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

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

Примечание.

Если активизировать ячейку, слева от которой находятся ячейки с числами (или над которой находятся ячейки с числами), и щелкнуть по кнопке «Автосумма» - , произойдет суммирование чисел в диапазоне, который выделяется пунктирной рамкой. Этот диапазон, при необходимости, можно уточнять в самой формуле.

Активизируйте ячейку D8 и щелкните по кнопке «Автосуммирование». Сделайте это и для ячеек D9 и D10.

В ячейке D10 появился результат суммирования не по строке, а по столбцу!Исправьте это, выполнив следующие действия:

- активизируйте ячейку D10;

- в строке формул измените интервал вычисления (указанный в скобках).

Чтобы не исправлять интервал при суммировании по строке, можно выделить все суммируемые числа и одну пустую ячейку справа от них. Щелкнув по кнопке «Автосумма», Вы получите правильный результат суммы по строке.

Удалите число из ячейки D10. Выполните суммирование чисел по строке, выделив их.

Перейдите на рабочий лист «Формулы» и заполните столбец «Квартал».

Ячейка "Квартал" - это сумма за 3 месяца.

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

Сделайте ярлык рабочего листа «Ссылки» светло-зеленным.

Активизируйте ячейку А3 и введите таблицу:

Выполните обрамление таблицы двойной линией, сетку – одинарной, цвет любой.

Введите в ячейку D4 формулу:

=B4*C4

Выполните автозаполнение (скопируйте формулу) от ячейки D4 до ячейки D8.

При копировании формулы ссылка на ячейки меняется, т.к. это – относительная ссылка.

Введите в ячейку А1 процент налога на начисленную сумму - 10%.

Введите в ячейку Е4 формулу:

=$A$1*D4

Выполните автозаполнение (скопируйте формулу) от ячейки Е4 до ячейки Е8.

При копировании формулы абсолютная ссылка на ячейку А1 (признак абсолютный ссылки – наличие знака $ перед именем столбца и номером строки) остается неизменной в каждой строке столбца Е.

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

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

Электронная таблицаExcelпозволяет выполнять автоматические вычисления по формулам.

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

Кроме знаков математических операций (сложение - +; вычитание - —; умножение -*; деление - /; возведение в степень - ^; процент от числа - %), используются знаки сравнений (равно - =; не равно - <>; больше -> ; больше или равно -≥ ; меньше - <; меньше или равно - ≤), а также специальные знаки (интервал - :, объединение - ; , сцепление строк - &, пересечение – пробел).

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

- ссылки на ячейки (на имена ячеек);

- имена диапазонов;

- константы;

- встроенные функции.

Создайте имена «Начислено» и «Налог» (имя диапазона с данными в столбцах).

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

1 способ:

-выделить данные столбца (строки);

- через команду Формулы\ Определенные имена\ Создать из выделенного фрагмента вызвать диалоговое окно «Создание имен из выделенного диапазона»;

- щелкнуть «мышкой», чтобы установить «флаг» в окошке «в строке выше» («в столбце слева»), для создания имени столбца (строки);

- щелкнуть «мышкой» по кнопке ОК.

2 способ:

-выделить данные столбца (строки) вместе с заголовком;

- через команду Имя диапазона контекстного меню вызвать диалоговое окно «Создание имени»;

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

ВНИМАНИЕ!

Д иалоговое окно «Создание имени» можно также вызвать, выделив данные вместе с заголовком, через команду Формулы\ Определенные имена\ Диспетчер имен\ Создать или через команду Формулы\ Определенные имена\ Присвоить имя.

Можно дать имя-заголовок для диапазона данных. Для этого:

-выделить заголовок столбца (строки);

- любым способом (из перечисленных выше) вызвать диалоговое окно «Создание имени»;

- в окошке «Диапазон» щелкнуть по кнопке в правом углу;

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

Для подтверждения выбранного диапазона данных нажать клавишу Enter.

Активизируйте ячейку F4 и введите формулу для столбца «К выдаче»:

=Начислено-Налог

Используйте в формуле созданные имена!

Для этого установив курсор в строке формул, через команду Формулы \ Определенные имена \ Использовать в формуле выберите в списке имен нужные, щелкнув по ним правой кнопкой «мышки» (знаки операций наберите на клавиатуре, знак = появляется автоматически при вводе имени, участвующего в формуле).

Скопируйте эту формулу во все строки столбца.

Перейдите на рабочий лист «Формулы».

Создайте имена «Квартал» и «Цена».

Введите формулу для столбца «Выручка»:

=Квартал*Цена

Скопируйте эту формулу во все строки столбца.

Создайте имя «Выручка».

Введите формулу для столбца «НДС»:

=23%*Выручка

Скопируйте эту формулу во все строки столбца.

Создайте имена «НДС» и «Себестоимость».

Введите формулу для столбца «Прибыль»:

=Выручка-НДС-Себестоимость

Скопируйте эту формулу во все строки столбца.

Сохраните файл «Начало», щелкнув по пиктограмме с изображением дискеты.

Построение графиков и диаграмм

Для построения графиков и диаграмм по данным, расположенным в ячейках электронной таблицы, надо:

- выделить диапазон данных вместе с заголовками;

- через команду Вставкавызвать вкладку Диаграммы и выбрать тип диаграммы (графика).

Чтобыпостроить диаграмму для нескольких не смежных диапазонов данных (расположенных на расстоянии друг от друга), надо:

- выделить один диапазон данных вместе с заголовками;

-удерживая клавишу Ctrl выделить «мышкой» второй диапазон данных вместе с заголовками;

- через команду Вставкавызвать вкладку Диаграммы и выбрать тип диаграммы (графика).

Диаграмма (график) появляется на Рабочем листе автоматически, когда подтвержден сделанный выбор: щелчком «мышки» на выбранном типе диаграммы (графика).

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

Если поле диаграммы (графика) обозначено тонкой рамкой, диаграмма находится в пассивном состоянии. Если поле диаграммы (графика) обозначено широкой рамкой,диаграмма активизирована и ее можно корректировать, используя команды вкладок меню Работа с диаграммами – Конструктор, Макет, Формат.

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

Переименуйте рабочий лист, следующий после листа «Ссылки», дав ему имя «Графики».

Сделайте ярлык рабочего листа «Графики» желтым.

Постройте график функции Y=COS(X) для Х от 0 до 5 с шагом 0,2. Для этого выполните следующие действия:

- введите в ячейку В2 заголовок для столбца – Х;

- введите в ячейку В3 число 0 и нажмите клавишу Enter;

- вернитесь в ячейку В3 и через команду Главная\Редактирование\ Заполнить\ Прогрессия вызовите диалоговое окно «Прогрессия»

Кнопка «Заполнить» на вкладке «Редактирование»

- в диалоговом окне «Прогрессия» введите шаг изменения значений аргумента и его предельное значение, установите флажок расположения значений аргумента по столбцам и нажмите кнопку ОК.

Примечание. После завершения диалога выполняется автоматическое заполнение ячеек столбца значениями аргумента (Х) от 0 до 5 с шагом 0,2.

- введите в ячейку С2 заголовок для столбца – Y;

- активизируйте ячейку С3 и, щелкнув по кнопке «Вставить функцию» (в строке формул), вызовите диалоговое окно мастера функций;

ВExcel определено более 500 встроенных функций, которые можно разделить на 11 категорий:

финансовые функции

функции даты и времени

математические функции

статистические функции

функции ссылок и массивов

функции баз данных

текстовые функции

логические функции

функции проверки свойств и значений

инженерные

аналитические

Ч тобы воспользоваться для расчетов встроенной функцией, можно вызвать диалоговое окно «Мастер функций» через команду Формулы\ Библиотека функций\ Вставить функцию или щелкнуть мышкой на значке f_x в начале формульной строки.

Примечание.

На вкладке Библиотека функций расположены кнопки - пиктограммы 7 категорий встроенных функций, а также кнопка - пиктограмма «Автосумма», как часто используемая функция.

- в диалоговом окне «Мастер функций - шаг 1» выберите функцию и подтвердите свой выбор, нажав кнопку ОК;

- в появившемся диалоговом окне «Аргументы функции» введите имя столбца значений аргумента – Х;

- так как имя столбца значений еще не определено (не создано), появится запрос имени;

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

- выполните копирование (автозаполнение) формулы, которая находится в ячейке С3 для всех значений аргумента;

- выделите столбец Y со всем и значениями;

- через команду Вставка\ Диаграммы выберите длявыделенного диапазона график.

- вставьте заголовок графика – «График функции Y=COSX» (команда Макет\ Подписи\ Название диаграммы надменю «Работа с диаграммами»).

Сохраните файл «Начало», щелкнув по пиктограмме с изображением дискеты.

Перейдите на рабочий лист «Формулы».

Выделите диапазон А3-D7.

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

Р асположите полученную диаграмму под таблицей «Товарооборот за полугодие».

Вставьте заголовок для диаграммы – «Товарооборот за квартал».

Выделите одновременно два диапазона А3-D7 и К3-М7.

Постройте диаграмму – «гистограмму с группировкой» для этих диапазонов, расположив ее рядом с предыдущей.

В ставьте заголовок для диаграммы – «Товарооборот за полугодие».

Сохраните файл «Начало», щелкнув по пиктограмме с изображением дискеты.

Группировка ячеек

Выделите смежные столбцы (заголовки) таблицы «Товарооборот за полугодие» - B-F и сгруппируйте их через команду Данные\ Структура\ Группировать.

Повторите эти действия для столбцов H-I и K-M.

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

Скройте все сгруппированные столбцы (останутся столбцы «Вид товара», «Выручка» и «Прибыль»).

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

Вставьте строку в таблицу после строки вид товара - Pentium 4 HT.

Чтобы вставить, удалить строку или столбец, надо воспользоваться командами Главная\ Ячейки \ Вставить (Удалить).

Введите в вставленную строку в столбце «Вид товара» текст - «Всего РС».

Введите в столбец «Вид товара» после строки с текстом «МЦ Sony» текст - «Всего ТВ\МЦ».

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

Найдите суммы «Выручки» и «Прибыли» только для всех видов ТВ и МЦ (результат в строке «Всего ТВ\МЦ»).

С группируйте строки таблицы так, чтобы остались только заголовки (три столбца) и строки «Всего РС» и «Всего ТВ\МЦ».

Сохраните и закройте файл «Начало».

Часть 2. Задания для закрепления навыков работы с программой Excel

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

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

Уровень сложности – низкий

Запустите программу Excel 2007.

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

Создайте таблицу по образцу (объедините ячейки, как в образце; сделайте перенос слов в заголовках, как в образце), установив шрифт:

- для набора заголовков – Arial, размер – 12, начертание – полужирный курсив, цвет – синий;

- для набора данных в таблице - Arial, размер – 10, начертание – полужирный, цвет - авто.

Выполните выравнивание заголовков, строк и столбцов таблицы, как в образце.

Выполните границы для таблицы толстой линией синего цвета, а сетку – толстой линией оранжевого цвета.

Выполните заливку строки заголовков – желтым цветом.

Заполните пустые столбцы таблицы, используя формулы:

Цена продаж = Цена покупная*1,3

Сумма покупок = Цена покупная*Количество

Сумма продаж = Цена продаж*Количество

Прибыль = Сумма продаж – Сумма покупок

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

Объединив ячейки над таблицей, введите заголовок – «Годовой отчет по продажам оргтехники», установив размер шрифта – 16, начертание – полужирный, цвет – красный.

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

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

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

Уровень сложности – средний

Задание 1.

Наберите строку заголовков: «Арифметическая прогрессия», «Геометрическая прогрессия», «Даты» и «Даты-2».

Введите в столбец под заголовком «Арифметическая прогрессия» арифметическую прогрессию от -12 до 20 с шагом 2.

Введите в столбец под заголовком «Геометрическая прогрессия» геометрическую прогрессию от 3 до 200000 (196608) с шагом 2.

В столбце под заголовком «Даты» введите дату – 1 апреля 2009 года и выполните автозаполнение до 17 апреля.

Введите в столбец под заголовком «Даты-2» прогрессию по дате от 01.04.2008 до 20.06.2008 с шагом в 5 дней.

Сделайте автоподбор ширины столбцов.

Сохраните выполненное задание на своем логическом диске.

Задание 2.

Установите параметры страницы - Альбомная.

Заполните таблицу по образцу (расположите верхний край таблицы в ячейке А3).

Создайте имена «Цена» для столбца «Цена за шт.($)» и «Сумма» для столбца «Сумма ($)».

Используя логическую функцию «Если», введите формулу и получите результаты в столбце «Сумма скидки ($)»:

=Если(Цена>700;Сумма*10%;Сумма*0%)

Введите в ячейку А1 текст «Курс доллара», а в ячейку А2 число 25,6.

Создайте имя для столбца «Сумма скидки ($)».

Введите формулу и получите результаты в столбце «Сумма скидки (руб.)»:

= Сумма_cкидки*$A$2

Объясните почему была необходима абсолютная ссылка на ячейку А2.

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

Введите формулу и получите результат для столбца «Сумма с учетом скидки (руб.)»:

=Сумма_по_курсу_в_руб.- Сумма_скидки_руб.

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

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

Выполните обрамление таблицы и сетку – синим цветом.

Сохранить на своем логическом диске под именем «Заявка».

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

Цель работы: Закрепить навык выполнения автозаполнения; выравнивания текста в ячейках таблицы; выполнения вычислений с использованием Мастера функций; создания имен; использование абсолютных ссылок; получение навыка использования различных форматов ячеек таблицы.

Уровень сложности – высокий

Запустите программу Excel 2007.

УстановитеРазметка страницы \ Ориентация– Альбомная.

Заполните ячейки А2-В3 в соответствии с образцом, а с ячейки А6 начните ввод таблицы, установив шрифт – Calibri размер - 11.

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

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

Заполните столбец «Стаж» следующим образом:

- введите формулу

=ЦЕЛОЕ ((дата- дата приема)/365),

выбрав ее в категории математические;

- укажите в качестве аргументов ячейки В2 (дата) и С7 (дата приема);

- получив значение стажа для преподавателя Ивакина, измените ссылку на ячейку В2, сделав ее абсолютной (нажимая клавишу F4 в строке формул на имени ячейки);

- выполните автозаполнение всех ячеек столбца «Стаж».

Заполните столбец «Зарплата», введя формулу

= Оплата_за_час* Нагрузка

следующим образом:

- выделите столбец «Оплата за час» (с заголовком) и, нажав правую кнопку мышки, подтвердите создание имени для выделенного диапазона;

- выполните эти же действия для столбца «Нагрузка»;

- установите курсор в первую пустую ячейку столбца «Зарплата» и, введя знак «=», через команду Формулы\ Определенные имена \Использовать в формуле выберите имена для формулы;

- выполните автозаполнение для всех ячеек столбца «Зарплата».

Создайте имя для столбца «Зарплата».

Заполните столбец «По курсу», используя формулу

= Зарплата/курс_доллара

Внимание!

Вместо имени «курс доллара» используйтеабсолютную ссылку на ячейку В3 (= Зарплата/$В$3).

Создайте имя для столбца «По курсу».

Заполните столбец «Процент надбавки», выполнив следующие действия:

- с ячейки А20 введите следующую вспомогательную таблицу

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

=ЕСЛИ(D7>10;$B$24;ЕСЛИ(D7>5;$B$23;ЕСЛИ(D7>3;$B$22;0)))

Внимание!

Вы можете вписывать в формулу все символы, кроме имени функции – ЕСЛИ, ее надо вставлять!

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

Создайте имя для столбца «Процент надбавки».

Заполните столбец «Сумма надбавки», используя формулу:

=Процент_надбавки*По_курсу

Создайте имя для столбца «Сумма надбавки».

Заполните столбец «Итого», используя формулу:

=(По_курсу+Сумма_надбавки)*$В$3,

чтобы получить результат в рублевом эквиваленте.

Создайте имя для столбца «Итого».

Заполните столбец «Сумма налога», используя функцию ЕСЛИ и учтя, что сумма налога от общей суммы до 20 тыс.руб. – 12%, свыше – 15% :

=ЕСЛИ(Итого>2000;Итого*15%;Итого*12%)

Создайте имя для столбца «Сумма налога».

Заполните столбец «На руки», используя формулу

=Итого-Сумма-налога

Прочтите и проработайте приведенный ниже текст:

Форматирование ячеек

Сначала выделите необходимые ячейки. Затем войдите в меню Главная\ Ячейки\ Формат.

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

В диалоговом окне щелкните на вкладке Число.

Из списка форматов выберите:

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

Денежный для установки основных денежных форматов.

Финансовый для выравнивания денежных величин по десятичной точке.

Дата для отображения данных в формате даты.

Время для отображения данных в формате времени.

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

Дробный для вывода числа в виде правильной дроби.

Внимание!

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

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

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

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

Уровень сложности – низкий

Запустите программу Excel.

Начните ввод таблицы «Результаты экзаменов» по образцу с ячейки А5, установив шрифт:

- для заголовков таблицы - Arial Cyr, размер – 12, начертание – полужирный;

- для всех остальных ячеек таблицы - Arial Cyr, размер – 14, начертание – обычный.

Введите в ячейку А3 текст «Проходной балл», а в ячейку В3 – число 12.

Заполните все ячейки таблицы, выполнив вычисления по формулам:

Средний балл - =СРЗНАЧ(диапазона)

Общий балл - =СУММ(диапазона)

Решение о зачислении -

=ЕСЛИ(Общий балл>=$B$3;"принять";"не принимать")

Принято - =ЕСЛИ(Общий балл>=$B$3;1;0)

Выполните для таблицы – «все границы», а затем – «толстая внешняя».

Введите в ячейку А26 текст «Всего принято:».

Выделите диапазон ячеек от А26 до G26 и выполните выравнивание текста по центру выделения.

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

Выполните границы – «толстая внешняя» для строки 26.

Введите в ячейку А1 текст «Результаты экзаменов», установив шрифт Arial Cyr, размер – 18, начертание – полужирный, цвет - синий.

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

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

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

Цель работы: Закрепить навык оформления таблицы; выполнения вычислений с использованием Мастера функций; работы с листами книги.

Уровень сложности – низкий

Запустите программу Excel.

Скопируйте лист 1 три раза.

Измените заголовки таблиц, добавив в название цифры: «Цех 1», «Цех 2» и т.д.

Измените название ярлыка 1 листа на имя «Цех 1». Измените названия ярлыков листов-копий, дав им имена по заголовкам, т.е. «лист 1(2)» – «Цех 2» и т.д.

Расставьте листы по порядку (после листа «Цех 1» - «Цех 2» и т.д.).

Наберите в таблице «Цех 1», в ячейке «Бригада 1» («январь») следующую формулу:

=35+85*СЛЧИС

ВНИМАНИЕ! – СЛЧИС – вставить через Мастер функций! Это – математическая функция.

Скопируйте эту формулу для всех бригад по всем месяцам.

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

Найдите сумму по всему цеху за все месяцы по всем бригадам.

Сделайте все эти операции для всех цехов (на других Рабочих листах).

После Рабочего листа «Цех 4» добавьте (переименуйте) лист, дав ему имя «Завод».

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

Это значит, что надо сложить значения в ячейках «Весь цех» (за конкретный месяц) на разных листах; адрес ячейки появляется автоматически, при выделении нужной.

Пример: =Цех1!Е7+Цех2!Е7+Цех3!Е7+Цех4!Е7

После Рабочего листа «Завод» добавьте (переименуйте) лист, дав ему имя «Отчет».

На Рабочем листе «Отчет» наберите таблицу

Просуммируйте данные по цехам по полугодиям и за весь год.

Пример: =Цех2!Е7+Цех2!Е8+… (за N месяцев)

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

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

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

Цель работы: Закрепить навык построения графиков и диаграмм.

Уровень сложности – средний

Задание 1.

Запустите программу Excel.

Заполните столбец-заголовок X данными от 0 до 2 с шагом 0,05.

Заполните столбцы-заголовки Y1, Y2, Y3 и Y4 данными для всех значений X, вычисляемыми по формулам:

Y1 = (x-1)²

Y2 = 2x

Y3 = x+1

Y4 = 2x²+2x

Постройте на одном листе графики для следующих пар функций:

Y1 и Y2

Y1 и Y3

Y1 и Y4

Y2 и Y3

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

Сохраните выполненную работу на своем логическом диске под именем «Графики-пары».

Задание 2.

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

Заполните столбец-заголовок X данными от 0 до 12 с шагом 0,5.

Заполните столбцы-заголовки Y1, Y2 и Y3 данными для всех значений X, вычисляемыми по формулам:

Y1=

Y2=

Y3 =

Постройте все три графика вместе под заголовком «Степенная функция».

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

Задание 3.

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

Введите в ячейку а2 заголовок для столбца – Х.

Введите в ячейку а3 первое значение для аргумента х=-4и выполните автозаполнение (арифметическая прогрессия) по столбцу до значения х=4 с шагом 0,25.

Создайте имя для всех значений столбца х.

Введите в ячейку в2 заголовок для столбца -Y=х^3.

Вставьте в ячейку в3 формулу соответствующую названию столбца (=СТЕПЕНЬ(x;3)) и выполните автозаполнение для всех значений х.

Постройте график функции Y=х^3.

Переименуйте лист, дав ему название «график Y=х^3».

Измените цвет ярлыка для листа «график Y=х^3» на красный.

Пояснение.

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

На других листах книги постройте графики функций:

Y=COS(x)

Y= СТЕПЕНЬ(0,25*x;2)

Y=EXP(x)

вводя соответствующие заголовки для столбцов в ячейку в2 и выполняя автозаполнение по столбцам от ячейки в3 до ячейки в35.

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

Сохраните выполненную работу под именем «Графики».

Задание 4

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

Заполните таблицу по образцу 1, установив шрифт Times New Roman, размер – 11, полужирный.

Выполните заливку строки заголовков сине-зеленым цветом, а строку данных – голубым.

Выполните внешние и внутренние границы таблицы синим цветом.

Разместите над таблицей заголовок, установив шрифт Times New Roman, размер – 24, полужирный, выравнивание по центру выделения.

Постройте круговую диаграмму по данным таблицы только для космических кораблей «Венера-2», «Венера-5», «Венера-7», «Венера-8» и «Венера-10» и поместите ее под таблицей.

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

Задание 5

На новом листе книги, оформите таблицу по образцу.

Постройте круговую диаграмму по образцу:

- легенда сверху (Работа с диаграммами \ Макет \ Подписи \ Легенда), шрифт – TimesNewRoman, размер – 14;

- подписи данных – по центру (Работа с диаграммами \ Макет \ Подписи \ Подписи данных), шрифт – TimesNewRoman, размер – 14, полужирный.

Переименуйте лист книги, дав ему название «Лифты».

Сохраните книгу под именем «Диаграммы».

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

Цель работы: Закрепить навык оформления таблицы; форматирование ячеек (заливка и цвет шрифта); получить навык фильтрации данных по условиям

Уровень сложности – средний

Запустить программу Excel 2007.

Создать базу данных – таблицу по образцу.

Используя условное форматирование (меню «Главная», вкладка «Стили»), выделите ячейки, содержащие текст:

- «компьютер» - красным цветом;

- «принтер» - зеленым цветом.

Рекомендация.

Выделите весь диапазон данных и используйте пользовательский формат.

Выделите ячейки содержащие текст:

- Pentium 100/8/1G – зеленая заливка и темно-зеленый текст;

- Pentium 133/16/1.2G – желтая заливка и темно-желтый цвет;

- 486DX5 133/8/1G – светло-красная заливка и темно-красный цвет.

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

Рекомендация.

Установив автофильтр, погасите все ненужные номера комнат.

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

Восстановите базу данных, отключив автофильтр.

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

Восстановите базу данных, отключив автофильтр.

Используя расширенный фильтр, получите список оргтехники, которая имеет инвентарный номер меньше 896-10 и находится в 317 комнате. Сохраните полученный список на отдельном листе Рабочей книги.

Рекомендация.

Скопируйте в свободные ячейки заголовки «Инв.номер»и «Комната». Введите условия для расширенного фильтра. Фильтруйте список на месте.

Восстановите базу данных.

Сохраните Рабочую книгу под именем «База» на своем логическом диске.

Адрес публикации: https://www.prodlenka.org/metodicheskie-razrabotki/97863-posobie-dlja-samostojatelnogo-izuchenija-prog