ЛАБОРАТОРНАЯ РАБОТА 10. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ

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

Выполняйте лабораторную работу в пакете, который установлен на вашем компьютере: Microsoft Excel или LibreOffice Calc.

10.1. MICROSOFT EXCEL

10.1.1. Интерфейс программы, адресация ячеек

Загрузите Excel. Как и у остальных программ пакета Microsoft Office 2010, у Excel новый интерфейс Microsoft Office Fluent, включающий панель инструментов «Лента» (при работе в более ранних версиях, большинство команд вы найдёте в соответствующих меню).

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

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

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

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

Ниже ленты расположена Строка формул, в которую вводят формулы вручную, или с помощью кнопки fx мастера Вставить функцию.

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

204

Рабочая область представлена сеткой ячеек для размещения в них данных и формул. Слева и сверху рабочей области расположены заголовки – панели имен строк и столбцов (режимы именования которых описаны ниже), при нажатии на которые мышью, можно выделить целиком строку или столбец. При удержании и перемещении мыши, будет выделено несколько строк или столбцов. Также выделить строку относительно текущей (или выделенной) ячейки можно клавишами Shift+Пробел, а столбец – Ctrl+Пробел, если уже было выделено несколько ячеек, то выделяется соответствующее выделению количество строк или столбцов. Выделить весь лист можно клавишами Ctrl+A или ячейкой без имени в верхнем левом углу с изображением треугольника. Для удаления содержимого выделенных ячеек нажмите Delete. Если необходимо изменить размер строки или столбца, то поместите курсор мыши на границу ячейки заголовка с именем (курсор при этом изменит вид), нажмите, и, удерживая, передвиньте границу. Если в ячейке уже введён текст, то двойной щелчок левой кнопкой мыши автоматически изменит размер строки или столбца под максимальный размер данных. Строки и столбцы также можно скрыть (например, промежуточные данные или для уменьшения занимаемого таблицей места) с помощью команды контекстного меню. Для отображения скрытых строк и столбцов, выделите два соседних, к скрытой области, строки или столбца, и в контекстном меню выберите команду Показать. В случае эксперимента, когда были выделены все (Ctrl+A) и скрыты ячейки, или когда сразу нужно показать все скрытые блоки, выберите команду

Главная/ Формат (блока Ячейки)/ Скрыть или отобразить. При выполнении действий также нужно помнить, что последние действия можно отменить клавишами Ctrl+Z.

Отображение линейки рабочей области доступно в режиме Разметка страницы.

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

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

205

новом документе создаётся три листа. Команды контекстного меню (нажатием правой кнопки мыши) позволяют добавлять, удалять, копировать и менять параметры страниц и их закладок. Справа списка ярлыков расположен ярлык-команда Вставить лист (Shift+F11). С помощью контекстного меню переименуйте листы в Накладная,

Вычисления и Ветвление. Вставьте новые листы Тест, График и Фильтр.

Измените цвет ярлыков, используйте светлые оттенки палитры. Перейти на соседние листы можно с помощью клавиш Ctrl+PgUp и Ctrl+PgDn (переключите страницы и вернитесь к исходной). Правее ярлыков и в правой части рабочей области расположены полосы прокрутки. Прямоугольная кнопка слева горизонтальной полосы прокрутки позволяет менять размер полосы, что может освободить место для отображения ярлыков. Кнопка-маркер (в виде вытянутого прямоугольника) справа горизонтальной полосы прокрутки и аналогичная сверху вертикальной полосы прокрутки, позволяют их перетягиванием разделить лист на части для одновременного просмотра далеко расположенных частей таблицы. Каждая часть имеет свою полосу прокрутки. Разделите лист на отдельные области. Проверьте, что можно пролистывать содержимое для каждой из них отдельно, верните вид к одному листу, перетянув соответствующие маркеры в исходное положение. Некоторые листы можно скрыть из списка (например, на них расположены справочные данные, или данные промежуточных вычислений). Также данные листа можно защитить от случайного или намеренного удаления и изменения, при этом можно задать пароль.

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

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

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

206

шаблона, необходимо будет при сохранении присвоить имя. Шаблоны могут располагаться в папках для всех пользователей, персонально в профиле пользователя, а также на сайте Office.com.

Вызовите справочную систему, найдите и прочитайте раздел «Форматы Open XML и расширения имен файлов», определите, какие расширения файлов использует Excel. Также в справке найдите раздел «Сочетания клавиш в Excel», при выполнении лабораторной работы записывайте горячие клавиши операций. В статусной строке справки слева выводится область поиска и справа кнопка переключения хранилища информации. При автономной работе справка выводится из хранилища локального компьютера, при подключении к интернету – с сайта Office.com. Использование онлайн-справки повышает вероятность получения исчерпывающей информации, т.к. база знаний сервера Office.com постоянно пополняется.

10.1.2. Запись данных и формул, адресация, форматирование

Имена строк и столбцов в Excel можно отображать в цифровом режиме или в буквенно-цифровом режиме. В первом случае, как имена строк, так и имена столбцов обозначаются цифрами, а в формулах используется запись RnCm, где n и m – соответственно номера строки (Row) и столбца (Column) ячейки. Так, адрес верхней левой ячейки записывается как R1C1. Во втором режиме имена столбцов обозначаются буквами, что не требует использования префиксов R и C в адресе. Так, эта же ячейка (R1C1) в этом режиме будет называться A1 (как в игре Морской бой). Выбрать режим можно в окне Файл/ Параметры/

Формулы, раздел Работа с формулами, параметр Стиль ссылок R1C1.

Если галочка у параметра установлена – снимите её и нажмите OK.

Как было сказано выше, ячейки могут содержать как данные, так и формулы. Данные могут быть разных типов. Численные данные в качестве десятичного разделителя используют знак, заданный в настройках системы. Для европейских стран по умолчанию принято использовать запятую. Необходимо помнить, что при написании программы в VBA (макросы), десятичным разделителем в числах является точка, что описано в правилах языка. Excel может автоматически преобразовывать вид данных, например дату. Местным

207

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

Ввод формулы в ячейке всегда начинается со знака «=», после него записываются операнды, разделяемые операторами вычислений. Формула может содержать как константы, так и ссылки на ячейки, содержащие данные. Формула вычисляется слева направо в соответствии с определённым порядком для каждого оператора в формуле. Ниже приведём арифметические, текстовые и логические операторы в порядке убывания приоритета:

операторы ссылок (двоеточие, один пробел, точка с запятой)

изменение знака (унарный минус)

процент (%). Установка у числа, адреса или формулы знака процента равносильно умножению на 0,01 (или делению на сто).

возведение в степень (^)

умножение (*) и деление (/)

сложение (+) и вычитание (–)

конкатенация, слияние двух текстовых строк в одну (& – амперсанд)

сравнение (=, <, >, <=, >=, <>)

Для изменения порядка вычисления используют круглые скобки. Часто допускают ошибку при вычислении выражения подобного x1/3 (допустим, значение x хранится в A1), многие начинающие пользователи записывают это выражение как =A1^1/3, что неверно, т.к. согласно приоритету сначала выполняется возведение в степень, а потом деление, необходимо вычисление значения степени взять в скобки: =A1^(1/3).

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

208

Введите в ячейке A3 значение пн (кратное обозначение дня недели – понедельник) и нажмите Enter для завершения ввода (далее необходимость нажатия клавиши Enter указываться не будет, самостоятельно завершайте ввод данных в ячейки этой клавишей). Выделите эту ячейку мышью (поднесите к ней курсор и кратко нажмите левую кнопку), поднесите курсор к маркеру заполнения в правом нижнем углу ячейки, обозначенному жирной точкой, курсор изменит вид на тонкий черный крестик. Нажмите, удерживайте левую кнопку мыши, и передвигайте курсор вниз, до 16й строки, Excel автоматически заполнит ячейки сокращёнными обозначениями дней недели. В ячейке B3 введите слово Понедельник. Заполните значениями ячейки до B16 включительно. Дважды щёлкните на правую границу имени столбца B для автоматического подбора ширины столбца по ширине данных. В ячейке C3 введите янв, в ячейке D3 – Январь. Заполните самостоятельно ячейки снизу введенных значений с помощью маркера заполнения названиями месяцев. Excel поддерживает более сложные правила заполнения. В ячейку E3 введите значение 1.янв. При копировании с помощью маркера заполнения, последовательно будет изменяться значение дня. Создайте таким образом список дат до середины февраля. Для того чтобы просто скопировать значение ячейки без циклического перебора значений списка, копирование нужно производить принажатой клавише Ctrl. Создать список своей циклической последовательности можно командой

Изменить списки раздела Общие/ Создавать списки для сортировки и заполнения окна Файл/ Параметры/ Дополнительно.

При заполнении списка цифр, Excel будет копировать значение числа ячейки, т.к. не задана последовательность. Для создания последовательности с шагом единица, нужно производить заполнение при нажатой клавише Ctrl. Поместите в ячейку F3 число 1. С помощью маркера заполнения при нажатой клавише Ctrl заполните ячейки снизу числами до 20. Для создания последовательности с шагом, отличным от единицы, необходимо при заполнении выделить две соседние ячейки, содержащие значения последовательности, Excel высчитает разницу, и заполнит последующие ячейки с этим шагом. В ячейку G3 поместите число 1, в G4 введите 3. Мышью выделите обе ячейки и маркером заполнения у ячейки G4 заполните ячейки ниже до строки 14. В H3 поместите число 0, в H4 –

209

будет скопирована, и в каждой ячейке будет содержать адреса на свои соседние ячейки (одну справа и через одну справа). Для просмотра всех формул листа выполните команду Формулы/ Показать формулы. Режим R1C1 отображения формул более наглядно показывает, что формулы заданы в относительных координатах. Переключите отображение в режим R1C1. Вы видите, что формулы одинаковы, и имеют вид: =RC[– 1]*RC[–2]. Отсутствие значения у префикса R указывает, что используется текущая строка, число у префикса C указывает столбец, квадратные скобки указывают адрес относительно текущей ячейки, минус у числа – смещение влево или вверх, плюс (можно опустить) – вправо и вниз. Т.е. ясно видна формула произведения ячейки столбца левее и ячейки на два столбца левее. Отключите режим R1C1. Отключите режим Показать формулы.

Вячейку F4 введите формулу =E4*$C$1, для этого введите «=», затем нажмите на ячейку E4, введите «*», нажмите на ячейку C1, нажмите клавишу F4, последний адрес будет преобразован из вида C1 в вид $C$1 (при последовательном нажатии F4 адрес циклически преобразуется в C$1, $C1, C1). Знак доллара при отключенном режиме R1C1 устанавливает абсолютную адресацию, что означает неизменность адреса или его части при копировании формулы в другие ячейки. Таким образом, часть адреса фиксируется, «замораживается». Адресация, когда

узначения столбца и строки есть знак доллара, называется абсолютной, когда нет – относительной, когда только у строки или столбца – смешанной. Скопируйте формулу ячейки F4 в ячейки F5 и F6. Включите режим отображения формул и R1C1, сравните формулы, выключите режимы. При самостоятельной работе с формулами в дальнейшем можете использовать как включенный, так и отключенный режим R1C1 на своё усмотрение.

Вячейку B7 введите «ИТОГО:», выделите ячейку жирным. Выделите ячейки с E4 по E7, выполните команду Главная/ Автосумма (или Формулы/ Автосумма). В ячейку E7 автоматически будет вставлена формула =СУММ(E4:E6), и вычислена сумма указанного диапазона. Как видно, диапазон адресов ячеек разделяется двоеточием. Выделите ячейку E7 жирным.

211

Вычислите сумму НДС85: в ячейку B8 введите текст: «В том числе НДС:». Выделите мышью ячейки с F4 по F6, перейдите в поле Имя, находящееся левее поля редактирования формул (под лентой), введите имя диапазона «НДС», и нажмите Enter (на вкладке ленты Формулы есть блок команд Определённые имена для работы с именованными ячейками и группами ячеек). Перейдите в ячейку E8, командой Вставить функцию (кнопка fx слева строки редактирования формул) откройте окно мастера формул. По умолчанию выбрана категория 10 недавно использовавшихся функций. Как раз последней мы использовали функцию суммирования. Выберите из списка «СУММ» и нажмите OK. В следующем окне, в поле Число 1 введите «НДС» и нажмите Enter. В ячейке будет высчитана сумма именованного диапазона.

Для указания нескольких диапазонов в качестве аргумента функции используют точку с запятой. Например, запись =СУММ(E4:E6;G4:G6) вычислит сумму ячеек двух несвязанных диапазонов. Пробел может быть использован в качестве оператора пересечения множеств, так, оператор =СУММ(E4:E6 E5:E8) вычислит сумму диапазона E5:E6, который является общим для двух указанных диапазонов.

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

имя должно начинаться с буквы, обратной косой черты «\» или символа подчеркивания «_»;

в именах можно использовать любые числа;

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

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

нельзя использовать имена, которые могут трактоваться как ссылки на ячейки, например, $B$5, X1;

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

212

в качестве имен можно использовать любые одиночные буквы, кроме букв R и C;

имя может быть длиной до 255 символов, регистр букв не различим. Выделите диапазон ячеек с A3 по F8, выполните команду Главная/

Границы/Все границы, не снимая выделения выполните там же команду Толстая внешняя граница. Выделите ячейки A6:F6, в списке границ выберите Толстая нижняя граница. Выделите ячейки A2:F2, выполните команду Главная/ Объединить и поместить в центре. Выделите получившийся заголовок жирным. Выделите диапазон A2:F8, выполните команду Разметка страницы/Область печати/ Задать. Просмотрите результат в области предварительного просмотра команды Печать (Ctrl+P). Удобной возможностью печати являются функции масштабирования (Вписать лист на одну страницу и Вписать все столбцы на одну страницу).

Выделите ячейку E7, нажмите Ctrl+C для копирования содержимого. Перейдите на лист Тест, в ячейку B18, выполните команду контекстного меню Специальная вставка/ Вставить связь. В ячейку без копирования формата вставлена абсолютная ссылка на ячейку первого листа со значением =Накладная!$E$7. Заметьте, что можно адресовать ячейки других листов, указав их имя, отделенное от адреса самой ячейки восклицательным знаком. Выделите ячейку B19, вставьте значение из буфера обмена (Ctrl+V). Вставлена формула, вычисляющая сумму диапазона трёх вышестоящих (от текущей) ячеек листа Тест.

Формат текста в ячейках таблицы можно задавать с помощью команд вкладки ленты Главная, с помощью команд контекстного меню, окна Формат ячеек, вызываемого клавишами Ctrl+1, или из контекстного меню, или командой Главная/ Формат/ Формат ячеек, угловыми кнопками блоков команд Шрифт, Выравнивание, Число.

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

Перейдите на лист Накладная, для диапазонов ячеек C4:C6, E4:E8, F4:F6 в окне Формат ячеек на вкладке Число выберите Числовой формат,

число десятичных знаков 2, включить разделитель групп разрядов. Для

213