Образец решения задач по теме excel

Применяемые в тексте термины:

Клавиша – клавиша клавиатуры.

Кнопка – кнопка на панели инструментов программы (ищем на экране, нажимаем мышью).

Открыть кнопку – нажать мышью на треугольник, расположенный в правой части кнопки, например, или.

Задание

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

Таблица 40  Ведомость удержаний за товары, приобретенные в кредит

Таб. Номер	Фамилия И.О.	Стоимость товара, руб.	Долг на начало месяца	Сумма взноса, руб.	Долг на конец месяца
0102	Федоров М.И.	10500	5000	600
1202	Лебедева Р.А.	12000	4000	400
2111	Семенов А.Н.	15400	8000	800
3121	Носов П.К.	8000	3400	600
3125	Тимофеев Н.И.	9600	2500	500
3212	Николаев И.И.	7400	4250	450
3225	Васильев К.Д.	11500	5600	550
	Итого

Решение.Выполним предварительную разметку таблицы по столбцам и строкам:

	A	B	C	D	E	F
1	Таб. Номер	Фамилия И.О.	Стоимость товара,руб.	Долг на начало месяца	Сумма взноса,руб.	Долг на конец месяца
2	0102	Федоров М.И.	10500	5000	600
3	1202	Лебедева Р.А.	12000	4000	400
4	2111	Семенов А.Н.	15400	8000	800
5	3121	Носов П.К.	8000	3400	600
6	3125	Тимофеев Н.И.	9600	2500	500
7	3212	Николаев И.И.	7400	4250	450
8	3225	Васильев К.Д.	11500	5600	550
9		Итого

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

После заполнения всех ячеек получим примерно такой вид экрана:

Рисунок 3 – Вид экрана после заполнения ячеек данными

Выполняем коррекцию и форматирование

1. Табельный номер 0102 превратился в номер 102 в связи с тем, что 0102 считается числом, и незначащие цифры в нем по умолчанию отбрасываются. Для решения этой проблемы установим для ячейки А3 текстовый формат. Для этого вызовем меню Формат ячеек(2), нажав на кнопкуЧисло(1), щелкнем мышью на строчкеТекстовый(3) и нажмем кнопку [OK]:

Рисунок 4 – Вид диалогового окна «Формат ячеек»

После этого следует стереть содержимое ячейки А3, набрать его заново и нажать [Enter]. Ноль сохранился, но теперь текст 0102 по умолчанию прижат к левому краю ячейки, остальные числа – к правому.

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

2. Слова Таб.номериФамилия И.О.не поместились в ячейку по ширине, но это короткие тексты. Для исправления ситуации достаточно увеличить ширину ячейки.

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

Рисунок 4 – Вид экрана при изменении ширины столбца А

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

Для этого выделим ячейки шапки С2:F2, вызовем меню Формат ячеек, нажав на кнопку Число(4). Откроем вкладку Выравнивание(5), закажем щелчком мыши Переносить по словам(6), нажмем [OK].

Рисунок 5 – Настройки в диалоговом окне «Формат ячеек»

Рисунок 6 – Вид экрана после настройки переноса текста в ячейках по словам

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

Рисунок 7 – Вид экрана после настройки переноса ширины столбцов

5) Тем не менее, выравнивание текстов в шапке таблицы неудовлетворительное. Вернемся к меню вкладки Выравнивание (см. п.3) и закажем кнопками выравнивание по горизонтали (7) и по вертикали (8) – по центру.

Рисунок 8 – Настройки выравнивания в диалоговом окне «Формат ячеек»

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

Рисунок 9 – Вид экрана после установки формата «Полужирный шрифт» в шапке таблицы

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

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

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

Рисунок 10 – Вид экрана после установки формата «Денежный» в таблице

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

Примечание. Если открыть кнопку (нажать на треугольник в правой ее части), выбрать щелчкомДругие финансовые форматы, и в кнопке Обозначение выбрать вариант Нет (9), то знак денежной единицы р. будет в таблице отсутствовать, но числа, тем не менее, будут отодвинуты от правой границы ячеек, чего мы и добивались.

Рисунок 11 – Вид меню кнопки

Рисунок 12 – Настройка финансового формата в диалоговом окне «Формат ячеек»

При желании для лучшей читаемости документа можно развернуть на 90^о заголовок первого столбца:

Рисунок 13 – Вид первого столбца таблицы после изменения ориентации его заголовка

Для этого установите маркер на ячейку А2, нажмите кнопку [Число], откроется меню Формат ячеек. Перейдите на вкладку Выравнивание и установите в зоне Ориентация кнопку в состояние 90 градусов (10). Нажмите кнопку [OK].

Рисунок 14 – Настройка ориентации текста в ячейке в диалоговом окне «Формат ячеек»

7. Следующий шаг – вычисление формул. Установите маркер на пустую ячейку F3. Нажмите на клавиатуре клавишу [ = ] . Щелкните мышью на ячейке С3, нажмите на клавиатуре клавишу [ + ] , щелкните мышью на ячейке D3, нажмите на клавиатуре клавишу [ - ] , щелкните мышью на ячейке Е3, нажмите на клавиатуре клавишу [Enter]. В ячейке F3 получен ответ по формуле.

Формула в ячейке:

Рисунок 15 – Вид экрана во время записи формулы в ячейку F3

После нажатия на клавишу [Enter]:

Рисунок 16 – Вид экрана после нажатия клавиши Enter

8. Скопируем формулу из ячейки F3 в остальные ячейки столбца. Для этого верните маркер на ячейку F3, наведите указатель мыши на правый нижний угол маркера. Мышь при этом примет вид тонкого черного крестика, нажмите левую кнопку и, не отпуская, буксируйте мышь вниз до ячейки F9 включительно. Отпустите мышь.

1) Указатель мыши принял правильное положение:

Рисунок 17 – Положение указателя мыши для копирования формулы

2) буксируем мышь:

Рисунок 18 – Положение указателя мыши при буксировке

3. Результат:

Рисунок 19 – Результат копирования формулы

9. Рассчитаем формулы в строке ИТОГО. Для этого установите маркер на ячейку С10, нажмите кнопку . В ячейке отображается формула, суммируемые ячейки обведены бегущим пунктиром. Для завершения нажмите [Enter]. Повторите вычисление суммы для остальных ячеек строки ИТОГО или скопируйте первую сумму в ячейки, расположенные правее (см. п.8).

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

10. Сетка ячеек, которая видна на экране, не печатается. Для завершения создания документа необходимо обвести его границы. Выделите все ячейки документа, исключая заголовочную строку. Откройте кнопку , выберите щелчком вариантСнимите выделение щелчком на любой ячейке.

Рисунок 21 – Вид таблицы с построенными границами

11. Для большей наглядности можно выделить жирным шрифтом строку ИТОГО. Выделите ячейки строки, нажмите кнопку .

Задание

Рассчитать графу «% к итогу», округлить данные в графе до 2‑х знаков после запятой.

Решение

Рисунок 22 – Таблица для расчета процента к итогу

1. Установите маркер на ячейку С2. Нажмите клавишу [=], щелкните мышью на ячейке В2, нажмите клавишу [ / ], щелкните мышью на ячейке В14, нажмите клавишу[Enter].

2. Верните маркер на ячейку С2, нажмите кнопку [%].

Рисунок 23 – Процесс ввода формулы в ячейку С2

3. Найдите содержащуюся в ячейке формулу =В2/В14, которая отображается в строке формул (11) и щелкните мышью в зоне адреса В14. Положение курсора при этом принципиального значения не имеет, например, может получиться следующий вид: =В2/В|14. Нажмите на клавиатуре клавишу [F4], убедитесь в получении формулы вида =В2/$В$14 (12), нажмите [Enter]. Скопируйте формулу до конца документа методом буксировки маркера автозаполнения (см. п.8 Задания1).

Рисунок 24 – Вид таблицы после заполнения столбца «Процент» формулой из ячейки С2.

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

Рисунок 25 – Вид таблицы после округления до двух знаков после запятой данных в столбце «Процент»

Задание

Установите для заголовка таблицы указанный шрифт: Гарнитура шрифта (его тип) - Times, кегль шрифта (высота) — 14 пт, начертание – курсив, цвет – синий.

Решение

Установите маркер на ячейку А1, далее

1. Откройте кнопку , выберите щелчком вариантTimes New Roman;

2. Откройте кнопку , выберите вариант 14;

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

4. Откройте кнопку , выберите щелчком квадрат синего цвета.

Задание

Сохраните таблицу в указанном месте диска: на Рабочем столе, в папке Задание с именем Контрольная работа.

Решение

1. Нажмите кнопку в левом верхнем углу окна программы. Выберите вариантСохранить как.

Рисунок 26 – Вид диалогового окна «Сохранение документа»

2. Нажмите кнопку Рабочий стол (13).

3. Нажмите кнопку создания папки (14).

Рисунок 27 – Вид диалогового окна «Сохранение документа» во время создания новой папки.

4. Наберите имя папки Задание, нажмите кнопку [ОК].

5. В Excel-2007 созданная папка откроется по умолчанию. Если этого не произошло, откройте созданную папку двойным щелчком мыши.

6. В строке Имя файла (15) щелкните мышью, сотрите указанное там имя, наберите на клавиатуре Контрольная работа.

7. Если вы планируете продолжать работу над документом в более старой версии Excel, откройте кнопку [Тип файла] и выберите вариант Книга Excel 97‑2003 (16).

Рисунок 28 – Вид диалогового окна «Сохранение документа» во время выбора типа файла для сохранения.

8. Нажмите кнопку [Сохранить].

Задание

Переименовать Лист1 в лист с именем Расчет.

Решение

В левом нижнем углу экрана найдите обозначения ярлычков листов Excel.

Наведите указатель мыши на Лист1и выполните двойной щелчок мышью.Примечание.

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

Сотрите фразу Лист1, наберите на клавиатуреРасчет, нажмите клавишу [Enter].

Задание

Сделать копию таблицы на Лист2 и Лист3.

Решение

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

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

Щелчком на ярлычке Лист2перейдите на него, убедитесь, что маркер указывает на ячейку А1.

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

Исправьте, если нужно ширину столбцов.

Повторите те же действия для копирования таблицы на Лист3.

Задание

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

Решение

Установите маркер на ячейку G2 и внесите в нее текст заголовка:«Выбор данных».

Установите маркер в ячейку G3 (напротив первой фамилии).

Нажмите кнопку f_x на панели задач (17).

Рисунок 29 – Вид таблицы во время вызова мастера функций.

В списке функций выберите щелчком функцию Если и нажмите кнопку [OK]. Примечание при отсутствии функции в списке функций откройте кнопку Категория, выберите щелчком строку Логические, затем выберите щелчком функцию Если и нажмите кнопку [OK].

В появившемся диалоговом окне внесите следующую информацию:

Лог_выражение F3>15000

Значение_если_истина В3

Значение_если_ложь нажмите пробел или тире

Рисунок 30 – Вид диалогового окна «Аргументы функции» во время создания логической функции ЕСЛИ,

Нажмите кнопку [OK].

Ячейка G3 пуста, поскольку долг для фамилии Федоров менее 15 000 рублей. Скопируйте ячейку G3 до конца документа (см. п.8 Задания 1)

Рисунок 31 – Вид таблицы после заполнения столбца «Выбор данных» формулой с функцией ЕСЛИ.

Задание

Построить диаграмму (гистограмму) по данным стоимости товара и суммы взноса .

Решение

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

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

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

Рисунок 32 – Вид диалогового окна «Выбор источника данных», открытого в процессе построения диаграммы.

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

Рисунок 33 – Вид таблица с двумя выделенными столбцами

Рисунок 34 – Вид диалогового окна «Выбор источника данных» после выделения двух столбцов с данными в таблице.

Полученная диаграмма является «заготовкой», в ней отсутствуют подписи элементов:

Рисунок 35 – Вид построенной диаграммы.

Выделите щелчком Ряд 1, нажмите кнопкуИзменить, выделите щелчком названия первой группы чисел (значения ряда 1) – ячейкуС2:

Рисунок 36 – Вид диалогового окна «Изменение ряда».

Нажмите [OK].

Выделите щелчком Ряд 2, нажмите кнопкуИзменить, выделите щелчком названия второй группы чисел (значения ряда 1) – ячейкуЕ2, нажмите [OK].

Рисунок 37 – Вид диалогового окна «Выбор источника данных» после переименования рядов.

Рисунок 38 – Вид построенной диаграммы.

В группе Подписи горизонтальной осинажмите кнопкуИзменить и выделите ячейки с фамилиями. Нажмите [OK].

Рисунок 39 – Вид диалогового окна «Выбор источника данных» после изменения подписей горизонтальной оси.

Закройте окно, нажав [OK].

Рисунок 40 – Вид построенной диаграммы.

Выберите в секции Макеты диаграмм ту, которая наиболее точно соответствует заданию, например:

Рисунок 41 – Вид построенной диаграммы.

Щелкните на тексте Название диаграммы, сотрите его и наберите на клавиатуре Диаграмма взносов и долгов:

Рисунок 42 – Вид диаграммы с заголовком.

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

Рисунок 43 – Вид диаграммы во время изменения ее размера.

Задание

С помощью команды Автофильтр на Листе2 вывести записи, удовлетворяющие условиям задачи: о работниках, табельные номера которых начинаются с цифры 3 .

Решение

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

Перейдите для работы на Лист2, щелкнув для этого на ярлычке листа.

Установите маркер на любую ячейку столбца Таб.номер. Нажмите кнопкуСортировка и фильтр, выберите щелчкомФильтр (18).

Рисунок 44 – Меню кнопки «Сортировка и фильтр».

Результат:

Рисунок 45 – Вил таблицы с включенным автофильтром.

Откройте кнопку Таб.номер, щелчком по кнопке с изображением треугольника в ячейке А2.

Рисунок 46 – Вид таблицы с открытым списком фильтра.

Переведите указатель мыши на строку Числовые фильтры (19), затем –больше…(20), выполните щелчок мышью.

В правом поле наберите соответствующее заданию число 3000 (21), нажмите кнопку [OK].

Рисунок 47 – Вид диалогового окна «Пользовательский автофильтр».

Рисунок 48 – Результат применения автофильтра.

Задание

На Листе3 выполнить сортировку таблицы, расположив строки по алфавиту для столбца фамилий.

Решение

Перейдите для работы на Лист3, щелкнув для этого на ярлычке листа.

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

Выделите щелчком на бордюре число 10 (номер строки (22)).

Рисунок 49 – Таблица с выделенной итоговой строкой.

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

Рисунок 50 – Вид контекстного меню для вставки новой строки в таблицу.

Результат (24):

Рисунок 51 – Результат операции вставки новой строки.

Установите маркер на любую ячейку столбцаФамилия И.О.. Нажмите кнопкуСортировка и фильтр, выберите щелчкомСортировка от А до Я (25).

Рисунок 52 – Меню кнопки «Сортировка и фильтр».

Результат:

Рисунок 53 – Результат сортировки таблицы по алфавиту фамилий.

Задание

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

Решение

Перейдите для работы на Лист1. Нажмите кнопку предварительного просмотра или, щелкнув по кнопке(26), выберите в менюПечать(27)Предварительный просмотр(28).

Рисунок 54 – Меню кнопки Office.

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

Уменьшение полей

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

Рисунок 55 – Вид элементов управления окна предварительного просмотра.

Нажмите кнопку Параметры страницы (29) и откройте вкладку Поля(30):

Рисунок 56 – Вид диалогового окна «Параметры страницы», вкладка Поля.

Установите поля, оставляя левое поле не менее двух сантиметров, остальные – не менее одного. Нажмите кнопку [OK].

Печать в альбомном формате

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

Нажмите кнопку Параметры страницы и откройте вкладку Страница (31).

Рисунок 57 – Вид диалогового окна «Параметры страницы», вкладка Страница.

Установите ОриентацияАльбомная (32). Нажмите кнопку [OK]

Уменьшение масштаба документа

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

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

Рисунок 58 – Вид диалогового окна «Параметры страницы», вкладка Страница.

Закажите МасштабРазместить не более чем на 1 стр. в ширину и 1 стр. в высоту (33). Нажмите кнопку [OK].

Печать таблицы на отдельном листе

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

Рисунок 59 – Вид диалогового окна «Печать».

Установите щелчком Вывести на печатьВыделенный диапазон (34).

Для запуска печати нажмите кнопку [OK].

Печать диаграммы на отдельном листе

Закройте окно предварительного просмотра, если оно было открыто. Выделите щелчком диаграмму. Убедитесь в появлении рамки и ограничительных маркеров. Откройте щелчком кнопку и выберите в менюПечатьПечать.

Установите щелчком Вывести на печатьВыделенную диаграмму.

Для запуска печати нажмите кнопку [OK].

Рисунок 60 – Вид диалогового окна «Печать».