Табличный процессор MS Excel . Формулы.

Табличный процессор MS Excel. Формулы.

Диаграммы.

В данном методическом пособии номер задания указывает также и на номер балла в документе   лаб.р.+ оценки.xlsx

Запуск программы

Программа Microsoft Excel входит в состав пакета Microsoft office и является табличным процессором.

Запуск программы выполняется через меню ПУСК/ Все программы / Microsoft office/ Microsoft Excel либо на рабочем столе 2 раза щелкнуть мышкой на иконку .

Операции с файлами Excel (Создание нового документа, его сохранение, форматирование документа) аналогично работе в пакете Word.

Работа с документом (книгой)

Документ, создаваемый программой Microsoft Excel, называется книгой, отдельные рабочие области книги называются листами. Основные форматы файлов:

<Имя книги>.xls – книга в формате, совместимом с Microsoft Excel 97-2003

<Имя книги>.xlsx - книга в формате Microsoft Excel 2007, 10, 16

Рабочая область Excel представлена в виде таблицы. Программа Microsoft Excel предназначена для работы с таблицами данных, преимущественно числовых, которые используются для разнообразнейших вычислений.

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

Интерфейс программы представлен на следующем рисунке:

 

 

 

Меню организовано в виде вкладок (Файл, Главная, Вставка, Формулы и т.д.), все команды меню, предназначенные для работы с содержимым книги, представлены в виде пиктограмм (значков). Для работы с самой книгой используется вкладка ФАЙЛ:

 

Столбцы озаглавлены прописными латинскими буквами и, далее, их двухбуквенными комбинациями (от A до IV). Строки последовательно нумеруются цифрами, от 1 до 65536. На пересечении строки и столбца расположена отдельная ячейка. Соответственно каждая ячейка имеет свой адрес A1, C2, E6 и т.п. Также адреса ячеек можно использовать для обозначения диапазона ячеек, который всегда имеет прямоугольную форму и адресуется верхней левой ячейкой и нижней правой. Например, диапазон A1:B2 содержит 4 ячейки

В любой момент времени только одна ячейка может быть активной. Активная ячейка выделяется темным контуром — рамкой активной ячейки. Ее адрес, т.е. буква столбца и номер строки, указывается в поле ИМЯ.

Работа с листами

Один из листов книги является активным, он помещается в рабочую область окна Excel, и все текущие операции выполняются на этом листе. Перемещение между листами осуществляется нажатием закладок Лист1, Лист2…

 Для активизации другого листа книги можно также использовать комбинации клавиш: < Ctrl + PgUp > ¾ предыдущий лист, < Ctrl + PgDn > — следующий лист. Если в рабочей книге находится много листов, то не все их ярлычки могут быть видимы. В этом случае следует воспользоваться кнопками прокрутки ярлычков листов. Полный набор ярлычков можно вывести, щелкнув правой кнопкой в области значков прокрутки. При этом на экран выводится список всех листов текущей книги.

			Панель закладок

 

	Перейти к первому листу		Перейти к предыдущему листу		Переименованный лист		Стандартный лист

 

 

Добавление листа

Существует четыре способа добавления нового листа в рабочую книгу:

· Нажать комбинацию клавиш < Shift + F 11>.

· Щелкнуть правой кнопкой по ярлычку листа, перед которым добавляется новый и выбрать команду Вставить/Лист.

· Кнопкой  находящейся рядом с ярлычками листов.

 

Чтобы сразу добавить несколько листов, необходимо:

Шаг1. Выделить диапазон ярлычков, удерживая клавишу Shift указателем мыши (отмечают первый и последний лист выделяемого диапазона);

Шаг2. Выполнить команду вставки листов (Excel добавит столько листов, сколько было выделено ярлычков в диапазоне).  

При выделении нескольких листов рабочей книги происходит группировка листов. Эта операция используется для одновременного ввода данных в ячейки нескольких листов. Для разгруппировки ранее выделенных листов необходимо щелкнуть правой кнопкой в области диапазона и выбрать команду Разгруппировать листы.

 

Удаление листа

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

Чтобы удалить из рабочей книги несколько листов с помощью одной команды, нужно предварительно их выделить. Для этого, удерживая нажатой клавишу < Ctrl >, необходимо щелкнуть на ярлычки тех листов, которые надо удалить. Затем удалить листы описанным выше способом.

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

Существует несколько способов перемещения и копирования листа:

 

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

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

 

Переименование листа

По умолчанию рабочие листы книги называются Лист1, Лист2 и т.д. Обычно листам принято давать более содержательные названия. Чтобы переименовать лист, нужно воспользоваться одним из следующих способов:

· Дважды щелкнуть на ярлычке листа и изменить название.

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

копирование/ перемещение листаю. В текущей книге или в другую книгу), (эти действия выполняются посредством функций контекстного меню.

 

сокрытие и отображение листа. Эти действия выполняются посредством функций контекстного меню /Скрыть или Отобразить.

Задание А. Запустите программу Excel. Сохраните книгу в свою папку под именем <Фамилия>. xls х. Вставьте в книгу еще три листа и переименуйте все листы по порядку, присвоив имена Первый лист, …, Шестой лист. Перегруппируйте порядок следования листов в книге так, чтобы нумерация листов шла в порядке 2, 1, 4, 3, 6, 5.

 

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

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

Чтобы отобразить скрытый лист, выберите команду в контекстном меню Показать. Excel откроет диалоговое окно со списком скрытых листов. Выберите лист, который нужно отобразить, и нажмите кнопку OK.

Иногда возникает необходимость иметь в поле зрения две различные части одной рабочей таблицы. Для того чтобы создать новое окно, выберите команду ВИД/ОКНО ─ НовоеОкно и выберите команду Упорядочить/ Рядом. В результате Excel откроет новое окно и поместит туда активную рабочую книгу. Обратите внимание на текст в строках заголовков этих окон: Книга1:1, Книга1:2.

 Можно просто дать команду ВИД/ОКНО ─ Разделить. Команда Окно ─ Разделить разбивает активный рабочий лист на две или четыре панели в том месте, где находится табличный курсор. С помощи мыши можно придать им нужный размер. Чтобы вернуться к первоначальному виду экрана, необходимо ещё раз выполнить команду Окно ─ Разделить.

 

Диапазон ячеек

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

Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и столбцов. Диапазон ячеек обозначают, указывая через двоеточие номера ячеек, расположенных в противоположных углах прямоугольника, например A 1: C 15.  Если требуется выделить прямоугольный диапазон ячеек, это можно сделать протягиванием указателя от одной угловой ячейки до противоположной по диагонали.

 Чтобы выбрать столбец или строку целиком, следует щелкнуть на заголовке столбца или строки. Протягиванием указателя по заголовкам можно выбрать несколько идущих подряд столбцов или строк. Все рассмотренные диапазоны называются   смежными. Однако Excel позволяет также работать с несмежными диапазонами. Для выделения отдельных ячеек или диапазонов необходимо нажать клавишу < Ctrl > и, удерживая ее, щелкнуть на нужных ячейках.

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

позиции курсора.

Форматирование содержимого ячеек.

Форматирование осуществляется для текущей ячейки или выделенного диапазона ячеек. Вкладка Главная / Ячейки /Формат-Формат ячеек либо в Контекстном меню Формат ячеек.

 

 

Диалоговое окно Формат ячеек содержит вкладки:

Число – служит для установления формата данных ячейки;

Выравнивание – служит для форматирования содержимого ячейки (выравнивание текста, ориентация, перенос по словам, объединение ячеек и др.);

Шрифт – служит для выбора шрифта текста ячейки;

Граница – служит для установления границ ячейки;

Вид – служит для установления заливки ячейки.

Типы данных

Отдельная ячейка может содержать данные, относящиеся к одному из четырех типов: число, текст, дата и формула. Тип данных, определяется автоматически при вводе, при этом текстовые данные по умолчанию выравниваются по левому краю ячейки, а числовые ─ по правому.

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

· общий (используется как для чисел, так и для текста), 

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

· денежный, процентный, дробный (обыкновенные дроби).

· дата (используется для отображения дат в различных видах, например, 12.03.2017 или 12 мар 2017 или 12.03.17),

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

Формат ячеек можно копировать с помощью копки . Действия такие же, как и в Word.

Задание 1.1

Переименовать Лист 2 – назвать его «Данные». На этом листе для ячеек А1:А5 установить формат ячеек – числовой, для ячеек В1:В5 – денежный, для ячеек С1:С5 – процентный. Заполним диапазон ячеек А1:С5 числами,  как показано на рисунке. Расчертить все границы для диапазона ячеек А1:С5.

 

Над первой строкой вставить новую строку. Объединить ячейки А1, В1, С1, в объединенную ячейку ввести текст заголовка «Исходные данные». В ячейку D1 – «Результаты».

 

 

Вводить данные можно одновременно в несколько ячеек одного листа или нескольких листов книги.

Для ввода одного и того же значения в несколько ячеек одновременно, необходимо выделить эти ячейки, а затем ввести данные и нажать клавиши <Ctrl> и <ENTER>.

Если выбрана группа рабочих листов, то изменения в одном из них применяются ко всем выделенным листам. Для этого:

1. Выделите листы, на которые необходимо ввести данные.

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

3. Введите или измените данные в первой выделенной ячейке.

4. Нажмите клавишу ENTER или клавишу табуляции Tab.

Задание Б. Введите в ячейки диапазонов A 1: B 2 и C 10: D 11 на листах Лист1 Лист3, Лист4 число 3 одновременно.

Также как и в Word текст в документах Excel можно форматировать. Для этого нужно выделить фрагмент текста и выполнять необходимые действия при помощи пиктограмм на панели инструментов меню ГЛАВНАЯ. Форматирование текста в ячейке выполняется также на панели ФОРМАТ ЯЧЕЕК, вызываемой по правой кнопке мыши. Текст в ячейке можно выравнивать по вертикали и по горизонтали. Можно задавать отступ от левой границы ячейки. Можно изменять ориентацию текста на нужное количество градусов.

 

Работа с функциями

Именно наличие формул делает  электронные таблицы Excel табличным процессором, автоматизируя вычислительные операции. Формула всегда начинается со знака равно «=». Элементами формулы могут быть: операторы (+ - * /, логические операторы < > и некоторые др.); ссылки на ячейки с данными; числа; текст; функции.

Ввод формулы может осуществляться различными способами:

Ввод простых формул:

1) вручную (указывая значения вычисляемых переменных и операторов или функций), Например, =1+4 Enter;

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

Например, =А2 +А5 Enter

- Ввод формулы с помощью библиотеки функций

3) Например, суммирование диапазона ячеек от А2 до А5 с помощью функции: =СУММ(А2:А5) или суммирование отдельных аргументов =СУММ(А2;В3;В5)

Ввод сложных формул:

Для вставки функции в формулу необходимо выбрать вкладку Формулы/ Вставка функции. Открывается Мастер функций. Из библиотеки функций, расположенной в раскрывающемся списке вверху слева от рабочего листа выбирается соответствующая функция из необходимой категории (математические, статистические, финансовые). На втором шаге Мастера функций указываются в окне АРГУМЕНТЫ ФУНКЦИИ имена ячеек с данными вводятся либо нажатием мышки в соответствующие ячейки либо вводом с клавиатуры. В ячейке с формулой отображается результат вычислений, а формула отображается в строке формул.

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

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

 

Указываем диапазон ячеек с данными

Ячейка, в которой записывается формула

Раскрывающийся список библиотеки функций

 

1. Использование библиотеки функций.

Например, категория Математические, функция ABS (модуль числа). К любой функции есть комментарий, т.е. краткое описание функции, а также справка по этой функции.

 

 

Задание 1.2

В таблице из Задачи 1.1.  на листе «Данные»

· В ячейке D2 рассчитать произведение содержимого ячеек А2 и В2. Формула имеет вид =Произвед(А2;В2)

· В ячейке А7 рассчитать сумму содержимого диапазона ячеек А2:А6.

· В ячейке D3 рассчитать произведение содержимого ячеек А3, В3, С3.

· В ячейке D7 рассчитать сумму произведений ячеек А4,В4 и А5, В5

 

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

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

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

При этом ссылки могут быть абсолютными и относительными.

Относительные ссылки. Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейку, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании относительной ссылки из ячейки B2 в ячейку B3, она автоматически изменяется с = A1 на = A2.

Абсолютные ссылки. Абсолютная ссылка ячейки в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, и для использования абсолютных ссылок надо выбрать соответствующий параметр. Например, при копировании абсолютной ссылки из ячейки B2 в ячейку B3, она остается прежней =$A$1.

Смешанные ссылки. Смешанная ссылка содержит либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка столбцов приобретает вид $A1, $B1 и т. д. Абсолютная ссылка строки приобретает вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная ссылка не изменяется. При копировании формулы вдоль строк и вдоль столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании смешанной ссылки из ячейки A2 в ячейку B3, она изменяется с = A$1 на = B$1.

Функции, мастер функций

Шаг 1. Выбрать категорию, к которой относится функция.

При выборе категории справа выводится список всех входящих в нее функций. После этого выбираем нужную функцию и нажимаем кнопку ОК для пере хода к шагу 2. Например, для вычисления среднего арифметического выбирается функция СРЗНАЧ. Для вычисления суммы значений всех ячеек диапазона выбрана функция СУММ.

Шаг 2. В поля аргументов Число1, Число2, …и т.д. ввести аргументы – константы, адреса или интервалы ячеек, для которых вычисляется значение функции.

 

Для ввода в поле аргумента адреса ячейки или интервала щелкаем по этой ячейке или выделяем интервал. Справа у каждого поля аргумента отображается набор значений из выделенных ячеек. По мере заполнения полей аргументов автоматически добавляются новые поля. Результат вычисления вводимой функции отображается в нижней части панели аргументов. При нажатии кнопки ОК созданная функция помещается в формулу.

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

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

 

Задание 2.2. В рабочей книге <Фамилия>. xls х. выберите лист «Масштаб». Выделите всю строку 2 этого листа (нажав за поле 2 слева) и с помощью контекстного меню (правая кнопка мыши) вставьте пустую строку.

 В столбце A этого листа сделайте текущей новую пустую ячейку А2. В ней посчитайте для первого столбца A сумму элементов с помощью функции Автосумма   (стандартная панель инструментов). Для элементов столбца B в ячейке В2 найдите среднее значение с помощью функции СРЗНАЧ (категория Статистические). Для столбцов C, D и E вычислите соответственно минимальное число в заданном наборе (функция МИН), максимальное число (функция МАКС) и количество элементов в столбце (функция СЧЕТ).

Работа с таблицами Excel

Для создания таблицы в пакете Excel необходимо выделить область, в которую введены данные, и определить ее границы либо по пиктограмме  либо через контекстное меню ФОРМАТ ЯЧЕЕК\ закладка ГРАНИЦА.

Для переноса таблицы Excel в документ Word необходимо выделить таблицу, скопировать ее в буфер (по пиктограмме    во вкладке ГЛАВНАЯ (или через контекстное меню), перейти в документ Word, поместить курсор в нужном месте и вставить из буфера (по пиктограмме  или через контекстное меню). В редакторе Word импортированную таблицу можно редактировать как обычную таблицу формата word.

Также в Excel можно выбрать формат таблицы из имеющейся библиотеки автоформатов. Для этого выделяется область таблицы, ГЛАВНАЯ/СТИЛИ/ Форматировать как таблицу и в представленном списке выбирается нужный стиль оформления таблицы.

Задание 3. Создайте в книге <Фамилия>. xls х следующий за листом «Масштаб» новый лист «Прейскурант» и заполните его по образцу на рисунке справа. Заполните ячейки С2 и В4.

В ячейку А3 введине название списка «Товары» В последующие ячейки столбцов A и B (начиная с А4 иВ4)введите соответственно названия 5-ти товаров и их цены в условных единицах. Затем скопируйте формулу из ячейки C 4 методом автозаполнения во все ячейки столбца C, соответствующие заполненным ячейкам столбцов A и B.

Измените курс пересчета в ячейке C 2, все цены в рублях автоматически обновятся. Выделите диапазон ячеек A 1: C 1 и выполните команду Формат ─ Ячейки. На вкладке Выравнивание задайте выравнивание по горизонтали По центру и установите флажок Объединение ячеек. На вкладе Шрифт выберите шрифт 14 пунктов полужирный. На вкладке Главная установите темно серый цвет текста.

Для ячейки B 2 задайте выравнивание по горизонтали По правому краю, для ячейки C 2 ─ По левому краю (контекстное меню ─ Формат ячеек).

Выделите весь диапазон использованных ячеек и выполните команду Формат ячеек ─ Границы ─ Все. Выделите диапазон B 2: C 2. В соответствующем контекстном меню выберите команду Формат ячеек ─ Границы и задайте для этих ячеек толстую внешнюю границу.

Щелкните на кнопке Предварительный просмотр (меню: Файл/Печать), чтобы увидеть, как будет выглядеть документ при печати. Сохраните книгу

Задание 4.1 В открытой рабочей книге <Фамилия>. xlsх. создайте новый лист «Таблица» за листом «Прейскурант».

Создайте таблицу на основе данных диаграммы:

 

Год