Работа в среде электронных таблиц excel

Иллюстрация: Meery Mary для Skillbox Media

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

Excel — универсальный софт для работы с электронными таблицами. Он одинаково хорош как для составления примитивных отчётов или графиков, так и для глубокого анализа больших объёмов информации.

Функции Excel позволяют делать всё, что может понадобиться в работе с таблицами: объединять ячейки, переносить данные с одного листа на другой, закреплять строки и столбцы, делать выпадающие списки и так далее. Они значительно упрощают работу с данными, поэтому применять их должны уметь все.

В Skillbox Media есть серия инструкций по работе с Excel. В этом материале — подборка главных возможностей программы и ссылки на подробные руководства с примерами и скриншотами.

• Как ввести и оформить данные
• Как работать с формулами и функциями
• Как объединить ячейки и данные в них
• Как округлить числа
• Как закрепить строки и столбцы
• Как создать и настроить диаграммы
• Как посчитать проценты
• Как установить обычный и расширенный фильтр
• Как сделать сортировку
• Как сделать выпадающий список
• Как пользоваться массивами
• Как использовать функцию ЕСЛИ
• Как использовать поисковые функции
• Как делать сводные таблицы
• Как делать макросы
• Как узнать больше о работе в Excel

---

С этого видеоурока стоит начать знакомство с Excel. В нём сертифицированный тренер по Microsoft Office Ренат Шагабутдинов показывает:

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

Формулы в Excel — выражения, с помощью которых проводят расчёты со значениями на листе. Пользователи вводят их в ячейки таблицы вручную. Чаще всего их используют для простых вычислений.

Функции в Excel — заранее созданные формулы, которые проводят вычисления по заданным значениям и в указанном порядке. Они позволяют выполнять как простые, так и сложные расчёты.

На тему работы с формулами и таблицами тоже есть видеоурок. В нём Ренат Шагабутдинов показывает:

• как проводить расчёты с помощью стандартных формул и функций;
• как создавать формулы с абсолютными и относительными ссылками;
• как находить ошибки в формулах.

Функция объединения позволяет из нескольких ячеек сделать одну. Она пригодится в двух случаях:

• когда нужно отформатировать таблицу — например, оформить шапку или убрать лишние пустые ячейки;
• когда нужно объединить данные таблицы — например, сделать одну ячейку из нескольких и при этом сохранить всю информацию в них.

В статье подробно рассказали о четырёх способах объединения ячеек в Excel:

• Кнопка «Объединить» — когда нужно сделать шапку в таблице.
• Функция СЦЕПИТЬ — когда нужно собрать данные из нескольких ячеек в одну.
• Функция СЦЕП — когда нужно собрать данные из большого диапазона.
• Функция ОБЪЕДИНИТЬ — когда нужно собрать данные из большого диапазона и автоматически разделить их пробелами.

Округление необходимо, когда точность чисел не важна, а в округлённом виде они воспринимаются проще.

В Excel округлить числа можно четырьмя способами:

• Округление через изменение формата ячейки — когда нужно округлить число только визуально.
• Функция ОКРУГЛ — когда нужно округлить число по правилам математики.
• Функции ОКРУГЛВВЕРХ и ОКРУГЛВНИЗ — когда нужно самостоятельно выбрать, в какую сторону округлить число.
• Функция ОКРУГЛТ — когда нужно округлить число с заданной точностью.

В статье показали, как применять эти способы округления.

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

Опция «замораживает» первую строку таблицы, первый столбец или несколько столбцов и строк одновременно. В этой статье Skillbox Media мы подробно разбирали, как это сделать.

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

В статье «Как создать и настроить диаграммы в Excel» рассказали:

• для чего подойдёт круговая диаграмма и как её построить;
• как показать данные круговой диаграммы в процентах;
• для чего подойдут линейчатая диаграмма и гистограмма, как их построить и как поменять в них акценты;
• как форматировать готовую диаграмму — добавить оси, название, дополнительные элементы;
• как изменить данные диаграммы.

В этой статье Skillbox Media подробно рассказывали о четырёх популярных способах расчёта процентов в Excel:

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

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

В этой статье Skillbox Media на примерах показали:

• как установить фильтр по одному критерию;
• как установить несколько фильтров одновременно и отфильтровать таблицу по заданному условию;
• для чего нужен расширенный фильтр и как им пользоваться;
• как очистить фильтры таблицы.

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

В статье о сортировке в Excel разобрали:

• как сделать сортировку данных по одному критерию;
• как сделать сортировку по нескольким критериям;
• как настроить пользовательскую сортировку.

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

В статье дали пошаговую инструкцию по созданию выпадающих списков — на примере каталога авто.

Массивы в Excel — данные из двух и более смежных ячеек таблицы, которые используют в расчётах как единую группу, одновременно. Это делает работу с большими диапазонами значений более удобной и быстрой.

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

В статье показали, как выполнить базовые операции с помощью формул массивов и операторов Excel:

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

ЕСЛИ — логическая функция Excel. С помощью неё проверяют, выполняются ли заданные условия в выбранном диапазоне таблицы.

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

В статье Skillbox Media подробнее рассказали о том, как работает и для чего нужна функция ЕСЛИ в Excel. На примерах показали, как запустить функцию ЕСЛИ с одним или несколькими условиями.

Поисковые функции в Excel нужны, чтобы ускорить работу с большими объёмами данных. С их помощью значения находят в одной таблице и переносят в другую. Не нужно, например, самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.

В этой статье Skillbox Media разобрали, для чего нужна функция ВПР и когда её используют. Также показали на примере, как её применять пошагово.

В видеоуроке ниже Ренат Шагабутдинов показывает, как работают другие поисковые функции Excel — ПОИСКПОЗ и ПРОСМОТРX. А также учит пользоваться функциями для расчётов с условиями — СЧЁТ, СУММ, СРЗНАЧ, ИНДЕКС.

Сводные таблицы — инструмент для анализа данных в Excel. Сводные таблицы собирают информацию из обычных таблиц, обрабатывают её, группируют в блоки, проводят необходимые вычисления и показывают итог в виде наглядного отчёта.

С помощью сводных таблиц можно систематизировать тысячи строк и преобразовать их в отчёт за несколько минут. Все параметры этого отчёта пользователь настраивает под себя и свои потребности.

В статье дали пошаговую инструкцию по созданию сводных таблиц с примером и скриншотами. Также на эту тему есть бесплатный видеоурок.

Макрос в Excel — алгоритм действий, записанный в одну команду. С помощью макросов можно выполнить несколько шагов в Excel, нажав на одну кнопку в меню или на сочетание клавиш.

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

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

• В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
• Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.
• Здесь собраны все бесплатные видеоуроки по Excel и «Google Таблицам», о которых мы говорили выше.

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

MS Excel относится к
классу систем обработки числовой
информации, называемых spreadsheet.
Буквальный перевод термина “spreadsheet”
c английского языка означает “расстеленный
лист (бумаги)”. В компьютерном мире под
этим термином подразумевают класс
программных средств, именуемых у нас
“электронными таблицами”.

Области
применения электронных таблиц:

• бухгалтерский и банковский учет;

• планирование распределение ресурсов;

• проектно-сметные работы;

• инженерно-технические расчеты;

• обработка больших массивов информации;

• исследование динамических процессов.

Основные
возможности электронных таблиц:

• анализ и моделирование на основе
  выполнения вычислений и обработки
  данных;

• оформление таблиц, отчетов;

• форматирование содержащихся в таблице
  данных;

• построение диаграмм требуемого вида;

• создание и ведение баз данных с
  возможностью выбора записей по заданному
  критерию и сортировки по любому
  параметру;

• перенесение (вставка) в таблицу
  информации из документов, созданных
  в других приложениях, работающих в
  среде Windows;

• печать итогового документа целиком
  или частично.

Преимущества
использования ЭТ при решении задач

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

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

Структура
электронной таблицы

В таблице используются
столбцы(256) истроки(16384).

Строки пронумерованы
от 1 до 16384, столбцы помечаются латинскими
буквами от А до Z, и комбинациями букв
АА, АВ,…, IV,

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

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

1. Модель ячейки в ms Excel

Каждая ячейка таблицы
имеет следующие характеристики:

• адрес;

• содержимое;

• изображение;

• формат;

• имя;

• примечание (комментарий).

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

MS Excel позволяет
использовать два стиля ссылок: стиль
А1 и стиль R1C1. Переключение стилей
осуществляется в меню: Сервис/
Параметры/ Общиеопцией “Стиль
ссылок R1C1”.

Например. Пусть в
ячейке D3 нужно получить произведение
чисел, находящихся в ячейках А2 (второй
ряд, первая колонка) и B1 (первый ряд,
вторая колонка). Это может быть записано
одним из следующих способов:

Таблица 18.

Вид ссылок	Стиль А1	Стиль R1С1
относительный	=A2 * B1 имя столбца, имя строки, которые будут относительно изменяться, при копировании формулы в другую ячейку.	=R[-1]C[-3] * R[-2]C[-2] смещение по строке, смещение по столбцу, относительно ссылающейся ячейки. Сама формула при копировании не изменяет вид, но ссылается уже на другие ячейки.
абсолютный	=$A$2 * $B$1 имя столбца, имя строки, которые останутся неизменным, при копировании формулы .	=R2C1 * R1C2 номер строки и номер столбца, относительно начала координат.
смешанный	=$A2 * B$1 =A$2 * $B1	=R[-1]C1 * R1C[-2] =R2C[-3] * R[-2]C2
Рис. 61a.Стиль ссылок A1 (имя столбца, имя строки).	Рис. 61bСтиль ссылок R1C1 (смещение по строке, смещение по столбцу).

Содержимым ячейки
может быть:

• число (целое со знаком или без (-345),
  дробное с фиксированной точкой (253,62)
  или с плавающей точкой (2,5362е+2));

• текст;

• формула.

Формула — всегда
начинается со знака “=“и может
содержать: числовые константы, абсолютные
или относительные ссылки на адреса
ячеек, встроенные функции.

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

Рис. 62. Изображение не всегда совпадает с содержимым ячейки

Изображение— то, что пользователь
видит на экране монитора.

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

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

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

Формат ячейки—
формат чисел, шрифт, цвет символов, вид
рамки, цвет фона, выравнивание по
границам ячейки, защита ячейки.

Имя— используется
в формулах, как заменаабсолютногоадреса ячейки. Например, назначив ячейке
С3 имя “Произведение” в ячейку D3 можно
поместить формулу: =Произведение/3
(вместо формулы =С3/3). В этом случае, при
копировании формулы, адрес ячейки
меняться не будет.

Примечание —
сопроводительный текст к содержимому
ячейки. Ввести примечание в ячейку
можно с помощью менюВставка /
Примечание. Ячейка, имеющая примечание,
отмечается в рабочем листе точкой в
правом верхнем углу.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Microsoft Excel – самая популярная в мире программа электронных таблиц,
входящая в стандартный офисный пакет Microsoft Office. Она выдержала уже
несколько переизданий, возможности каждой новой версии расширяются, появляются
новые формулы, макросы для вычислений, которые используются в бухгалтерском
учете, научных и инженерных приложениях. MS Excel – универсальная программа для
составления электронных таблиц любой сложности и дизайна, графиков и диаграмм,
поэтому современные офисные работники готовят в ней отчеты, экспортируют в
Excel данные из других офисных программ.

Приложение Excel, будучи составной частью популярного пакета (имеется
в виду Microsoft Office), по массовости своего использования уступает, пожалуй,
только другому приложению этого же пакета (имеется в виду редактор Word). Хотя
это утверждение основано и не на статистических данных, однако, думается,
выглядит вполне правдоподобно. В любом случае, малознакомым широкому кругу
пользователей приложение Excel никак не назовешь. У Microsoft Excel есть
существенный, и, как представляется, не до конца раскрытый потенциал, и задача
данного пособия состоит в том, чтобы показать возможности MS Excel при решении
прикладных задач.

Основные
функции Excel:

−
проведение различных вычислений с использованием мощного аппарата функций и
формул;

−
исследование влияния различных факторов на данные; − решение задач оптимизации;

−
получение выборки данных, удовлетворяющих определенным критериям;

−
построение графиков и диаграмм.

Общие
сведения о книгах и листах Microsoft Excel

При
запуске Excel открывается рабочая книга с пустыми рабочими листами. Рабочая
книга в Microsoft Excel представляет собой файл, используемый для обработки и
хранения данных. Такие файлы имеют расширение — .xlsх. Каждая книга может
состоять из нескольких листов, поэтому в одном файле можно поместить
разнообразные сведения и установить между ними необходимые связи. Имена листов
отображаются на ярлычках в нижней части окна книги. Для перехода с одного листа
на другой следует указать соответствующий ярлычок. Название активного листа
выделено жирным шрифтом. Рабочий лист — это собственно электронная таблица,
основной тип документа, используемый в Excel для хранения и манипулирования
данными. Он состоит из ячеек, организованных в столбцы и строки, и всегда
является частью рабочей книги. В рабочем листе Excel 2007имеется 16 384столбца, заголовки которых указаны в
верхней части листа с помощью букв английского алфавита и1048576строк. Столбцы по умолчанию озаглавлены
буквами, строки — цифрами. Щелкните мышью на любой ячейке вашего рабочего листа
и, таким образом, вы сделаете ее текущей или активной (она пометится рамкой). В
поле имени, будет показан адрес текущей ячейки, например В4. Ячейка — это
основной элемент электронной таблицы только в ней может содержаться какая-либо
информация (текст, значения, формулы).

Элементы экрана

Строка заголовка находится в верхней части экрана и имеет обычный вид
для программ, работающих под управлением Windows, дополнительно здесь
установлена кнопка Office , которая
предназначена для вывода списка возможных действий с документом, включая
открытие, сохранение и печать. Также на строке заголовка есть панель быстрого
доступа.

Рис. 1.1. Структура рабочего
листа

Строка меню.

Под
строкой заголовка находится строка меню, в которой перечислены группы команд: Главная, Вставка, Разметка страницы, Формулы,
Данные, Рецензирование, Вид. Каждая группа объединяет набор команд,
имеющих общую функциональную направленность.

Рис. 1.2. Вид строки меню

Ввод и редактирование данных

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

Строка формул Microsoft Excel, используется для ввода или
редактирования значений или формул в ячейках или диаграммах. Здесь выводится
постоянное значение или формула активной ячейки. Для ввода данных выделите
ячейку, введите данные и щелкните по кнопке с зеленой «галочкой» или нажмите
ENTER. Данные появляются в строке формул по мере их набора.

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

Рис. 1.3. Вид строки формул

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

Каждая
ячейка имеет адрес, который обозначается именем столбца и именем строки.
Например А2. Если надо задать адрес ячейки,
которая находится на другом рабочем листе или в другой рабочей книге, перед
именем ячейки задают имя рабочего листа, а во втором случае и имя рабочей
книги. Например: Лист
1!А1или =[Работа1]Задание1!$B$4.

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

Есть два стиля представления ссылок в Microsoft
Excel:

— Классический;

— Стиль ссылок R1C1
(здесь R — row (строка), C — column (столбец)).

Ссылки
в Excel бывают 3-х видов:

— Относительные ссылки (пример:A1);

— Абсолютные ссылки (пример: $A$1);

— Смешанные ссылки (пример: $A1 или A$1, они наполовину относительные,
наполовину абсолютные).

Относительные ссылки

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

Например,
ячейка B2 содержит формулу = B5+C8, т. е. первый операнд находится на три
ячейки ниже в том же столбце, а второй операнд находится на 6 строк ниже и один
столбец правее ячейки B2. При копировании данной формулы и вставке ее в ячейку
С3, ссылки в ней снова будет указывать на ячейки, расположенные: первая — на
три ячейки ниже в том же столбце, вторая — на 6 строк ниже и один столбец
правее ячейки С3. Так, если формула из ячейки B2 копируется в ячейку С3, то
формула примет вид =С6 + D9, а если ско-пировать содержимое В2 в B3, то в
ячейке B3 формула примет вид = B6+C9.

Рис.
1.4. Относительная ссылка

Абсолютные ссылки

Если необходимо, чтобы ссылки не изменялись при
копировании формулы в другую ячейку, используют абсолютные ссылки. Абсолютная
ссылка всегда указывает на одну и ту же ячейку, независимо от расположения формулы,
её содержащей. Для создания абсолютной ссылки на ячейку необходимо поставить
знак доллара ($) перед той частью ссылки, которая не должна изменяться.
Например, если в A1 находится формула =$B$5+$C$8, то при копировании
содержимого ячейки A1 в ячейку В2 или A3 в этих ячейках также будетнаходиться
формула =$B$5+$C$8, что говорит о том, что исходные данные всегда будут браться
из ячеек В5 и С8.

Рис.
1.5. Абсолютная ссылка

Смешанные ссылки

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

Кроме понятия ячейки используется
понятие диапазона – прямоугольной области, состоящей из нескольких (или одного)
столбцов и нескольких (или одной) строк. В качестве адреса диапазона
указываются адреса левой верхней и правой нижней ячеек диапазона, разделенные
знаком двоеточие (:).
Например, диапазон A1:C4 содержит 12 ячеек (по 3 ячейки в строках и 4 ячейки в
столбцах).

Для
работы с несколькими ячейками сразу необходимо выделить блок ячеек. Это
выполняется следующим образом: для смежных ячеек щелкните на ячейке и удерживая
кнопку мыши, протяните по листу указателем. При этом будет произведено
выделение всех смежных ячеек. Блок описывается двумя адресами, разделенными
знаком двоеточия — адресом верхней-левой и нижней-правой ячеек. На рисунке,
например, выделен блок: A2:D4.

Рис.
1.7. Диапазон ячеек

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

Автозаполнение

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

С
помощью перетаскивания маркера заполнения ячейки её содержимое можно копировать
в другие ячейки той же строки или того же столбца. Данные в Excel в основном
копируются точно так же, как они были представлены в исходных ячейках.

Однако,
если ячейка содержит число, дату или период времени, то при копировании с
помощью средства Автозаполнение происходит приращение значения её содержимого.
Например, если ячейка имеет значение «Январь», то существует возможность
быстрого заполнения других ячеек строки или столбца значениями «Февраль»,
«Март» и так далее. Могут создаваться пользовательские списки автозаполнения
для часто используемых значений, например, названий районов города или списка фамилий
студентов группы.

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

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

— введите
в ячейку первый член ряда;

— подведите
указатель мыши к черной точке в правом нижнем углу выделенной ячейки (в этот
момент белый крестик переходит в черный) и нажмите на левую кнопку мыши;

— удерживая
нажатой кнопку мыши, выделите нужную часть строки или столбца;

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

Понятие формулы

Формулы – это выражение, начинающееся со знака
равенства«═» и
состоящее из числовых величин, адресов ячеек, функций, имен, которые соединены
знаками арифметических операций. К знакам  арифметических операций,
которые используются в Excelотносятся:сложение; вычитание; умножение; деление;
возведение в степень.

Некоторые операции в формуле имеют более высокий
приоритет и выполняются в такой последовательности:

возведение в степень и выражения в скобках;

умножение и деление;

сложение и вычитание.

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

В
формуле может быть указана ссылка на ячейку, если необходимо в расчетах
использовать её содержимое. Поэтому ячейка, содержащая формулу, называется
«зависимой ячейкой», а ячейка содержащая данное – «влияющей ячейкой». При
создании на листе формул можно получить подсказку о том, как связаны зависимые
и влияющие ячейки. Для поиска таких ячеек служат команды панели инструментов
«Зависимости». Значение зависимой ячейки изменится автоматически, если
изменяется значение влияющей ячейки, на которую в формуле есть ссылка. Формулы
могут ссылаться на ячейки или на диапазоны ячеек, а также на их имена или заголовки.

Перемещение и копирование формул

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

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

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

Таблица 1

Сообщения
об ошибках в формулах

#####	Ширина ячейки недостаточна для отображения результата вычисления или отрицательный результат вычислений в ячейки, отформатированной как данные типа даты и времени
#Знач	Нервный тип аргумента или операнда. Например, указание в качестве аргумента ячейки с текстом, когда требуется число
#Дел/0	Деление на 0
#Имя	Еxcel не может распознать текст, введённый в формулу, например неверное имя функции
#Н/Д	Данные ячейки одного из аргументов формулы в данный момент доступны
#Ссылка	Неверная ссылка на ячейку
#Число	Невозможно вычислить результат формулы, либо он слишком велик или мал для корректного отображения в ячейки
#Пусто	Результат поиска пересечений двух непересекающихся областей, то есть неверная ссылка

Функции Excel 

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

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

Синтаксис функций

Функции состоят из двух частей: имени функции
и одного или нескольких аргументов. Имя функции, например СУММ, — описывает
операцию, которую эта функция выполняет. Аргументы задают значения или ячейки,
используемые функцией. В формуле, приведенной ниже: СУММ — имя функции; В1:В5 —
аргумент. Данная формула суммирует числа в ячейках В1, В2, В3, В4, В5.

=СУММ(В1:В5)

Знак равенства в начале формулы означает, что
введена именно формула, а не текст. Если знак равенства будет отсутствовать, то
Excel воспримет ввод просто как текст.

Аргумент функции заключен в круглые скобки.
Открывающая скобка отмечает начало аргумента и ставится сразу после имени
функции. В случае ввода пробела или другого символа между именем и открывающей
скобкой в ячейке будет отображено ошибочное значение #ИМЯ? Некоторые функции не
имеют аргументов. Даже в этом случае функция должна содержать круглые скобки:

=С5*ПИ()

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

                                                               Федяева А.И

«Работа в Microsoft Excel»

Учебное пособие

Для дисциплины «Информационные технологии»

2019 год

 «Работа в Microsoft Excel» Учебное пособие для дисциплины «Информационные технологии

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

Содержание

Введение        4

Основные понятия и определения электронной таблицы MsExcel        6

Практическая работа 1  Тема: Обучение основным возможностям в программе Microsoft Excel. Ввод чисел и текста в таблицы Excel. Выделение границ. Форматирование текста. Понятие формул.        7

Практическая работа 2  Тема: Автозаполнение таблиц Excel. Абсолютные ссылки. Форматирование таблицы.        14

Практическая работа 3 Тема: Оформление документов Excel. Вставка примечаний. Использование функций.        16

Практическая работа 4 Тема: Построение диаграмм и графиков в таблицах Excel.        19

Практическая работа 5 Тема: Сортировка записей списка. Подготовка документа к печати.        22

Практическая работа 6 Тема: Математика в Excel. Графическое решение систем уравнений. Графики функций в Excel        24

Практическая работа 7 Тема: Решение линейных уравнений вида ax+b=0 в ЭТ Excel. Условное форматирование.        26

Практическая работа 8  Тема: Закрепление изученного материала: Решение квадратных уравнений вида ax2+bx+c=0 в ЭТ Excel.        31

Список используемой литературы        34

Введение

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

Составлено в соответствии с ГОС специальности 230105 Программное обеспечение вычислительной техники и автоматизированных систем.

При изучении данного курса студент должен:

Знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

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

Уметь:

• вводить данные в таблицы Excel;
• форматировать таблицы;
• использовать формулы при расчетах;
• добавлять примечания к ячейкам;
• применять функции для решения задач;
• строить различного рода диаграммы;

• выполнять сортировку данных;
• работать с формой;

• фильтровать данные;

• решать системы уравнений;
• решать линейные и квадратные уравнения;

• строить графики систем уравнений;

• применять логические функции для решения уравнений;
• выполнять условное форматирование;

В соответствии с государственным стандартом образовательной области «Информационные технологии» учебное пособие включает в себя: последовательность практических работ, для самостоятельного выполнения их на компьютере, позволяющих учащимся познакомиться с одной из наиболее популярных программ работы с электронными таблицами – MsExcel.

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

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

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

Основные понятия и определения электронной таблицы MsExcel

Объекты электронной таблицы (см. рис. 1):

Рис. 1.

Ячейка – элементарный объект электронной таблицы, расположенный на пересечении столбца и строки.

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

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

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

Таблица представляет собой сложный объект, который состоит из элементарных объектов: строки, столбца, ячейки, диапазона ячеек. Каждый элементарный объект обладает именем, которое определено разработчиками электронной таблицы.

Строка: заголовки строк представлены в виде целых чисел, начиная с 1.

Столбец: заголовки столбцов задаются буквами латинского алфавита сначала от A до Z, затем от АА до AZ, от ВА до ВZ и т.д.

Ячейка: адрес ячейки определяется ее местоположением в таблице, и образуется из заголовков столбца и строки, на пересечении которых она находится. Сначала записывается заголовок столбца, а затем номер строки. Например: А3, D6, АВ46 и т.д.

Диапазон ячеек: задается указанием адресов первой и последней его ячеек, разделенных двоеточием. Например: адрес диапазона, образованного частью строки 3 – Е3:G3, адрес диапазона, имеющего вид прямоугольника с начальной ячейкой F5 и конечной ячейкой G8 – F5:G8.

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

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

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

Формат ячейки характеризуется следующими параметрами: число, выравнивание, шрифт, рамка, вид, защита.

1. Число определяет тип данных, хранящихся в ячейке.
2. Выравнивание и шрифт используются так же, как и в текстовом редакторе. Рамка определяет внешнее обрамление ячейки (тип, толщину, штрих линии). Вид определяет заливку и узор фона ячейки.
3. Защита определяет уровень защиты данных в ячейке.

Формат строки позволяет регулировать высоту строки и управлять отображением строки в таблице.

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

Формат столбца позволяет регулировать ширину столбца и управлять отображением столбца в таблице.

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

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

Тема: Обучение основным возможностям в программе Microsoft Excel. Ввод чисел и текста в таблицы Excel. Выделение границ. Форматирование текста. Понятие формул.

Цель: Ознакомить учащихся с основными приемами работы в ЭТ Excel. Научить учащихся создавать простые таблицы в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

1. Для чего предназначены программы обработки числовой информации?
2. Какие вы знаете программы обработки числовой информации. Дать краткую характеристику?
3. Сферы применения электронных таблиц?
4. Основные возможности программы Excel?
5. Структура программы Excel?

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

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

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

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

Методические указания

Введение основных понятий, связанных с работой электронных таблиц Excel.

1. Запустите программу Microsoft Excel, любым, известным вам способом. Внимательно рассмотрите окно программы Microsoft Excel. Первый взгляд на горизонтальное меню и панели инструментов несколько успокаивает, так как многие пункты горизонтального меню и кнопки панелей инструментов совпадают с пунктами меню и кнопками окна редактора Word, изученным ранее.

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

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

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

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

Зафиксировать данные можно одним из способов:

• нажать клавишу {Enter};
• щелкнуть мышью по другой ячейке,
• воспользоваться кнопками управления курсором на клавиатуре (перейти к другой ячейке).

3. Все уже заметили, что таблица состоит из столбцов и строк, причем у каждого из столбцов есть свой заголовок (А, В, С…), и все строки пронумерованы (1, 2, 3…). Для того чтобы выделить столбец целиком, достаточно щелкнуть мышью по его заголовку, чтобы выделить строку целиком, нужно щелкнуть мышью по ее заголовку.

4. Обратите внимание на то, что в Поле имени, расположенном выше заголовка столбца А, появился адрес выделенной ячейки С4. Выделите другую ячейку, и вы увидите, что в Поле имени адрес изменился.

Выделите ячейку D5; F2; А16.

Какой адрес имеет ячейка, содержащая день недели?

5. Давайте представим, что в ячейку, содержащую имя студента нужно дописать еще и его фамилию. Выделите ячейку, содержащую имя студента, введите с клавиатуры фамилию и зафиксируйте данные, нажав клавишу {Enter}.

Что произошло? Фамилия не «дописалась» в ячейку, а новые данные заменили исходные и вместо имени студента вы получили фамилию. То есть, если выделить ячейку таблицы, содержащую некоторые данные и ввести новые данные с клавиатуры, в ячейке таблицы окажется последняя информация.

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

Выделите ячейку таблицы, содержащую имя студента, установите текстовый курсор перед текстом в Строке формул и наберите заново фамилию. Зафиксируйте данные. У вас должна получиться следующая картина (см. рис. 2):

Рис. 2.

Видно, что запись вышла за пределы своей ячейки и заняла часть соседней. Это происходит только тогда, когда соседняя ячейка пуста. Давайте ее заполним и проверим, что изменится.

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

Теперь видна только та часть ваших данных, которая помещается в ячейке (см. рис. 3). Как просмотреть всю запись? И опять к вам на помощь придет Строка Формул. Именно в ней можно увидеть все содержимое выделенной ячейки.

Рис. 3

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

• внести изменения в содержимое выделенной ячейки;
• просмотреть содержимое ячейки, если запись не видна целиком.

Формат ячейки: для того чтобы данные в ячейках правильно считались необходимо задать нужный формат.  Список возможных форматов, можно найти  в пункте меню «Формат»-«Ячейки…», закладка «Число», пункт «(все форматы)» в левом списке — справа появится список предопределенных форматов (см. рис. 4).

Рис. 4

6. Как увеличить ширину столбца для того, чтобы в ячейке одновременно были видны и фамилия и имя студента?

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

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

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

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

Обратите внимание, что в процессе выделения в Поле имени регистрируется количество строк и столбцов, попадающих в выделение. В тот же момент, когда вы отпустили левую клавишу, в Поле имени высвечивается адрес активной ячейки, ячейки, с которой начали выделение (адрес активной ячейки, выделенной цветом).

Выделите блок ячеек, начав с ячейки А1 и закончив ячейкой, содержащей «сегодня».

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

Выделите таблицу целиком. Снимите выделение, щелкнув мышью по любой ячейке.

8. Каким образом удалить содержимое ячейки? Для этого достаточно выделить ячейку (или блок ячеек) и нажать клавишу {Delete} или воспользоваться командой горизонтального меню Правка🢥Очистить.

Удалите все свои записи.

Далее рассмотрим понятие формулы:

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

Формулы вычисляют значения в определенном порядке.

Формула в Microsoft Excel всегда начинается со знака равенства (=). Знак равенства свидетельствует о том, что последующие символы составляют формулу. Элементы, следующие за знаком равенства, являются операндами, разделяемыми операторами вычислений. Формула вычисляется слева направо, в соответствии с определенным порядком для каждого оператора в формуле. Порядок операций может быть изменен с помощью скобок.

В приведенном ниже примере скобки вокруг первой части формулы определяют следующий порядок вычислений: определяется значение B4+25, затем полученный результат делится на сумму значений в ячейках D5, E5 и F5.

=(B4+25)/СУММ(D5:F5)

Применение ссылок на ячейки

Формула может ссылаться на значения констант и на другие ячейки. Ячейка, содержащая формулу называется зависимой ячейкой, если ее значение зависит от значений в других ячейках. Например, ячейка B2 является зависимой, если она содержит формулу =C2.

Всякий раз, когда меняется ячейка, на которую ссылается формула, по умолчанию зависимая ячейка также меняется. Например, если значение одной из следующих ячеек меняется, результат формулы =B2+C2+D2 также изменится.

Если формула использует не ссылки на ячейки, а константы (например =30+70+110), результат изменится только при изменении самой формулы.

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

Форматирование существующих чисел:

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

Задания для самостоятельной работы:

Задание №1:

Заполните предложенные таблицы, вычислив Доходы и Расходы.

Таблица 1

Доходы

статья	январь	февраль	март	апрель	май	июнь	всего
Продажи	400р.	300р.	500р.	563р.	632р.	756р.	?
Консультации	20р.	23р.	53	63р.	42р.	53р.	?
Услуги	100р.	102р.	105р.	109р.	111р.	145р.	?
Всего	?	?	?	?	?	?	?

Таблица 2

Расходы

статья	январь	февраль	март	апрель	май	июнь	всего
Материалы	200р.	100р.	20р.	53р.	42р.	40р.	?
Зарплата	100р.	100р.	100р.	100р.	100р.	100р.	?
Реклама	2р.	5р.	40р.	5р.	4р.	6р.	?
Аренда	20р.	20р.	20р.	20р.	20р.	20р.	?
Командировки	10р.	0р.	0р.	0р.	20р.	0р.	?
Оборудование	120р.	0р.	0р.	100р.	0р.	0р.	?
Всего	?	?	?	?	?	?	?

По полученным данным найдите разницу между Доходами и Расходами.

Занесите ее в таблицу:

Таблица 3

	январь	февраль	март	апрель	май	июнь	всего
Разница	?	?	?	?	?	?	?

Примечание:

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

2. В данном задании используется формат ячеек – денежный.

Задание №2:

Вычислите «бюджет семьи» в неделю, в месяц, за год.

Таблица 4

Бюджет семьи

наименование товара	цена	кол-во в неделю	стоимость в неделю	стоимость в месяц	стоимость за год
хлеб	4,7	7	?	?	?
молоко			?	?	?
фрукты			?	?	?
макароны			?	?	?
мясо			?	?	?
яйцо			?	?	?
овощи			?	?	?
крупы			?	?	?
сахар			?	?	?
конфеты			?	?	?
итого:		?	?	?

Задание №3:

Составьте финансовый план ремонта квартиры.

Таблица 5

Финансовый план

необходимые материалы	цена	необходимое кол-во	стоимость
Краска	5	5	?
Обои	20	40	?
гвозди	1	60	?
итого:	?	?	?

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

Тема: Автозаполнение таблиц Excel. Абсолютные ссылки. Форматирование таблицы.

Цель: Ознакомить учащихся с автоматическим заполнением таблиц. Научить учащихся форматировать таблицы в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

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

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

• использовать автозаполнение таблиц;
• форматировать таблицы.

Методические указания

1. Дать определение Маркера заполнения.
2. Изучение заполнения рядов чисел, дат или других элементов.
3. Копирование данных внутри строки или столбца.
4. Форматирование таблицы.
5. Вращение  и выравнивание текста в ячейке.
6. Процесс переноса длинной строки в ячейке  по словам.
7. Использование рамок в ячейках.
8. Заливка фона ячеек цветом.

Задания для самостоятельной работы:

Задание №1:

Подготовьте таблицу для расчетов платежей за электроэнергию.

1 кВт = 12р.

Таблица 6

месяц	дата	показание счетчика	расход кВт ч	сумма
	26.12.2002	3750
январь	30.01.2003	3840	90	57,60р.
февраль	25.02.2003	3960	?	?
март	23.03.2003	4070	?	?
апрель			?	?
май			?	?
июнь			?	?
июль			?	?
август			?	?
сентябрь			?	?
октябрь			?	?
ноябрь			?	?
декабрь			?	?

Задание №2: 

Подготовьте прайс-лист по продаже офисной мебели с учетом курса доллара. 1$= 32,19р.

Таблица 7

наименование товара	эквивалент $ US	цена в рублях
кресло рабочее	$               39	1 255,41р.
Стеллаж	$               35
стол приставной	$               42
стол рабочий	$               65
Стул	$               20
тумба выкатная	$               65
шкаф офисный	$               82

Задание №3:

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

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

Таблица 8

Фирма «Твистор» (текущий год)
Города	Квартал 1	Квартал 2	Квартал 3	Квартал 4	Год
Астрахань
Волгоград
Саратов
Самара
Нижний Новгород
Казань
Ульяновск
Пермь
Москва
Новосибирск
Итого:
В среднем:

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

Тема: Оформление документов Excel. Вставка примечаний. Использование функций.

Цель: Научить учащихся добавлять примечания к ячейкам в ЭТ Excel, решать задачи с применением функций.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

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

• использовать автозаполнение таблиц;

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

• форматировать таблицы.
• добавлять примечания к ячейкам;
• применять функции для решения задач.

Методические указания

1. Добавление примечания к ячейке
2. Использование функций для вычисления значений
3. Изучение структуры функции
4. Ввод формулы, содержащей функцию
5. Вставка функции

Задания для самостоятельной работы:

Задание №1:

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

Таблица 9

Наименование расходов	Дата	За неделю
01.мар	02.мар	03.мар	04.мар	05.мар	06.мар	07.мар
Продукты	60р.	30р.	45р.	120р.	50р.	25р.	300р.
Транспорт	10р.			20р.
Конц. товары		80р.
Развлечения			150р.				100р.
Прочие расходы		35р.	66р.				80р.
Итого:

Задание №2:

1. Составить таблицу, содержащую цены на мониторы различных марок в нескольких фирмах.

2. Заголовок таблицы отцентрировать по всем столбцам.

3. Изменить шрифты следующим образом:

• наименования мониторов – Times New Roman, коричневый;
• названия фирм – Courier New, синий, курсив;
• заголовок таблицы – Arial, красный, полужирный;
• заголовки столбцов, содержащих минимальную и максимальную цену на монитор данного вида – Arial, зеленый, цвет фона ячейки – салатовый.

4. Цены могут быть набраны любым шрифтом, их начертание изменить на курсив.

5. Установить размеры шрифтов:

• для заголовка таблицы – 12 пт.;
• в остальных ячейках – 10 пт.

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

1. Минимальную и максимальную цены найти с помощью функций МИН и МАКС.

Таблица 10

Модель	Фирма	Мин.	Макс.
Previous	Ellips	Vector	Aditon	IBM
Samsung 15»
Samsung 17»
Samsung 19»
LG 15»
LG 17»
LG 19»
Daewoo 15»
Daewoo 17»

Задание №3:

Составить таблицу значений функции двух переменных  F(x,y) заданной формулой

F(x,y),              

где x = a+hx,

y =c+hy,

hx =(b-a)/n,

hy =(d-c)/n,  

n=1,2,…,10

Дано:

Таблица 11

F(x,y)	a	b	c	D
= tg (x+y)	0	1	0	1

Ответ представить в виде таблицы:

Таблица 12

№	a	b	C	d	hx	hy	x	y	F(x,y)

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

Тема: Построение диаграмм и графиков в таблицах Excel.

Цель: Ознакомить учащихся с Мастером диаграмм. Научить строить диаграммы и графики по созданным таблицам в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

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

• использовать автозаполнение таблиц;

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

• форматировать таблицы;
• применять функции для решения задач;
• строить различного рода диаграммы.

Методические указания

1. Создание диаграммы.
2. Что такое Мастер диаграмм.
3. Создание диаграмм из несмежных диапазонов.
4. Изменение типа диаграммы (параметры диаграммы, исходные данные).
5. Форматирование диаграмм.
6. Добавление и удаление данных в диаграмме.

Задания для самостоятельной работы:

Задание №1:

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

• На основе данных о продажах наиболее ликвидных акций в Российской торговой системе за первые 4 недели периода наблюдений. Затем в диаграмму добавьте данные за 5 неделю (Тип диаграммы – гистограмма);
• На основе данных РАО «ЕЭС России» и Норильский никель постройте круговые диаграммы за весь период.

Таблица 13

Итоги торгов: количество сделок за период
Фирма	8-14 янв.	15-21 янв.	22-28 янв.	29-4 фев.	5-11 фев.
РАО «ЕЭС Россия»	2211	1365	1995	1381	1428
НК «Лукойл»	1417	1005	1159	797	561
Мосэнерго	629	253	481	294	238
Сургутнефтегаз	447	248	228	193	108
Ростелеком	398	245	237	404	208
Норильский никель	72	28	28	27	38

Пример диаграммы:

Рис. 5. Данные о продажах акций РТС за 4 недели

Задание №2:

1. Создайте таблицу.
2. Вычислите ежемесячные затраты фирмы «Пирамида» на проект «Зеленый остров», а также суммарные затраты за 4 месяца по статьям расходов.
3. Найдите сколько в среднем составляли затраты по статьям и максимальные затраты за каждый месяц.
4. Постройте объемную гистограмму, содержащую данные о проекте «Зеленый остров» за январь-апрель.
5. Вычислите, каковы были затраты в среднем за 4 месяца, и постройте по этим данным линейчатую диаграмму.
6. Постройте круговую диаграмму, показывающую, какая доля общей суммы расходов приходилась  на каждую из статей в апреле-марте.

Таблица 14

Наименование	Январь	Февраль	Март	Апрель	Итого
Контракты	3 000,00р.	3 600,00р.	2 700,00р.	2 880,00р.
Гонорары	1 500,00р.	2 658,00р.	2 658,00р.	2 280,00р.
Реклама	2 880,00р.	2 328,00р.	1 650,00р.	2 394,00р.
Фотографии	1 043,70р.	585,96р.	338,58р.	562,02р.
Приемы	531,24р.	453,90р.	589,32р.	298,50р.
Поездки	1 074,00р.	2 074,92р.	1 550,04р.	1 907,94р.
Поддержка	2 378,70р.	2 531,40р.	2 705,94р.	2 928,00р.
Дискеты	1 133,94р.	1 404,00р.	2 052,12р.	1 426,92р.
Проспекты	2 873,70р.	2 110,98р.	3 465,30р.	3 960,00р.
Итого:
В среднем:
Максимально:

Задание №3:

Постройте график функции y=10х2-5х-2, если х∈[–5;5] с шагом 1.

Для этого постройте таблицу:

Таблица 15

X	Y	Для вывода значений х необходимо выполнить ряд действий: Ввести начальное значение х в ячейку. Выделить ячейку с начальным значением. Выполнить команду Правка – Заполнить – Прогрессия. В появившемся диалоговом окне пометить арифметическая прогрессия, по столбцам и установить предельное значение и шаг. Нажать кнопку ОК. Значения у вычисляются при помощи формул.
-5	=10х2-5х-2
-4
-3
-2
-1
0
1
2
3
4
5

Результат:

Рис. 6. График функции 10х2-5х-2

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

Тема: Сортировка записей списка. Подготовка документа к печати.

Цель: Научить учащихся сортировать данные в таблицах, работать с формой и автофильтром в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

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

• использовать автозаполнение таблиц;

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

• форматировать таблицы;
• выполнять сортировку данных;
• работать с формой;
• фильтровать данные.

Методические указания

1. Установка полей страницы.
2. Печать документа.
3. Изучение команды Данные 🢥Форма…

Рис. 7. Окно «Форма»

1. Изучение команды Данные-🢥Сортировка…

Рис. 8.  Окно «Сортировка»

1. Применение команды Автофильтр.

Задания для самостоятельной работы:

Задание:

1. Составьте таблицу «Учет товаров».
2. Создайте форму для полученной таблицы, с ее помощью добавьте 5 любых товаров.
3. Выполните сортировку таблицы так, чтобы отделы магазина и наименования товаров в них располагались по алфавиту.
4. Отфильтруйте данные по отделу и с помощью Microsoft Word (изученный ранее) оформите список «Учет товаров кондитерского отдела».
5. С помощью Microsoft Word оформите «ассортимент товаров хлебного отдела».
6. Отфильтруйте данные, чтобы получить список товаров, оставшихся в магазине. Оформите список с помощью Word.
7. Оформите список товаров, где количество остатка <10.

Таблица 16

Учет товаров

	Приход	Расход	Остаток
№	Отдел	Наименование товара	Единица измерения	Цена прихода	Количество прихода	Цена расхода	Количество расхода	Количество остатка	Сумма остатка
1	Кондитерский	зефир в шоколаде	упак.	65,00р.	15	78,00р.	15
2	Кондитерский	конфеты «Желейные»	кг	57,00р.	280	64,00р.	135
3	Кондитерский	торт «Незабудка»	шт.	70,00р.	35	81,00р.	34
4	Молочный	Кефир	упак.	12,70р.	250	13,50р.	234
5	Молочный	Молоко	упак.	12,00р.	320	13,50р.	288
6	Молочный	Сметана	упак.	13,00р.	270	14,80р.	237
7	Мясной	колбаса докторская	кг	78,00р.	100	89,00р.	93
8	Мясной	Ливер	кг	62,00р.	60	67,00р.	45
9	Мясной	Окорок	кг	117,00р.	60	123,00р.	55
10	Мясной	Сосиски	кг	47,00р.	50	56,00р.	43

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

Тема: Математика в Excel. Графическое решение систем уравнений. Графики функций в Excel.

Цель: Научить учащихся решать системы уравнений в ЭТ Excel. Закрепить навыки работы с графиками функций в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

• создавать, форматировать таблицы и текст в таблицах.
• использовать формулы и функции при расчетах;

• использовать автозаполнение таблиц;

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

• решать системы уравнений;
• применять функции для решения уравнений;
• строить графики систем уравнений.

Методические указания

Графическое решение систем уравнений

Допустим, заданы следующие системы уравнений:

Рис. 9. Системы уравнений

Каждое из уравнений в представленных четырех системах может быть записано в виде функции:

F=y(x)

Очевидно, что первое уравнение в каждой из систем описывается функцией

F = х2 — 2х + 4         

Остальные  уравнения  описываются  функциями  Fi, где i отвечает порядковому номеру системы:

F1 =2х+ 1;        

F2 = 2х;        

F3 = 2х – 1;        

F4 = 5х+ 1.        

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

1. Создайте шапку таблицы со столбцами х, Fl, F2, F3 и F4.
2. Заполните столбец х значениями из интервала [-6,7] с шагом 1 .

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

1. Запишите в первую строку столбцов Fl, F2, F3 и F4 соответствующие формулы и при помощи авто заполнения скопируйте их в остальные ячейки столбцов.

а) в формульном виде таблица выглядит следующим образом (См. рис. 10):

Рис. 10

б) в результативном виде:

Рис. 11

Далее строим графики системы уравнений:

Необходимо составить диаграммы для первой системы уравнений на основе данных столбцов x, F и F1 и для второй системы – на основе данных столбцов x, F и F2.

Для этого выделите одновременно столбцы x, F и F1 и выполните команду Вставка – Диаграмма, задайте все необходимые условия. Для столбцов x, F и F2 проделайте то же самое.

Аналогичным образом постройте диаграммы для третьей и четвертой систем, используя данные столбцов х, F и F3; х, F и F4.

Результат представлен в Приложении 4.

Задания для самостоятельной работы:

Задание:

Построить график функции y=f(x) на интервале:

Результат:

Рис. 12. График функции y=f(x)

2. Построить график функции y=f(x) на интервале:

Результат:

Рис. 13. График функции y=f(x)

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

Тема: Решение линейных уравнений вида ax+b=0 в ЭТ Excel. Условное форматирование.

Цель: Ознакомить учащихся с логическими функциями  и функцией ЕПУСТО. Научить решать линейные уравнения в ЭТ Excel.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;

уметь:

• создавать, форматировать таблицы и текст в таблицах.
• использовать формулы и функции при расчетах;

• использовать автозаполнение таблиц;

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

• решать линейные уравнения;
• применять логические функции для решения уравнений;
• применять функцию ЕПУСТО;
• выполнять условное форматирование.

Методические указания

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

Итак, значения коэффициентов а и b будем вводить в ячейки В2 и ВЗ соответственно, а указывающие на это сообщения запишем в ячейки А2 и A3.

В ячейке В4 будем выводить информацию о том, имеет ли корень уравнение с заданными пользователем значениями коэффициентов; в случае положительного ответа в ячейке С4 выведем сообщение «Его значение:», а в ячейке D4 укажем значение корня.

Таблица 17

	A	B	C	D
1	Решение линейных уравнений вида ax+b=0			Необходимо найти х=- b/a
2	Введите значение коэффициента а
3	Введите значение коэффициента b
4

Для того чтобы решить данное уравнение будем использовать функцию ЕСЛИ, которая возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Функция ЕСЛИ используется при проверке условий для значений и формул.

Синтаксис

ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь)

Лог_выражение  — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — это логическое выражение; если значение в ячейке A10 равно 100, то выражение принимает значение ИСТИНА. В противном случае — ЛОЖЬ. Этот аргумент может быть использован в любом операторе сравнения.

Значение_если_истина   — это значение, которое возвращается, если лог_выражение равно ИСТИНА. Например, если этот аргумент — строка «В пределах бюджета» и лог_выражение равно ИСТИНА, тогда функция ЕСЛИ отобразит текст «В пределах бюджета». Если лог_выражение равно ИСТИНА, а значение_если_истина пусто, то возвращается значение 0. Чтобы отобразить слово ИСТИНА, необходимо использовать логическое значение ИСТИНА для этого аргумента. Значение_если_истина может быть формулой.

Значение_если_ложь   — это значение, которое возвращается, если лог_выражение равно ЛОЖЬ. Например, если этот аргумент — строка «Превышение бюджета» и лог_выражение равно ЛОЖЬ, то функция ЕСЛИ отобразит текст «Превышение бюджета». Если лог_выражение равно ЛОЖЬ, а значение_если_ложь опущено (то есть после значение_если_истина нет точки с запятой), то возвращается логическое значение ЛОЖЬ. Если лог_выражение равно ЛОЖЬ, а значение_если_ложь пусто (то есть после значение_если_истина стоит точка с запятой с последующей закрывающей скобкой), то возвращается значение 0. Значение_если_ложь может быть формулой.

Формула в ячейке В4 должна иметь вид:

В4=ЕСЛИ(B2>0;»есть»;»нет»)),        

где В2 — значение коэффициента а,

в ячейке

С4: =ЕСЛИ(B2<>0;»Его значение :»;»»)) (неполный вариант функции Если)                                                    
в ячейке

D4: =ЕСЛИ(B2<>0;-B3/B2;»»)),      

где В3 — значение коэффициента b

После того как эти формулы будут введены, таблица должна иметь следующий вид:

а) если есть корень

Таблица 18

	A	B	C	D
1	Решение линейных уравнений вида ax+b=0			Необходимо найти х=- b/a
2	Введите значение коэффициента а	3
3	Введите значение коэффициента b	6
4	Есть ли корень?	есть	Его значение :	-2

б) если корня нет:

Таблица 19

	A	B	C	D
1	Решение линейных уравнений вида ax+b=0			Необходимо найти х=- b/a
2	Введите значение коэффициента а	0
3	Введите значение коэффициента b	56
4	Есть ли корень?	нет

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

Чтобы устранить этот недостаток, можно использовать функцию ЕПУСТО, возвращающую значение ИСТИНА, если в ячейке, адрес которой указан в качестве аргумента функции в скобках, значение отсутствует.

С использованием указанной функции формулы будут иметь вид:
в ячейке

А4: =ЕСЛИ(ЕПУСТО(B2);»»;»Есть ли корень?»)        

в ячейке

В4: =ЕСЛИ(ЕПУСТО(B2);»»;ЕСЛИ(B2>0;»есть»;»нет»))        
в ячейке

С4: =ЕСЛИ(ЕПУСТО(B2);»»;ЕСЛИ(B2<>0;»Его значение :»;»»))        

в ячейке

D4: =ЕСЛИ(ЕПУСТО(B2);»»;ЕСЛИ(B2<>0;-B3/B2;»»))        

Таким образом, если один из коэффициентов не будет задан, таблица будет иметь следующий вид:

Таблица 20

	A	B	C	D
1	Решение линейных уравнений вида ax+b=0			Необходимо найти х=- b/a
2	Введите значение коэффициента а
3	Введите значение коэффициента b	56
4

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

Чтобы все это осуществить необходимо сделать ряд действий:

1. Выделить ячейку (в данном случае – В4)
2. Выполнить команду Формат – Условное форматирование

На экране появится окно, в котором нужно выбрать условия:

Рис. 14. Окно «Условное форматирование»

1. Нажать кнопку ОК.

В заключение урока студентам выдается домашнее задание:

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

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

Тема: Закрепление изученного материала: Решение квадратных уравнений вида ax2+bx+c=0 в ЭТ Excel.

Цель: Закрепить изученный материал методом решения квадратных уравнений.

Оборудование: ПК, ЭТ Excel.

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

знать:

• назначение электронных таблиц;
• структуру окна ЭТ Excel;
• основные приемы работы в ЭТ Excel;
• математический способ решения квадратных уравнений;
• формулы для решения квадратных уравнений.

уметь:

• создавать, форматировать таблицы и текст в таблицах.
• использовать формулы и функции при расчетах;

• использовать автозаполнение таблиц;

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

• решать квадратные уравнения в ЭТ;
• применять логические функции для решения уравнений;
• применять функцию ЕПУСТО;
• выполнять условное форматирование.

Методические указания

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

а) самостоятельно решить квадратное уравнение вида ax2+bx+c=0;

б) разработайте вариант оформления листа, учитывающий, что коэффициент a может быть равен нулю.

в) разработайте вариант оформления листа, использующий функцию ЕПУСТО.

Результат выполненной работы должен быть следующим:

1. Если корня нет

Таблица 21

Решение квадратных уравнений вида а*х*х+b*x+c=0
Введите значение коэффициента a	2
Введите значение коэффициента b	2
Введите значение коэффициента с	2
Есть ли корень?	нет

2.Уравнение имеет один корень (или коэффициент а  равен 0)

Таблица 22

Решение квадратных уравнений вида а*х*х+b*x+c=0
Введите значение коэффициента a	0	уравнение имеет один корень =	-1
Введите значение коэффициента b	2
Введите значение коэффициента с	2

3. Уравнение имеет два корня

Таблица 23

Решение квадратных уравнений вида а*х*х+b*x+c=0
Введите значение коэффициента a	2
Введите значение коэффициента b	-3
Введите значение коэффициента с	-1
Есть ли корень?	есть	Значение первого корня	1,78
	Значение второго корня	-0,3

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

А5=ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ((B2=0);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;»Есть ли корень?»)))),                              

где В2- значение коэффициента a,

В3 — значение коэффициента b,

В4 — значение коэффициента с.

В5=ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ((B2=0);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;ЕСЛИ(A8<0;»нет»;»есть»))))),                    

где А8 — дискриминант

С5=ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ((B2=0);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;

ЕСЛИ(A8>=0;»Значение первого корня»;»»)))))                                    

D5 =ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;ЕСЛИ(B2=0;» «;ЕСЛИ(A8>=0;(-B3+КОРЕНЬ(A8))/(2*B2);»»)))))      

C6 =ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ((B2=0);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;

ЕСЛИ(A8>=0;»Значение второго корня»;»»)))))                                      

D6 =ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;ЕСЛИ(B2=0;» «;ЕСЛИ(A8>=0;(-B3-КОРЕНЬ(A8))/(2*B2);»»)))))      

C2 =ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B4);» «;ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ(B2=0;»уравнение имеет один корень =»;» «))))                          

D2 =ЕСЛИ(ЕПУСТО(B3);» «;ЕСЛИ(ЕПУСТО(B2);» «;ЕСЛИ(ЕПУСТО(B4);» «;ЕСЛИ(B2=0;-B4/B3;» «))))                                                                        

Список используемой литературы

1. Новиков Ф.А., Яценко А.Д. Microsoft office XP в целом. — СПб.: БХВ-Петербург, 2002. – 928 С.
2. Информатика: Учеб./ Под ред. Проф. Н.В. Макаровой. М.: Финансы и статистика, 1997.
3. Никулин А.И. Операционные системы MsDOS и Windows. Новосибирск: НВИ, 2001.
4. Фигурнов В.Э. IBM PC для пользователя: Краткий курс. М.: ИНФРА – М, 1997.
5. Гарнаев А. Excel, VBA, Internet в экономике и финансах. СПб., 2002.
6. Олейников М. Internet для всех. М., 2000.
7. Могилев А.В., Пак Н.И., Хеннер Е.К. Практикум по информатике: Учеб.пособие для студентов, высш. учеб. заведений / Под ред. Е.К.Хеннера.- М.: Издательский центр «Академия», 2001. – 608 С.
8. Долженко В., Колесников Ю. Excel 2000. СПб, 2002.
9. Карпов Б. Microsoft Excel 2000: Справ. СПб.: Питер, 2000.
10.  Степанов А.Н. Информатика 3-е изд.-СПб.: Питер, 2002.- 608с.:ил.
11.  Карпов Б. Microsoft Office 2001: справочник.- СПб.: Питер, 2001 – 448с.:ил.

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

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

Создание таблицы в Microsoft Excel

Конечно, в первую очередь необходимо затронуть тему создания таблиц в Microsoft Excel, поскольку эти объекты являются основными и вокруг них строится остальная работа с функциями. Запустите программу и создайте пустой лист, если еще не сделали этого ранее. На экране вы видите начерченный проект со столбцами и строками. Столбцы имеют буквенное обозначение, а строки – цифренное. Ячейки образовываются из их сочетания, то есть A1 – это ячейка, располагающаяся под первым номером в столбце группы А. С пониманием этого не должно возникнуть никаких проблем.

Обратите внимание на приведенный выше скриншот. Вы можете задавать любые названия для столбцов, заполняя данные в ячейках. Именно так формируется таблица. Если не ставить для нее границ, то она будет бесконечной. В случае необходимости создания выделенной таблицы, которую в будущем можно будет редактировать, копировать и связывать с другими листами, перейдите на вкладку «‎Вставка» и выберите вариант вставки таблицы.

Задайте для нее необходимую область, зажав левую кнопку мыши и потянув курсор на необходимое расстояние, следя за тем, какие ячейки попадают в пунктирную линию. Если вы уже разобрались с названиями ячеек, можете заполнить данные самостоятельно в поле расположения. Однако там нужно вписывать дополнительные символы, с чем новички часто незнакомы, поэтому проще пойти предложенным способом. Нажмите «‎ОК» для завершения создания таблицы.

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

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

Основные элементы редактирования

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

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

Использование функций Excel

По сути, создать ту же таблицу можно практически в любом текстовом или графическом редакторе, но такие решения пользователям не подходят из-за отсутствия средств автоматизации. Поэтому большинство пользователей, которые задаются вопросом «‎Как научиться работать в Excel», желают максимально упростить этот процесс и по максимуму задействовать все встроенные инструменты. Главные средства автоматизации – функции, о которых и пойдет речь далее.

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

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

3. Если кликнуть по значку с функцией справа от поля ввода, на экране появится специальное окно «‎Вставка функции», в котором вы можете ознакомиться со всеми ними еще более детально, получив полный список и справку. Если выбрать одну из функций, появится следующее окно редактирования, где указываются аргументы и опции. Это позволит не запутаться в правильном написании значений.

4. Взгляните на следующее изображение. Это пример самой простой функции, результатом которой является сумма указанного диапазона ячеек или двух из них. В данном случае знак «:»​ означает, что все значения ячеек указанного диапазона попадают под выражение и будут суммироваться. Все формулы разобрать в одной статье нереально, поэтому читайте официальную справку по каждой или найдите открытую информацию в сети.

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

Вставка диаграмм

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

Подробнее: Как построить диаграмму по таблице в Excel: пошаговая инструкция

Элементы разметки страницы

Вкладка под названием «‎Разметка страницы» пригодится вам только в том случае, если создаваемый лист в будущем должен отправиться в печать. Здесь вы найдете параметры страницы, сможете изменить ее размер, ориентацию, указать область печати и выполнить другое редактирование. Большинство доступных инструментов подписаны, и с их использованием не возникнет никаких проблем. Учитывайте, что при внесении изменений вы можете нажать комбинацию клавиш Ctrl + Z, если вдруг что-то сделали не так.

Сохранение и переключение между таблицами

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

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

План урока:

Понятие и назначение электронных таблиц

Рабочий лист и книга, ячейка и ее адрес, диапазон ячеек

Интерфейс MS Excel: строка заголовка, строка меню, панель инструментов

Типы данных в Excel

Виды ссылок: абсолютные и относительные

Встроенные функции и их использование

Диаграмма. Виды и порядок построения диаграммы

Понятие и назначение электронных таблиц

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

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

Рисунок 1 – Основные функции электронных таблиц

Как правило, электронные таблицы предназначены для решения следующих задач:

— хранение разнородных данных в электронном виде в табличной форме;

— организация вычислений, выполняемых в автоматическом режиме;

— статистический анализ данных и поиск управленческих решений;

— построение графиков и диаграмм для наглядного представления данных;

— создание отчетов в форматах, удобных для последующей печати или распространения в сети.

Рабочий лист и книга, ячейка и ее адрес, диапазон ячеек

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

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

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

Адресация рабочего листа Excel задается в двумерной системе координат, где первой координатой является столбец листа, а второй – строка.  

Ячейка Excel – это хранилище одного элемента данных таблицы, доступ к которому осуществляется по адресу ячейки – номерам столбца и строки, на пересечении которых находится ячейка. Например, ячейка, расположенная в столбце «B» строки «6», будет иметь адрес «B6».

Диапазоном ячеек называют прямоугольную область, охватывающую стразу несколько строк и/или столбцов. Такие области имеют составную адресацию. Например, диапазон, охватывающий столбцы от «A» до «E» и строки от «4» до «9» включительно, будет иметь адрес «A4:E9».

Интерфейс MS Excel: строка заголовка, строка меню, панель инструментов

Интерфейс электронной таблицы Excel видоизменяется с каждым выпуском, следуя общему стилю и функциональности всего пакета MS Office. Тем не менее некоторые ключевые элементы, такие как строка заголовка, меню и панель инструментов присутствуют в каждой версии.

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

Рисунок 2 – Строка заголовка Excel

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

— «Файл»;

— «Главная»;

— «Вставка»;

— «Разметка страницы»;

— «Формулы»;

— «Данные»;

— «Рецензирование»;

— «Вид»;

— «Разработчик»;

— «Справка».

Рисунок 3 – Строка меню

При выполнении определенных задач состав меню может динамически видоизменяться, дополняясь новыми пунктами. Например, при редактировании диаграмм добавляются «Конструктор диаграмм» и «Формат».

Рисунок 4 – Динамически добавляемые пункты меню

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

Рисунок 5 – Фрагмент панели инструментов для пункта меню «Главная»

Типы данных в Excel

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

Тип данных – это формальное соглашение о том, какой объем памяти будет занимать элемент данных, как он будет храниться, обрабатываться в формулах и преобразовываться в другие типы.

Основные типы данных Excel:

— число;

— текст;

— дата и время;

— логическое значение;

— формула.

В большинстве случаев тип данных определяется автоматически, но бывают ситуации, когда Excel «не понимает» что имел в виду пользователь, тогда формат данных (включающий тип и способ его представления) указывают вручную. Это можно сделать как для отдельных ячеек, так и для целых столбцов, строк или диапазонов. Функция выбора формата доступна из контекстного меню.

Рисунок 6 – Команда контекстного меню для выбора формата данных

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

Рисунок 7 – Окно «формат ячеек»

Не все форматы отвечают за разные типы данных. Например, форматы «Числовой», «Денежный» и «Финансовый» – это просто разные представления числового типа, определяющие количество знаков после запятой, правила вывода отрицательных чисел, разделители разрядов и пр.

Виды ссылок: абсолютные и относительные

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

Ссылка в Excel – это адрес элемента или группы элементов данных, заданный в абсолютном или относительном виде.

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

Примеры относительных ссылок: «B3», «F2», «AP34».

Абсолютная ссылка – это адрес вида «$столбец, $строка», ссылающийся на ячейку, позиция которой остается неизменной при перемещении ячейки с формулой. Допускается отдельно «фиксировать» столбец или строку, указывая перед ними знак «$».

Примеры абсолютных ссылок:

— на ячейку E32: «$E$32»;

— на столбец F: «$F2»;

— на строку 4: «A$4».

Порядок создания формулы в Excel

Рассмотрим шаги создания формулы на примере произведения чисел.

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

Ошибки при вводе формул

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

Самые распространенные ошибки при вводе формул:

«#ДЕЛ/0!» – произошло деление на ноль или на пустую ячейку;

«#Н/Д» – один из аргументов функции в данный момент недоступен;

«#ИМЯ?» – некорректно задано название функции или аргумента;

«#ПУСТО!» – указанный диапазон не содержит ячеек;

«#ЧИСЛО!» – ячейка содержит значение, которое нельзя преобразовать в число;

«#ССЫЛКА!» – ссылка некорректна;

«#ЗНАЧ!» – один или несколько аргументов функции принимают недопустимые значения.

Встроенные функции и их использование

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

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

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

Рисунок 13 – Библиотека функций на панели инструментов

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

Пример вычисления математической функции

Допустим, перед нами стоит задача определения среднего балла ученика по имеющемуся списку оценок.

Шаг 1. На пустом листе в столбце B создайте список дисциплин, а в столбце C – соответствующих им оценок. Под списком дисциплин разместите ячейку с текстом «Средний балл».

Шаг 2. Поместите курсор в ячейку столбца C, расположенную напротив ячейки с текстом «Средний балл». В меню выберите пункт «Формулы» и нажмите на панели инструментов кнопку «Вставить функцию».

Шаг 3. Из списка функций выберите «СРЗНАЧ» — вычисление среднего значения, и нажмите кнопку «ОК». Появится окно заполнения аргументов функции, в которое Excel уже автоматически вписал столбец оценок.

Шаг 4. Если автоматически выбранный диапазон вас не устраивает, его можно скорректировать вручную. В нашем случае в диапазон попала ячейка с адресом C9, в которой никаких оценок нет. Ограничьте диапазон строками с третьей по восьмую, просто выделив его мышью.

Шаг 5. Подтвердите выбор, нажав кнопку «ОК». В ячейке C10 при этом появится среднее значение.

Шаг 6. Выводимое значение получилось не очень красивым, ограничим его одним знаком после запятой. Для этого щелкните правой кнопкой мыши по ячейке и в контекстном меню выберите «Формат ячеек…»

Шаг 7. В появившемся окне выберите формат «Числовой», число десятичных знаков – 1.

Теперь расчет и отображение среднего балла работают как нам нужно.

Диаграмма. Виды и порядок построения диаграммы

Диаграмма в excel – это форма наглядного графического представления набора данных.

Доступ к панели инструментов «Диаграммы» осуществляется через меню «Вставка».

В Excel имеется множество шаблонов диаграмм, объединенных в группы, самые популярные среди которых:

            — гистограммы;

            — точечные диаграммы;

            — графики;

            — круговые диаграммы.

Рассмотрим пошаговый порядок построения диаграммы успеваемости по четвертям учебного года. В качестве наиболее подходящего вида диаграммы определим столбчатую (гистограмму).

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

Шаг 2. Выделите на листе область, охватывающую все введенные данные и подписи.

Шаг 3. В меню «Вставка» — «Диаграммы» выберите первый элемент – «Гистограмма».

Шаг 4. Проверьте корректность создания диаграммы, при необходимости отмените шаги 2,3 и выделите диапазон заново.

Шаг 5. Измените название диаграммы. Щелкнув по нему мышью, введите «Успеваемость по четвертям».

Шаг 6. Слева на оси оценок мы видим значения 0 и 6. Таких оценок не бывает, поэтому исправим формат вывода. Наведите курсор мыши на ось оценок, нажмите правую кнопку и выберите «Формат оси…».

Шаг 7. В открывшемся окне параметров введите минимум – 1, максимум – 5, основные и промежуточные деления – 1.

Шаг 8. Добавьте к диаграмме таблицу оценок, нажав кнопку «+» в правом верхнем углу диаграммы и выбрав «Таблица данных».

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

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