ФЕДЕРАЛЬНОЕ АГЕНТСТВО ПО ОБРАЗОВАНИЮ
Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования
« Национальный исследовательский ядерный университет «МИФИ»
Математический колледж НИЯУ МИФИ
Теоретический материал
дисциплины «Пакеты прикладных программ»
для студентов III курса
специальности «Программное обеспечение ВТ и АС»
г. Москва
Оглавление
Ввод и редактирование данных. 3
Автоматизация ввода данных. 6
Условное форматирование. 6
Выполнение расчетов в Excel 7
Общения об ошибках. 9
Мастер функций. 12
Математические функции. 13
Статистические функции. 13
Логические функции. 14
Функции дата и время. 15
Работа с массивами. 16
Текстовые функции. 16
Функции поиска информации. 18
Функции проверки свойств. 19
Построение диаграмм.. 20
Линия тренда. 22
Построение поверхности. 22
Оформление и печать документа. 23
Работа со списками. 24
Сортировка данных. 24
Фильтрация данных. 26
Расширенный фильтр. 27
Составление отчетов. 28
Промежуточные итоги. 28
Структурирование рабочих листов. 29
Создание консолидированных отчетов. 29
Экспорт, импорт данных. 30
Сводные таблицы.. 30
Анализ и распределение данных. 34
Подбор параметра. 35
Поиск решения. 36
Таблицы подстановки. 41
Диспетчер сценариев. 46
Элементы управления. 49
Электронные таблицы Excel
Ввод и редактирование данных
Современные технологии обработки информации часто приводят к тому, что возникает необходимость представления данных в виде таблиц. В языках программирования для такого представления служат двухмерные массивы. Для табличных расчетов характерны относительно простые формулы, по которым производятся вычисления, и большие объемы исходных данных. Такого рода расчеты принято относить к разряду рутинных работ, для их выполнения следует использовать компьютер. Для этих целей созданы электронные таблицы (табличные процессоры) — прикладное программное обеспечение общего назначения, предназначенное для обработки различных данных, представимых в табличной форме.
Электронная таблица (ЭТ) позволяет хранить в табличной форме большое количество исходных данных, результатов, а также связей (алгебраических или логических соотношений) между ними. При изменении исходных данных все результаты автоматически пересчитываются и заносятся в таблицу. Электронные таблицы не только автоматизируют расчеты, но и являются эффективным средством моделирования различных вариантов и ситуаций. Меняя значения исходных данных, можно следить за изменением получаемых результатов и из множества вариантов решения задачи выбрать наиболее приемлемый.
Таблица – форма организации данных по строкам и столбцам.
Электронная таблица – компьютерный эквивалент обычной таблицы.
Табличный процессор – комплекс программ, предназначенных для создания и обработки электронных таблиц.
Табличный процессор MS Excel позволяет:
- Решать математические задачи: выполнять разнообразные табличные вычисления, вычислять значения функций, строить графики и диаграммы и т.п.;
- Осуществлять численное исследование (Что будет, если? Как сделать, чтобы?);
- Проводить статистический анализ;
- Реализовать функции базы данных – ввод, поиск, сортировку, фильтрацию (отбор) и анализ данных;
- Устанавливать защиту на отдельные фрагменты таблицы, делать их невидимыми;
- Наглядно представлять данные в виде диаграмм и графиков;
- Вводить и редактировать тексты;
- Осуществлять обмен данными с другими программами, например, вставлять текст, рисунки, таблицы, подготовленные в других приложениях;
- Осуществлять многотабличные связи.
При работе с приложением Excel создаются документы, которые называют рабочая книга, расширение файла xlsx (xls). Рабочие книги можно просматривать, изменять, записывать на носители внешней памяти для хранения, распечатывать на принтере.
Окно приложения имеет стандартный вид приложения Windows (заголовок, основное меню, панели инструментов, горизонтальная, вертикальная линейки, полосы прокрутки, строка состояния), контекстное меню, строка формул, поле адреса ячейки.
Строка формул в Excel используется для ввода и редактирования значений, формул в ячейках или диаграммах. Поле имени – это окно слева от строки формул, в котором выводится имя или адрес активной ячейки. Пиктограммы: X, V, fx, расположенные слева от строки формул - это кнопки отмены, ввода и вставка функции соответственно.
Строка состояния окна приложения Excel расположена в нижней части экрана. Левая часть строки состояния указывает сведения о состоянии рабочей области электронной таблицы (Готово, Ввод, Правка, Укажите). Кроме того, в левой части строки состояния кратко описываются результаты выполненной команды. В правой части строки состояния выводятся результаты вычислений (при выполнении автоматических вычислений с помощью контекстного меню строки состояния) и отображаются нажатые клавиш Ins, Caps Lock, Num Lock, Scroll Lock.
Настройки панели быстрого доступа – другие команды – на примере
Документ Excel - рабочая книга состоит из рабочих листов. Внизу рабочей области – панель ярлыков рабочих листов. Используя контекстное меню можно выполнять действия с рабочими листами.
По умолчанию открывается три рабочих листа или три электронных таблицы, переход к которым можно осуществить, щелкая на ярлычках, расположенных внизу книги. При необходимости в книгу можно добавить рабочие листы (электронные таблицы) или удалить их из книги. Кнопки прокрутки ярлычков осуществляют прокрутку ярлычков рабочей книги. Крайние кнопки осуществляют прокрутку к первому и последнему ярлычку рабочей книги. Внутренние кнопки осуществляют прокрутку к предыдущему и следующему ярлычку рабочей книги.
Рабочий лист состоит из ячеек – ячейка образуется на пересечении строки и столбца.
- Ячейка – минимальный объект табличного процессора;
- Строка – горизонтальный набор ячеек, заголовки столбцов – A, B, C,…,IV;
- Столбец – вертикальны набор ячеек, заголовки строк – 1, 2, 3,…65536;
- Адрес ячейки – определяется пересечением столбца и строки (A1, F123, AC72);
- Указатель ячейки – рамка;
- Активная ячейка – выделенная рамкой, с ней можно производить какие-либо операции;
- Смежные ячейки – ячейки расположенные последовательно;
- Диапазон (блок) ячеек – выделенные смежные ячейки, образующие прямоугольный участок таблицы;
Ячейки рабочего листа электронной таблицы могут содержать:
- исходные или первичные данные – константы;
- производные данные, которые рассчитываются с помощью формул или функций.
Данные в ячейках таблицы могут относиться к одному из следующих типов: текст, числа, дата, время, формулы и функции.
Текст – последовательность букв, иногда цифр или некоторых специальных символов, выравнивается по левому краю.
Числа могут включать цифры и различные символы: знак процента, знак мантиссы, круглые скобки, денежные обозначения, разделители и др. Например: 5; 3,14, выравниваются по правому краю. Дата и время вводятся в ячейки электронной таблицы как числа и выравниваются по правому краю.
Ввод данных осуществляется в три этапа:
1. выделение ячейки;
2. ввод данных;
3. подтверждение ввода (нажать клавишу Enter) или стрелки управления курсором.
Для редактирования данных в ячейке необходимо дважды щелкнуть на ячейке и произвести редактирование или исправление данных.
К операциям редактирования относятся:
- удаление и вставка строк, столбцов, ячеек и листов;
- копирование и перемещение ячеек и блоков ячеек;
- редактирование текста, чисел и формул в ячейках
К операциям форматирования относятся:
- изменение числовых форматов или формы представления чисел;
- изменение ширины столбцов, высоты строки;
- выравнивание текста и чисел в ячейках;
- изменение шрифта и цвета;
- Выбор типа и цвета границы
- Заливка ячеек
Для форматирования следует выделить ячейки и выполнить команду «Формат ячеек» - выравнивание, граница, заливка.
Любую информацию, которая обрабатывается на компьютере, можно представить в виде чисел или текста. Числа и текст по умолчанию Excel вводит в формате Общий.
Команда «Формат ячеек» - вкладка «Число»
Ввод текста
Текст - это любая последовательность введенных в ячейку символов, которая не может быть интерпретирована Excel как число, формула, дата, время суток. Введенный текст выравнивается в ячейке по левому краю.
Ячейка может вмещать до 255 символов. Если требуется ввести некоторые числа как текст, то для этого выделите ячейки, а затем выберите команду Формат / Ячейки. Далее выберите вкладку “Общий ” и в появившемся списке форматов выберите Текстовый. Еще один способ ввода числа как текста – это ввести перед числом символа апострофа.
Если текст не помещается в ячейку, то необходимо увеличить ширину столбца или разрешить перенос по словам (Формат / Ячейки, вкладка Выравнивание).
Ввод чисел
Числовые данные – это числовые константы: 0 - 9, +, -, /,*, Е, %, точка и запятая. При работе с числами необходимо уметь изменять вид вводимых чисел: число знаков после запятой, вид целой части, порядок и знак числа.
Excel самостоятельно определяет относится ли введенная информация к числу. Если введенные в ячейку символы относятся к тексту, то после подтверждения ввода в ячейку они выравниваются по левому краю ячейки, а если символы образуют число – то по правому краю ячейки.
Числа в Excel отображаются в категориях Числовой, Экспоненциальный, Финансовый, Денежный, Процентный, Дробный.
Даты и время рассматриваются – как числа, дата отсчитывается как количество дней от 1 января 1900 года, дата вводится через. или / или -, время через:
Число может быть представлено в денежном или финансовом формате.
