В предыдущих публикациях я поделился информацией о том, как импортировать данные из Google Analytics в различные процессоры электронных таблиц (мы рассматривали импорт в и ). Но импорт — это только первый шаг на пути к полноценному анализу данных. После загрузки всей необходимой вам информации в редактор электронных таблиц, ее необходимо визуализировать и только потом анализировать .

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

Любая сводная таблица строится на основе определенной базы данных. В виде базы данных выступает массив, состоящий из полей и записей. Каждая строка (запись) в базе данных представляет собой информацию об отдельном случае, объекте или состоянии изучаемого объекта, а каждый столбец (поле) является параметром, свойством или признаком всех исследуемых объектов. Например, параметром может быть источник трафика, описание информации об источнике. В поле «источник трафика» будет, например, Google. Все поля базы данных разделяются на два типа: измерения («параметры» в терминологии Google Analytics) и меры («показатели» в терминологии Google Analytics). Измерением — это название или свойство объекта, в разрезе которых мы можем анализировать различные количественные показатели.

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

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

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

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

Для построения сводной таблицы в Microsoft Excel 2013 вам необходимо открыть скачанный ранее csv файл. После чего, установив курсор на любой из ячеек таблицы, нажмите Ctrl+A — этим действием вы выделите всю базу данных. На её основе мы будем строить сводную таблицу. Далее перейдите на вкладку «Вставка» и в группе «Таблицы» нажмите «Сводная таблица».
На этом процесс создания закончен и мы приступаем к работе со сводной таблицей. После создания таблицы в книге Excel будет создан новый лист. Он будет выглядеть так:
Как вы видите на скриншоте, этот лист состоит из самой сводной таблицы и конструктора сводной таблицы. Конструктор состоит, в свою очередь, из пяти частей:

• список полей;
• фильтры;
• колонны;
• строки;
• значения.

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

1. Поле Date с помощью правой кнопки мыши перетащим в область строк.
2. Поле Device category перетащим в область колонн.
3. Поле User type — в область фильтров.
4. Поле Session — в область значений.

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

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

Перенеся поле Sessions в область значений, мы заполнили область значений сводной таблицы информацией о количестве сеансов за каждый день по каждому типу устройств. В полях, которые были добавлены в область значений конструктора сводной таблицы, можно изменять агрегирующую функцию. По умолчанию для числовых полей применяется суммирование, но вы можете выбрать любую другую из предложенных агрегирующих функций. Для этого достаточно в области значений сводной таблицы кликнуть правой кнопкой мыши и в выпадающем меню «Итоги по» выбрать нужную функцию.
Остается вопрос: а что же нам дал перенос поля User type в фильтры? Чтобы это понять, давайте применим фильтр сводной таблицы и выведем в отчет информацию только по новым пользователям.

1. Откройте перечень элементов поля User type, нажав на ярлык с изображением воронки в области фильтров сводной таблицы.
2. Выберите элемент New user.
3. Нажмите ОК.

Теперь в область значений сводной таблицы выводится информация о количестве сеансов, совершенных только новыми пользователями в разрезе дат и типов устройств. Выше показан пример простейшей сводной таблицы, но на самом деле это далеко не весь функционал, который вы можете использовать для построения отчетов сводных таблиц. Любые параметры могут быть выведены в отчет в виде определенной иерархии. Для примера перенесем в область строк поле Week таким образом, чтобы оно находилось выше, чем поле Date. Отчет сводной таблицы будет сгруппирован не только по датам, но и по неделям. К тому же к каждой неделе будут прикреплены промежуточные итоги для более удобного анализа отображаемой информации. Теперь сводная таблица приобрела следующий вид:
Такие иерархии можно строить и в области колонн. Вы можете смотреть информацию на уровне недель и при необходимости детализировать информацию по количеству сеансов до уровня дат простым нажатием на иконку +/−.
Очень полезная функция сводных таблиц в Microsoft Excel — возможность дополнительных вычислений над любыми выведенными в таблицу показателями.

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

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

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

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

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

В нашем случае формула вычисляемого поля будет выглядеть как «=Bounces / Sessions». Дальше жмем OК.

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

По сути, это поле содержит название всех показателей (полей), выведенных в область значений. Для закрепления материала и преобразования сводной таблицы в более читабельный вид перетянем поле «Значения» в область строк под поле Date.
Теперь в таблице каждая дата разбита на две строки. В первой выводится доля сеансов по каждому типу устройства, во второй — показатель отказов для каждого типа устройств. Иногда в ходе анализа нам требуется каким-либо образом объединить некоторые элементы измерения в группы. Например, в данном случае нам может понадобиться объединить типы устройств mobile и tablet в одну группу и назвать ее Other.

Таким образом мы можем проанализировать различие между настольными устройствами и всеми остальными. Все, что нам необходимо сделать для группировки измерения, — это выделить нужные его элементы с помощью мыши, после чего вызвать контекстное меню правым кликом мыши и выбрать пункт «Группировать».
Элементы mobile и tablet будут объединены в группу, которой по умолчанию присваивается название «Группа 1». Чтобы переименовать группу, просто перейдите в ячейку с названием и введите новое Other.
После группировки некоторых элементов измерения область столбцов отчета сводной таблицы стала двухуровневой, так же, как и область строк после добавления в нее поля Week. Теперь вы можете анализировать информацию по типам устройств на двух уровнях, переключаясь между ними с помощью +/−.
Далее вы можете настроить внешний вид вашей сводной таблицы, используя готовые шаблонные стили, либо выбрав свой собственный. Для создания кастомного стиля с помощью мыши выберите любую ячейку, которая входит в область отчета сводной таблицы, и, перейдя на вкладку «Конструктор», выберите один из предложенных стилей оформления.
Также существует возможность добавления срезов и временных шкал. Хочу заметить, что этот функционал не доступен в старых версиях Microsoft Excel, возможность добавления срезов появилась в 2010 версии, а временные шкалы добавили только в 2013 году. Срез выполняет ту же функцию что и фильтр, который мы построили, добавив поле User type при построении сводной таблицы в область фильтров.

Единственное отличие заключается в том, что срез имеет более удобную визуализацию. Давайте добавим срез по Source. Для этого выделите любую ячейку, относящуюся к области отчета сводной таблицы, перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез».
После нажатия ОК на рабочий лист будет добавлен срез. Использовать срез можно так же, как и обычный фильтр. Давайте для примера в созданном нами срезе выберем элемент «google» и тем самым в отчет сводной таблицы выведем информацию о доле каждого типа устройств по каждой дате только по сеансам, совершенным из источника «google».
Зажав левый Ctrl, вы можете выбрать любое количество элементов среза, информацию по которым планируете вывести в отчет. Временная шкала работает по такому же принципу, как и срез, но строить ее можно только на основе полей, содержащих данные в формате даты. Создается она на вкладке «Анализ» с помощью кнопки «Вставить временную шкалу», находящейся в группе «Фильтры».

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

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

Сводные таблицы в Google Spreadsheets (Google таблицы)

Основное преимущество Google Spreadsheets — доступ к ним есть всегда и везде при условии наличия подключения к интернету. Именно это делает продукт наиболее удобным при совместной работе нескольких пользователей. Для построения сводной таблицы в Google Spreadsheets необходимо создать новую таблицу в своем Google Диске, перейдя по этой ссылке . После этого импортируйте в созданную таблицу скачанную ранее базу данных (скачать csv файл с базой данных). Чтобы загрузить базу данных в таблицу, в меню «Файл» выберите пункт «Импорт». В открывшемся диалоговом окне переходим на вкладку «Загрузка» и жмем кнопку «Выберите файл на компьютере», после чего выбираем скачанный ранее файл pivotTableDB.csv .
В диалоговом окне «Импорт файла» устанавливаем переключатель «Действие после импорта» в положение «Заменить текущий лист», а переключатель «Разделитель» устанавливаем в положение «Другое». В качестве разделителя вводим точку с запятой. После нажатия кнопки «Импортировать» данные будут загружены в вашу таблицу на «Лист1». Теперь можно приступить непосредственно к созданию сводной таблицы. Для этого надо выделить весь загруженный массив данных. Наиболее быстрым способом в этом случае будет перейти в ячейку A1 и последовательно использовать сочетание клавиш Ctrl + Shift + Стрелка вправо , затем Ctrl + Shift + Стрелка вниз .

Далее переходим в меню «Данные» и жмем на пункт «Сводная таблица».
Далее в Google таблице, так же как и в Microsoft Excel, будет создан новый лист с названием «Сводная таблица 1», областью сводной таблицы и редактором отчетов.
Редактор отчет так же состоит из четырех областей: «Строки», «Столбцы», «Значения», «Фильтры». Давайте пройдем путь построения сводной таблицы, описанный в примере выше. Для этого произведем те же четыре действия.

1. В область «Строки» добавляем поле Date.
2. В область «Столбцы» добавляем поле Device Category.
3. В область «Значения» добавляем поле Sessions.
4. В область «Фильтры» добавляем поле User type.

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

Для этого в области фильтра редактора отчетов в выпадающем списке «Показать» отмечаем галочкой элемент New Visitor. Далее, чтобы добавить возможность детализировать информацию не только по датам, но и по неделям, необходимо в редакторе отчетов в область строки добавить поле Week и перетащить его на уровень выше, чем поле Date. Теперь в отчете сводной таблицы представлены два уровня детализации, по неделям и датам.
Далее необходимо ввести название расчетного поля и формулу. В нашем случае названием поля будет «Показатель отказов», а формулой для расчета «=bounces / sessions».

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

Теперь сводная таблица имеет следующий вид:
Для каждого типа устройства добавлен дополнительный столбец с информацией о показателе отказов. Если хотите привести таблицу в более читабельный вид и показатель отказов выводить второй строкой для каждой даты, а не вторым столбцом для каждого типа устройств, в редакторе отчетов в области значений переставьте переключатель в положение «как: Строки». Отчет примет вид, в котором на каждую дату приходится две строки данных. В первой будет количество сеансов, во второй — показатель отказов.
На этом функциональные возможности Google таблиц заканчиваются. В целом, этого достаточно для построения визуализации данных для проведения анализа.

Сводные таблицы в LibreOffice и OpenOffice

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

Для создания сводной таблицы в меню «Файл» LibreOffice выберите пункт «Открыть», после чего укажите скачанный ранее файл pivotTableDB.csv . В диалоговом окне «Импорт текста» установите переключатель «Параметры разделителя» в положение «Разделитель», и в качестве разделителя установите «Точка с запятой». После нажатия ОК необходимая таблица данных будет загружена в документ. Теперь, когда у вас есть база данных, необходимо выделить ее нажатием Ctrl+A, и в в группе «Сводная таблица» (меню «Данные») нажать кнопку «Создать».
В качестве источника в диалоговом окне «Выбрать источник» устанавливаем переключатель в положение «Выбранное выделение». Нажимаем ОК. Далее появится диалоговое окно «Разметка сводной таблицы». Это своеобразный конструктор сводной табицы из приведенных выше примеров с Microsoft Excel и Google Spreadsheets. Чтобы построить сводную таблицу, аналогичную двум предыдущим примерам, сделайте следующие действия.

1. В область «Поля страниц» перетащите поле User type, так как в LibreOffice область «Поля страниц» — это область фильтров сводной таблицы.

2. В область столбцов перенесите поле Device category. По умолчанию область столбцов уже будет содержать поле с именем «Данные». С помощью этого поля вы можете изменять положение рассчитываемых метрик. Примерно так же, как мы делали это в Excel и Google Таблицах. Тогда мы располагали информацию так, чтобы каждая дата содержала две строки: одну с данными о сессиях, а вторую — о показателе отказов. Так же и в LibreOffice вы можете менять расположение вычисляемых данных, отображая их в строках или столбцах.

3. В область «Поля строк» перенесите сначала поле Week, после чего под этим полем расположите поле Date.

4. В область «Поля данных» перетащите поле Sessions. Для того, чтобы изменить агрегирующую функцию либо настроить дополнительное вычисление для рассчитываемого поля, достаточно дважды кликнуть на него левой кнопкой мыши и выбрать из списка нужную функцию либо дополнительное вычисление. Ранее мы в примере с Microsoft Excel устанавливали в качестве дополнительного вычисления «Процент от суммы по строке». Чтобы настроить подобное вычисление в LibreOffice после двойного клика по полю Sessions, расположенного в области данных, в открывшемся диалоговом окне «Поле данных» раскройте меню «Отображаемое значение», установите «Тип: % от строки» и нажмите ОК.

5. Для быстрого изменения уровня детализации откройте подменю «Параметры», находящееся в нижней части диалогового окна «Разметка сводной таблицы», и установите там флажки «Добавить фильтр» и «Разрешить переход к деталям». Нажмите OK.
В ваш документ будет добавлена сводная таблица, аналогичная приведенным выше примерам. С одним исключением: в данной сводной таблице мы не вывели поле «Показатель отказов», так как на момент написания статьи LibreOffice не поддерживает функционал рассчитываемых полей. У вас получится такая сводная таблица:

Заключение

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

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

Лабораторная работа №13

Создание электронных таблиц в OpenOffice.org Calc

Оборудование: ПК
Программное обеспечение: Windows , OpenOffice.org Calc .
Цель работы: приобретение и закрепление практических навыков работы в OpenOffice.org Calc

Теоретическая часть

Что такое Calc ?

Calc это модуль электронных таблиц OpenOffice.org (OOo). В электронную таблицу можно вводить данные, обычно числовые, и затем манипулировать этими данными для получения определенных результатов.

Можно также вводить данные и использовать Calc в режиме ‘Что-если’, изменяя некоторые из этих данных и наблюдая полученные результаты без необходимости полного повторного ввода остальных данных.

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

Электронные таблицы, листы и ячейки

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

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

Каждая электронная таблица может иметь много листов и каждый лист может состоять из большого количества индивидуальных ячеек. Каждый лист Calc может иметь максимум 65,536 строк и максимум 245 столбцов (от A до IV). Это составляет 16,056,320 индивидуальных ячеек на один лист.

Составные части главного окна Calc

При загрузке Calc на выполнение, главное окно имеет вид, изображенный на Рис. 1.

Задание №1. Форматирование данных в электронных таблицах
1. Запустить OpenOffice.org Calc.
2. Сохраните книгу под именем "Электронная таблица" в папке ЛР_№9 на диске H:
3.Создайте следующую таблицу. (Желтым цветом в таблице выделены номера строк и названия столбцов в Calc. Порядок выполнения см. ниже таблицы).

4. Курсор установить в ячейку A1 и набрать "Представление данных в OpenOffice.org Calc", размер шрифта 14, полужирный курсив.
5. Выделить ячейки A1-H1. (Курсор установить в центр ячейки A1, нажать левую клавишу и, не отпуская ее, протянуть до ячейки H1). На панели инструментов "Форматирование" нажать кнопку "Объединить ячейки" и поместить в центре.
6. Выделить столбцы A-H . (Курсор установить на имя столбца A (имя – это заглавная латинская буква в верхней части столбца), нажать левую клавишу и, не отпуская ее, протянуть до столбца F).
7. Выполнить команду "Формат \ Столбец \ Ширина" и установить ширину столбцов равной 4 см.
8. Курсор установить в ячейку A3 и набрать текст "Случайное число"
9. Курсор установить в ячейку B3 и набрать текст "Сл. число < 100"
10. Курсор установить в ячейку С3 и набрать текст "Денежный формат"
11. Курсор установить в ячейку D3 и набрать текст "Процентный формат"
12. Курсор установить в ячейку E3 и набрать текст "Дробный формат"
13. Выделить столбцы A-F, выполнить команду "Формат \ Столбец \ Оптимальная ширины". Оставить все без изменений и нажать кнопку ОК.
14. Курсор установить в ячейку A4, Вставка → Функция, тип функции – математические, выбрать функцию "RAND()". (Функция "RAND()" возвращает случайное число между 0 и 1).
15. Указатель мыши установить на маркер заполнения ячейки A4, нажать левую клавишу мыши и, не отпуская ее, протянуть до ячейки A14.
Маркер – это черная точка в правом нижнем углу ячейки. При установке указателя мыши на маркер заполнения он принимает вид тонкого черного креста. При этом происходит копирование записанной в ячейку формулы в следующие ячейки с изменением адресов ячеек.
16. Курсор установить в ячейку B4, и набрать формулу: =A4*100, нажать кл. ENTER (Имена столбцов А4, С12 и т.д. набирать только латинскими буквами).
17. Курсор установить на маркер заполнения ячейки B4 и скопировать функцию до ячейки B14
18. Курсор установить в ячейку C4, и набрать формулу: =A4*100, нажать кл. ENTER и скопировать формулу до ячейки C14.
19. Курсор установить в ячейку D4, и набрать формулу: =A4*100, нажать кл. ENTER и скопировать формулу до ячейки D14
20. Курсор установить в ячейку E4, и набрать формулу: =A4*100, нажать кл. ENTER и скопировать формулу до ячейки E14
21. Выделить ячейки A4-A14 и установить формат чисел ("Формат \ Ячейки \ Числа\ Числовой")
22. Выделить ячейки B4-B14 и установить числовой формат с тремя знаками после запятой ("Формат \ Ячейки \ Числа \ Особый") и в коде формата написать # ###,000
23. Выделить ячейки C4-C14 и установить денежный формат с двумя знаками после запятой ("Формат \ Ячейки \ Числа \ Денежный")
27. Выделить ячейки D4-D14 и установить процентный формат с одним знаком после запятой ("Формат \ Ячейки \ Числа \ Особый") и в коде формата написать 0,0%
28. Выделить ячейки E4-E14, установить дробный формат и вывести числа в виде простых дробей ("Формат \ Ячейки \ Числа \ Дробный")
29. Выделить ячейки A3-F14 и установить внешние и внутренние границы ("Формат \ Ячейки \ Обрамление")
30. Установить параметры страницы. (Формат\ Страница \ Страница, верхнее поле -1 см, нижнее – 4 см, левое – 3 см, правое – 1 см)
31. Выполнить команду "Сервис \ Параметры \ OpenOffice.org Calc \ Вид" и установить режим показа формул.
32. Выделить столбцы A-F, выполнить команду "Формат \ Столбец \ Оптимальная ширина"
33. Выполнить команду "Сервис \ Параметры \ OpenOffice.org Calc \ Вид" и убрать режим показа формул.
34. Выделить столбцы A-F, выполнить команду "Формат \ Столбец \ Оптимальная ширина"
35. Щелкните по кнопке "Сохранить" на панели инструментов или выполните команду Файл / Сохранить.
36. Перейти на лист 2. (Номера листов выводятся внизу экрана на горизонтальной линейке прокрутки). Создать таблицу из 10 строк.
37. Курсор установить в ячейку A1 и набрать заголовок: "Представление чисел в в OpenOffice.org Calc", размер шрифта 14, полужирный курсив.
38. Выделить ячейки A1-F1 и на панели инструментов "Форматирование" нажать кнопку "Объединить ячейки" и выровнять по центру.
39. Курсор установить в ячейку A3 и набрать текст "Случайное число <10000"
40. Курсор установить в ячейку B3 и набрать текст "0 знаков"
41. Курсор установить в ячейку С3 и набрать текст "3 знака после запятой"
42. Курсор установить в ячейку D3 и набрать текст " 2 знака до запятой "
43. Курсор установить в ячейку E3 и набрать текст "Целое число"
44. Выделить столбцы A-E, выполнить команду "Формат \ Столбец \ Оптимальная ширина"
45. Выделите ячейки A3-E3, выполните поворот текста на 60˚ ("Формат \ Ячейки \ Выравнивание").
46. Курсор установить в ячейку A4, и набрать формулу =RAND()*10000
47. Курсор установить на маркер заполнения ячейки A4 и скопировать функцию до ячейки A14
48. Курсор установить в ячейку B4, и набрать формулу =ROUND(A4;0)
49. Курсор установить на маркер заполнения ячейки B4 и скопировать функцию до ячейки B14
50. Курсор установить в ячейку C4, и набрать формулу =ROUND(A4;3), нажать кл. ENTER и скопировать формулу до ячейки C14
51. Курсор установить в ячейку D4, и набрать формулу =ROUND(A4;-2), нажать кл. ENTER и скопировать формулу до ячейки D14
52. Курсор установить в ячейку E4, и набрать формулу =INT(A4), нажать кл. ENTER и скопировать формулу до ячейки E14

53. Выполнить команду "Сервис \ Параметры \ OpenOffice.org Calc \ Вид" и установить режим показа формул. Должна получиться таблица следующего вида, состоящая из 10 строк:
54 Выделить столбцы A-F, выполнить команду "Формат \ Столбец \ Оптимальная ширина"
55. Выполнить команду "Сервис \ Параметры \ Вид" и убрать режим показа формул.
56. Выделить столбцы A-F, выполнить команду "Формат \ Столбец \ Оптимальная ширина"
57. Создайте два списка из пяти фамилий: первый – из студентов первой подгруппы; второй - из студентов второй подгруппы (Сервис / Параметр / Списки / OpenOffice.org Calc /Списки сортировки и нажать на кнопку Создать в "Элементы" построчно набрать фамилии и нажать кнопку добавить и OK).
58. В ячейке G3 набрать "1 группа".
59. В ячейке G4 набрать фамилию студента из первого списка, курсор установить на маркер заполнения и протянуть вниз.
60. В ячейке H3 набрать "2 группа".
61. В ячейке H4 набрать фамилию студента из второго списка, курсор установить на маркер заполнения и протянуть вниз.
62. Выделить ячейки A4-H14 и установить внешние и внутренние границы ("Формат \ Страница \ Обрамление", выбрать тип линии, цвет, щелкнуть по кнопкам "Внутренние" и "Внешние")
63. Сохранить книгу под именем "Электронная таблица" в папке ЛР_№9 на диске H:

Задание №2

1.Создайте новую книгу (Файл / Создать)
2. Сохраните книгу под именем "Объем продаж" в папке ЛР_9 на диске H:
3.Создайте следующую таблицу. (Порядок выполнения см. ниже таблицы).
Объем продаж

4.Курсор установить в ячейку A1 и набрать "Объем продаж", размер шрифта 14, полужирный курсив.
5.Выделить ячейки A1-H1 и на панели инструментов "Форматирование" нажать кнопку "Объединить" и выровнить по центру.
6.Курсор установить в ячейку A3 и набрать "№№".
7.Выделить ячейки А3 и А4 и выполнить команду Формат / Ячейки / Выравнивание, установить выравнивание как по горизонтали так и по вертикали «По центру» и на панели инструментов "Форматирование" нажать кнопку "Объединить".
8.Курсор установить в ячейку В3 и набрать "Наименование".
9.Выделить ячейки В3 и В4 и объединить их (см. выше).
10.Заполнить шапку таблицы по приведенному выше образцу.
11.Заполнить столбец "Наименование". (Набрать десять наименований различных продуктов).
12.Объем продаж в октябре будем вычислять как целое случайное число в интервале от 0 до 10. Для получения целых, случайных чисел в интервале от 0 до 10 необходимо в ячейку C5 ввести формулу:

=INT(RAND()*10)

13. Курсор установите на маркер заполнения ячейки C5 (правый нижний угол ячейки, появляется черный крестик), нажмите левую клавишу мыши и не отпуская ее протяните курсор вниз до строки №14.
14. Объем продаж в ноябре вычисляется как целое случайное число в интервале от 10 до 30. Для получения целых, случайных чисел в интервале от 10 до 30 необходимо в ячейку D5 ввести формулу:

=INT(RAND()*(30 - 10) + 10)

15. Курсор установите на маркер заполнения ячейки D5
16. Объем продаж в ноябре вычисляется как целое случайное число в интервале от 20 до 50. Для получения целых, случайных чисел в интервале от 20 до 50 необходимо в ячейку E5 ввести формулу:

=INT(RAND()*(50 – 20) + 20)

17. Курсор установите на маркер заполнения ячейки E5, нажмите левую клавишу и протяните курсор вниз до строки №14.
18. Для заполнения столбца «Продано всего» необходимо просуммировать Объем продаж за октябрь, ноябрь, декабрь. В ячейку F5 ввести формулу:

=C5+D5+E5

19. Курсор установите на маркер заполнения ячейки F5 , нажмите левую клавишу и протяните курсор вниз до строки №14.
20. Для получения случайных чисел в денежном формате в интервале от 50 до 150 необходимо в ячейку G5 ввести формулу:

=RAND()*(150 – 50) + 50)

21. Курсор установите на маркер заполнения ячейки G5 , нажмите левую клавишу и протяните курсор вниз до строки №14.
22. Для представления чисел в денежном формате выделить столбец с названием «Цена» (щелкнуть левой клавишей мыши по названию столбца, т.е. по букве G ), выполнить команду Формат / Ячейки / Числа / Денежный / Установить два знака после запятой, ОК.
23. Для нахождения значений столбца «Сумма» необходимо перемножить значения столбцов «Продано всего» и «Цена». В ячейку H5 ввести формулу:

=F5*G5

24. Для нахождения суммы «ИТОГО» установите курсор в ячейку С15 и ввести формулу:

=SUM(C5:C14)

25. Курсор установите на маркер заполнения ячейки C15 , нажмите левую клавишу и протяните курсор вправо до ячейки F15 .
26. Курсор установите в ячейку H15 и введите формулу:

=SUM(H5:H14)

27. Выделите таблицу и выполните команду Формат / Столбец / Оптимальная ширина.
28. Установите внешние и внутренние границы таблицы. Выделите таблицу, выполните команду Формат / Ячейки / Обрамление / щелкните по пунктам Внешние и Внутренние, выберите тип линии, ОК.
29. Установите параметры страницы (Формат\ Страница \ Страница)
30. Выполните предварительный просмотр. (Файл / Предварительный просмотр).
31. Закройте режим предварительного просмотра.
32. Сохранить книгу под именем "Объем продаж" в папке ЛР_9 на диске H:
33. Скопируйте таблицу с листа №1 на лист №2.
34. Установите режим показа формул. (Сервис \ Параметры \ OpenOffice.org Calc \ Вид ОК).
35. Выделите таблицу и выполните команду Формат / Столбец / Оптимальная ширина.
36. Установите параметры страницы.
37. Щелкните по кнопке "Сохранить" на панели инструментов или выполните команду Файл / Сохранить.
38. Перейдите на лист 3.
39. Создайте следующую таблицу (см. ниже) .
40. Количество проданных товаров за месяц - целое случайное число в интервале от 0 до 20, - цена - случайное число в денежном формате с двумя знаками после запятой в интервале от 10 до 100 руб. (Результат округлить до двух знаков после запятой). - план – целое случайное число в денежном формате интервале от 100 до 2000 руб.
41. Для нахождения суммы необходимо количество умножить на цену.
42. Для нахождения % выполнения плана необходимо сумму разделить на план и представить в процентном формате. (Результат округлить до двух знаков после запятой).
43. Для нахождения итоговой суммы сложите все.

Объем продаж


12. Установите внешние и внутренние границы таблицы. Выделите таблицу, выполните команду Формат / Ячейки / обрамление / щелкните по пунктам Внешние и Внутренние, выберите тип линии, ОК.
13. Установите параметры страницы.
14. Сохранить книгу (Щелкнуть по кнопке "Сохранить).
15. Создайте лист №4. (Вставка / Лист).
16. Скопируйте таблицу с листа №3 на лист №4.
17. Установите режим показа формул. Выполните команду Сервис \ Параметры \ OpenOffice.org Calc \ Вид / формулы и ОК.
18. Выделите таблицу и выполните команду Формат / Столбец / Оптимальная ширина.
19. Установите параметры страницы.
20. Переименуйте все листы книги под именами Таблица№1, Таблица№2, Таблица№3, Таблица№4 (Щелкнуть правой клавишей мыши по названию листа, выбрать пункт "Переименовать).
21. Сохранить книгу (Щелкнуть по кнопке "Сохранить).
22. Завершить работу в OpenOffice.org Calc.
23. Результат предъявить преподавателю.

Вывод по работе:

Создание, редактирование и оформление таблицы

1. Цель работы

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

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

2. Создание таблицы

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

Рис.1: Вставка таблицы

После вставки таблицы появится дополнительная инструментальная панель «Таблицы» :

Рис.2: Инструментальная панель "Таблицы"

Если в процессе работы вы случайно закроете данную панель, то вернуть ее можно через главное меню Вид → Панели инструментов → "Таблица" (щелчок левой кнопкой мыши по названию инструментальной панели приведет к открытию/закрытию выбранной панели).

3. Перемещение по таблице

Перемещение по таблице выполняется с помощью мыши, клавиши TAB или клавиш управления курсором.

4. Выделение элементов таблицы

Способы выделение элементов таблицы с помощью мыши приведены в таблице:

Выделение возможно и с помощью команды: Главное меню: Таблица → Выделить - выделяется строка или столбец, в которой находится курсор ввода текста, ячейка или вся таблица.

5. Вставка строк

Вставка строки осуществляется с помощью кнопки «Вставить строку» на панели инструментов Таблица : новая строка вставиться под текущей строкой (текущей строкой является та в ячейке, которой в данный момент находится курсор).

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

Произвольное число строк можно вставить через главное меню: Таблица → Вставить → Строки… В появившемся диалоговом окне требуется указать сколько строк необходимо добавить и их положение – до или после текущей строки.

Рис.3: Диалоговое окно "Вставить строки"

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

6. Вставка столбцов

Вставка столбцов осуществляется аналогичным образом или с помощью соответствующей кнопки «Вставить столбец» на панели инструментов Таблица или через главное меню Таблица → Вставка → Столбцы…

7. Вставка текста перед таблицей

Поместить курсор в левой верхней ячейке первой строки таблицы и нажать клавишу Enter.

8. Удаление строк, столбцов

Для удаления строк или столбцов необходимо:

• выделить необходимые элементы таблицы (строки или столбцы);
• выполнить команду «Удалить…» пункта меню «Таблица» либо с помощью соответствующих кнопок на панели инструментов Таблица

Внимание! Клавиши «Delete» и «Backspace» удаляют только содержимое ячеек!

9. Оформление таблицы

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

Диалоговое окно «Оформление таблицы» содержит несколько вкладок:

• вкладка «Обрамление» служит для определения свойств линий таблицы;
• вкладка «Фон» определяет заливку, цвет фона строк, столбцов, ячеек или всей таблицы;

Рассмотрим как изменить тип и цвет линий таблицы с помощью вкладки «Обрамление» :

Рис.4: Диалоговое окно "Оформление таблицы: Обрамление "

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

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

В разделе «предопределенные» содержатся следующие пиктограммы:

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

• Серый цвет линии – будет оставлен предыдущий стиль линии
• Белый цвет линии – линия будет удалена.

Изменять стиль линий таблицы (ячеек) можно и с помощью соответствующих кнопок на панели инструментов «Таблицы»

Порядок действий:

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

10. Изменение ширины столбца и высоты строки

Для изменения ширины столбца необходимо:

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

Для изменения высоты строки необходимо:

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

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

11. Объединение ячеек

Для объединения ячеек необходимо:

• или выделить все объединяемые ячейки и выполнить команду меню «Таблица» / «Объединить ячейки»;
• или выделить все объединяемые ячейки и воспользоваться одноименной кнопкой панели инструментов «таблицы»;

12. Разбиение ячеек

Для разделения ячеек необходимо:

• выделить разделяемые ячейки;
• выполнить команду меню «Таблица» / «Разбить ячейки» или воспользоваться одноименной кнопкой панели инструментов «таблицы». В появившемся окне диалога задать количество ячеек, на которое предполагается разбить каждую из выделенных ячеек и указать направление разбиения по вертикали или по горизонтали. Данную операцию можно провести, используя кнопку «Разбить ячейки» панели «Таблицы»..