2.3. Электронные таблицы

Электронная таблица Microsoft EXCEL

Электронная таблица – интерактивная система обработки информации, упорядоченной в виде таблицы с поименованными строками и столбцами.Табличный процессор − категория прикладного программного обеспечения, предназначенного для работы с электронными таблицами. Инструментарий электронных таблиц включает мощные математические функ­ции, позволяющие вести сложные инженерные, финансовые, статистические и прочие расчёты. Одними из самых популярных табличных процессоров сегодня являютсяMicrosoft Excel, вхо­дящий в состав пакетаMicrosoft Office, иOpen Office Calc, входящий в состав открытого для доступа пакетаOpen Office.

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

Рабочее окно Microsoft Excelпредставляет собой стандартное окно Microsoft Office со специфическими элементами. В верхней части окна расположена строка заголовка, в которой располагаются кнопка системного меню, название приложения и имя файла, загруженного в окно (в начале работы по умолчанию выводится имя файла Книга1), кнопки свертывания и развертывания окна программы. Ниже расположены: кнопка менюФайл, лента, панель быстрого доступа, строка ввода данных, рабочее окно, строка состояния

Лентаимеет ту же структуру, что и лента Microsoft Word 2010. Она имеет восемь постоянно открытых вкладок, ярлычки которых видны на экране:Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид и Надстройки. Каждая вкладка состоит из групп команд, в которых расположены кнопки управления. У большинства групп в правом нижнем углу имеется кнопка, которая вызывает окно диалога настройки параметров. Для увеличения рабочей области ленту можно свернуть соответствующей командой контекстного меню или комбинацией клавишCtrl + F1. По умолчанию на экран не выводится одна важная вкладка ленты– Разработчик. Чтобы добавить ее на ленту, выполните следующее: введите командуФайл, Параметры, Настройкаленты, в окне Основные вкладки установите флажокРазработчик и щелкните по кнопке ОК.

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

Строка формулимеет три поля: поле адреса ячейки (Имя), поле управляющих клавиш и поле ввода данных (Строка формул). ПолеИмяпредставляет собой раскрывающийся список, в нем указан адрес текущей ячейки или ее имя. Если щелкнуть мышкой по этому полю, ввести адрес ячейки и нажать клавишу Enter, то курсор электронной таблицы перейдет в указанную ячейку. Этот прием перехода к нужной ячейке называется непосредственной адресацией.

В поле управляющих кнопок выводятся три кнопки: ×–отмена редактирования строки ввода,?ν–окончание редактирования,f_x–ввод функций. Названные кнопки появляются при активизации строки ввода. ПолеВвод данных предназначено для отображения вводимой информации или содержания выделенной ячейки. Для активизации строки ввода необходимо щелкнуть по ней мышью.

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

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

Информация в электронной таблице сохраняется в виде рабочих книг. Имя книги выводится в строке заголовка. Рабочая книга состоит из листов различного типа. Максимально возможное число листов в рабочей книге – 256. Рабочий лист состоит из пронумерованных строк и столбцов. Столбцы рабочих листов озаглавлены латинскими буквами от A до Z и их комбинациями по два и три символа, например, АА, АВ, IU, …,XFD. Строки пронумерованы цифрами.

Рабочий лист содержит 16 385 столбца и 1 058 576 строк. На пересечении строк и столбцов образованы ячейки. В одной из ячеек расположен контур выделения – курсор электронной таблицы. Рабочий лист имеет номер, который указан на ярлыке. Если щелкнуть правой кнопкой мыши по ярлыку, то откроется контекстное меню с перечнем команд для управления рабочим листом. Рабочие листы можно добавлять, удалять, копировать, переименовывать, перемещать, группировать, разгруппировывать.

Ячейка и ее свойства

Ячейка является основным элементом таблицы. В качестве содержания ячейки выступают числовые и текстовые константы, а также вы­ражения (формулы).

Ячейка – область, образованная пересечением строки и столбца. Она обозначается номером столбца и строки, на пересечении которых находится. Например, С1, AV9999.

Диапазон(группа) – непрерывная область ячеек, обозначенная номерами начальной и конечной ячеек, разделенными двоеточием или точкой, например, А1:С10, D8.H12. Ячейке или диапазону может быть присвоено уникальное имя. Ячейка характеризуется следующими параметрами: адрес, содержание, значение, формат, статус.

Адрес ячейки

Адрес ячейки может быть абсолютным, относительным и смешанным.

Относительный адрес: А1, Е7. Относительный адрес в операциях копирования автоматически настраивается.

Абсолютный адрес: $A$1, $E$7. Абсолютный адрес ячейки не меняется в операциях копирования, вставки или удаления ячеек, строк и столбцов.

Смешанный адрес: $A1, A$1. Если ячейке присвоен смешанный адрес, то при копировании будет меняться только тот параметр, перед которым не стоит знак $. Например: $D6 – при копировании ячейки будет меняться только номер строки; D$6 – при копировании будет меняться только адрес столбца.

Присвоение имени ячейке

Ячейке или диапазону ячеек может быть присвоено имя. В Microsoft Excel 2010 для работы с именами создана отдельная группа – Определенные именана вкладкеФормулы. Присвоение имени осуществляется командойПрисвоить имя.

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

1. Выделить ячейку (диапазон ячеек).

2. Ввести команду Формула,Присвоить имя.

3. Выбрать область действия имен из списка Область: книга или лист. Ввести в строке ввода Имя диалогового окна Создание имени имя ячейки и щелкнуть по кнопке ОК. Область, которой присваивается имя, отображается в строкеДиапазон.

Содержание ячейки

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

Значение ячейки

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

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

Дата– значение функции дата.

Время– значение функции время.

Сообщения об ошибках:

#ДЕЛ/0! – деление на ноль;

#ИМЯ? – не определено имя переменной в формуле;

#Н/Д! – нет допустимых значений, аргумент функции не может быть определен;

#ЗНАЧ! – неправильный тип аргумента; например, использование текста там, где необходимо число и др.

Формат ячейки

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

Статус ячейки

Ячейка может иметь два статуса: защищена или не защищена. В защищенную ячейку нельзя внести информацию или изменить ее содержание. Установка режима защиты осуществляется командами вкладки Защита?окна диалогаФормат ячеек. Эта команда позволяет установить защиту на ячейку или скрыть формулу. По умолчанию для всех ячеек установлен режим защиты. Режим защиты ячейки вступает в силу только после защиты листа командамиЗащитить листиз группыИзменения вкладкиРецензирование. Для отмены защиты ячейки достаточно отменить защиту листа командойСнять защиту листа. Можно защитить также структуру книги.

Ввод данных

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

Курсор таблицы, или контур выделения, представляет собой рамку, окаймляющую всю ячейку. В правом нижнем углу рамки на пересечении сторон располагается маленький квадрат – маркер заполнения. Этот маркер используется для заполнения ячеек рядом данных с постоянным шагом, а также для копирования формул. Для перемещения курсора используются клавиши управления перемещением курсора, а также клавиши Home – перейти в первую ячейку строки, [Ctrl + Home] – перейти в ячейку А1, [End + клавиши управления перемещением курсора] – последняя занятая ячейка в соответствующем направлении, используются также клавиши прокрутки и ряд других комбинаций клавиш. Непосредственная адресация осуществляется вводом адреса ячейки в поле «Адрес ячейки» Строки ввода данных.

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

Ввод даты. Дата вводится в формате ДД.ММ.ГГ или ДД.ММ.ГГГГ: день, месяц, год (17.05.14). В качестве разделителя используется точка. Электронная таблица позволяет выводить дату на экран в различных форматах.

Ввод текстовых констант. Для ввода текстовых констант необходимо ввести символ = и текст в кавычках: =”Текст”. Для преобразования чисел или числовых значений выражений в текстовые константы следует воспользоваться функцией ТЕКСТ(). Тип данных в ячейке определяется при первом вводе.

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

Примеры записи формул:

=А2+2 - сложение;

=ЕСЛИ(А2<B2;C3;D2*E17) – условное выражение. Если значение в ячейке А2 меньше значения в ячейке В2, то результат будет равен значению ячейки С3, иначе – произведению значений ячеек D2 и Е17.

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

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

Методика работы с электронной таблицей

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

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

• Для выбора нескольких несвязанных областей необходимо нажать и удерживать клавишу Ctrl, а затем выделить требуемые области.

• Для отмены выделения ячеек щелкните мышью по чистому полю в любом месте экрана.

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

Копирование ячеек. При копировании ячеек можно использовать командыКопироватьиВставитьиз группыБуфер обмена вкладкиГлавнаяленты, команды контекстного меню.

Копирование с использованием маркера автозаполнения.

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

Копирование с помощью команд группы Буфер обмена или контекстного меню.

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

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

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

Оформление таблицы. Для оформления таблицы: обрамления, заливки цветом – можно воспользоваться вкладкамиГраницаиЗаливкаокна диалогаФормат ячеекили одноименными кнопками в группеШрифтвкладкиГлавная. Можно воспользоваться также командамиФорматировать как таблицуиСтили ячеекиз группыСтиливкладкиГлавная.

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

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

Сохранение, открытие и печать таблицы

Перед печатью целесообразно сохранить документ на диске, для этого необходимо выполнить следующее: введите команду СохранитьилиСохранить как... из менюФайл, укажите в строке ввода «Имя Файла» имя файла, при необходимости измените тип файла .xlsx на .xls для совместимости с предыдущими версиями программы Excel. Выберите диск и папку, щелкните по кнопкеОК. Открытие сохраненного ранее документа осуществляется командойФайл, Открыть. Выберите соответствующий диск, папку.

Настройка параметров таблицы

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

Разработка электронных таблиц

Категории функций электронной таблицы

Для удобства вычисления в табличных процессорах имеются встроенные функции (11 категорий): математические/тригонометрические, инженерные, текстовые, статистические, финансовые, даты и времени, логические,функции для работы с базами данных/списками, информационные и функции категории ссылки/массивы; функции проверки свойств и значенийи др.Кроме того, Excel содержит большое число надстроечных функций, которые используются для создания компьютерных программ в Excel, а также имеется возможность создания пользовательских функций и программ на Visual Basic for Applications. Можно написать программы на других языках программирования высокого уровня, например, С, FORTRAN, и потом вызвать их в Excel. Вызов функций осуществляется с помощью кнопки f_x строки ввода данных или команд вкладки Формулы.

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

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

Генерирование данных

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

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

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

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

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

Графические возможности электронной таблицы

Виды иллюстраций деловой графики

Табличные процессоры предлагают различные виды иллюстраций деловой графики(диа­грамм), причём их построение облегчено за счёт использования «Мастера диаграмм» − встроенных автоматизированных пошаговых процедур, позволяющих выбрать тип диаграммы и для него выполнить все необходимые операции, в том числе оформления различными компонентами.

Гистограмма показывает изменение данных за определённый период времени и иллю­стрирует соотношение отдельных их значений. Категории располагаются по горизонтали, а значения − по вертикали. Гисто­грамма с накоплением демонстрирует вклад отдельных элементов в общую сумму.

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

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

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

Точечная диаграмма показывает взаимосвязь между числовыми значени­ями в нескольких рядах и представляет две группы чисел в виде одного ряда точек в ко­ординатахx иy. Она отображает нечётные интервалы данных и часто используется для предоставления данных научного характера. При подготовке данных следует располо­жить в одной строке или столбце все значения переменнойx, а соответствующие значе­нияy – в смежных строках или столбцах.

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

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

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

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

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

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

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

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

Работа с матрицами

Операции с матрицами

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

МОБР(массив) – вычисление обратной матрицы;

МОПРЕД(массив) – вычисление определителя матрицы;

МУМНОЖ(массив; массив) – умножение матриц;

ТРАНСП(массив) – транспонирование матриц и др.

Решение систем линейных алгебраических уравнений

С помощью встроенных функций МОБР, МУМНОЖ и МОПРЕД операции решения систем линейных алгебраических уравнений выполняются достаточно эффективно. Например, можно воспользоваться формулой вычисления вектора неизвестных через обратную матрицу A⁻¹и вектор свободных членов B: X = A⁻¹⋅B .

Пример. Решить систему линейных алгебраических уравнений матричным методом:

65,18x + 36,31y + 23,76z = 86,56

–17,98x + 23,89y + 27,55z = –38,07

23,75x + 13,95y + 58,12z = 53,97

Решение:

1. Внесите в ячейки B6–D8 значения коэффициентов при неизвестных.

2. Внесите в ячейки F6–F8 значения свободных членов системы уравнений.

3. Выделите диапазон ячеек B12:D15 -? D14

и введите формулу МОБР(B6:D8), для завершения операции ввода нажмите комбинацию клавиш Ctrl+Shift+Enter.

5. Выделите диапазон ячеек F12:F15-? F14и введите формулу?МУМНОЖ(B12:D15-? D14;F6:F8). Для завершения ввода формулы нажмите комбинацию клавиш Ctrl + Shift + Enter. В ячейках F12–F14появятся значения корней системы уравнений: 1,652508; -0,88768; 0,466381

Контрольные вопросы

1. Как изменить ширину строки (столбца)?

2. Как вставить строку, группу строк (столбцов)?

3. Каким образом устанавливается формат ячейки?

4. Что такое Строка формул, какие поля она имеет?

5. Где можно увидеть адрес текущей ячейки?

6. Что такое абсолютный, смешанный и относительный адреса ячейки, как они записываются?

7. Как выполняется копирование ячеек?

8. Как ввести в ячейку текст, формулу?

9. Как сохранить таблицу на диске?

10. Как загрузить таблицу с диска?

11. Как вывести таблицу на печать?

12. Каким образом осуществляется фиксация шапки таблицы?

13. Назовите основные элементы диаграммы.

14. Опишите порядок построения графиков функций.

15. Как добавить график функции на диаграмму?

16. Как изменить заголовок диаграммы или ее осей?

17. Как изменить стиль линий сетки?

18. Каким образом перемещается диаграмма по рабочему листу?

19. Как изменить размеры диаграммы?

20. Как установить вспомогательную ось на диаграмму?

21. Для чего необходима проверка данных?

22. Как создать список для выбора значений из списка?

23. Как вычисляется значение обратной матрицы для заданной матрицы?