Как сделать сводную таблицу в либре офис пошаговая инструкция - Большая энциклопедия инструкций и руководств

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

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

Цель работы: сформировать
навыки по использованию возможностей Calc для построения сводных таблиц.

Теоретические сведения

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

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

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

Для создания сводной таблицы:

–
Установите
курсор в любой ячейке внутри списка.

–
Выберите
команду Данные / Сводная таблица / Создать. Появится диалоговое окно Выбор
источника. Установите флажок Текущее выделение и подтвердите выбор
нажатием кнопки ОК.

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

Рассмотрим
технологию создания сводной таблицы на примере.

Ход работы

1.
Загрузите
электронную таблицу Calc, используя Пуск /
Офис / Электронная таблица.

2. Откройте файл «Работа3».

3. Перейдите на Лист 8. Переименуйте
лист как «Сводная таблица».

4.
Скопируйте
с листа Ввод данных всю область данных и вставьте ее на лист Сводная
таблица начиная с ячейкиА1.

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

1.5.1.
Щелкните
левой кнопкой мыши по любой ячейке внутри списка.

1.5.2.
Выполните
команду Данные / Сводная таблица / Создать. На данном
этапе выдается запрос об источнике исходных данных. Проверьте правильность предложенного
программой диапазона и нажмите ОК.

1.5.3.
В
открывшемся диалоговом окне представлены Шаблон сводной таблицы и Список
полей сводной таблицы.

1.5.4.
Раскройте
список Источник и назначение и в разделе Назначение выберите
Новый лист (сводная таблица будет располагаться на отдельном листе).

1.5.5.
Сформируйте
строки и столбцы будущей сводной таблицы:

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

–
в
область Поля строк перетащите Класс;

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

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

–
в
область Поле данных перетащите Средний балл. Обратите внимание,
что по умолчанию установится функция Сумма. Щелкните по ней два раза
левой кнопкой мыши и выберите функцию Максимум (рис. 1).

Рис. 1. Создание сводной таблицы

1.5.7.
Для
просмотра сводной таблицы нажмите кнопку ОК.

6.
Проанализируйте
полученную сводную таблицу, которая создана на отдельном листе Сводная
таблица_Лист1_1. Измените представление данных в сводной таблице, выбирая
различные опции в раскрывающихся списках полей. Выберите из списков Класс,
Изучаемый предмет и Фамилия и проанализируйте, как меняются
данные в сводной таблице.

7. Убедитесь, что сводная таблица
легко обновляется:

– перейдите на лист Сводная таблица;

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

– перейдите на лист Сводная таблица_Лист1_1;

– выполните команду Данные / Сводная
таблица / Обновить;

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

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

9. Сохраните файл.

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

1. Раскройте понятие «сводная
таблица».

2. Для чего служит сводная таблица?

3.
Опишите
пошагово, какие действия следует совершить для создания сводной таблицы.

4.
Для
чего предназначены области «Поля страниц» и «Поля данных» в разметке будущей
сводной таблицы?

5. Можно ли обновлять данные
в сводной таблице?

Выпишите ответы на вопросы в тетрадь для
проверки.

Источник

Подробности: Категория: 8. Расчеты в Calc; Опубликовано 26.08.2011 16:03; Автор: Шитов В.Н.; Просмотров: 4130

Сводная таблица является вариантом фильтрации данных и выборки по заданным критериям.

Для создания сводной таблицы выделите данные, которые должны попасть в сводную таблицу. Выделять смежные ячейки можно с помощью клавиши Shift. Несмежные ячейки можно с помощью клавиши Ctrl. Выполните команду Данные → Сводная таблица → Создать. После этого открывается окно Выбрать источник (Рис. 1).

Рис. 1. Окно Выбрать источник

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

Рис. 2. Распределение полей сводной таблицы

В сводной таблице пользователь сам определяет, где должны располагаться поля относительно друг друга. Слева находится шаблон сводной таблицы, состоящий из элементов. Справа — имеющиеся в таблице поля. Для того, чтобы связать поля таблицы с элементами шаблона нужно подхватить мышью поле и перетащить на элемент сводной таблицы. Обратите внимание, на примере видно, что я подхватил поле Цена в $ и перетащил это поле в элемент Поля столбцов, но еще не отпустил. Видно, что белая стрелка держит небольшой прямоугольный значок. Так как сводная таблица создается самим пользователем, то всегда можно удалить неудавшуюся таблицу и создать новую. Перетаскивать все поля из исходной таблицы в сводную не обязательно. В каждый элемент сводной таблицы не обязательно помещать только одно поле из исходной таблицы: все решает сам автор сводной таблицы: никаких ограничений и запретов. После связывания полей исходной таблицы с элементами сводной таблицы нажмите на кнопку ОК. После этого многие хватаются за сердце и ищут глазами валидол: на месте исходной таблицы появляется сводная. Спокойно, граждане! Вы просто не обратили внимание, что сводная таблица создана на отдельном рабочем листе и имя этого листа Сводная таблица и номер исходного листа (Рис. 3). Если Вы сомневаетесь в этом, то в нижнем левом углу рабочего листа предлагаются миниатюрные треугольные кнопочки: щелкните по левой и получите исходный лист.

Рис. 3. Сводная таблица

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

Рис. 4. Данные по фильтрованной записи

Источник

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

Самый удобный и быстрый инструмент для преобразования массива данных в информативный отчет — сводные таблицы. В основе сводных таблиц лежит технология 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 будет создан новый лист. Он будет выглядеть так:Как вы видите на скриншоте, данный лист состоит из самой сводной таблицы и конструктора сводной таблицы. Конструктор состоит, в свою очередь, из пяти частей:

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

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

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

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

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

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

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

Теперь в область значений сводной таблицы выводится информация о количестве сеансов, совершенных только новыми пользователями в разрезе дат и типов устройств. Выше показан пример простейшей сводной таблицы, но на самом деле это далеко не весь функционал, который вы можете использовать для построения отчетов сводных таблиц. Любые параметры могут быть выведены в отчет в виде определенной иерархии. Для примера перенесем в область строк поле 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. Для этого выделите любую ячейку, относящуюся к области отчета сводной таблицы, перейдите на вкладку «Анализ» и в группе «Фильтры» нажмите иконку «Вставить срез».Откроется диалоговое окно «Вставка срезов», в котором вам необходимо выбрать, по каким полям вы хотите создать срезы. В нашем случае это поле 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», областью сводной таблицы и редактором отчетов.Редактор отчет так же состоит из четырех областей: «Строки», «Столбцы», «Значения», «Фильтры». Давайте пройдем путь построения сводной таблицы, описанный в примере выше. Для этого произведем те же четыре действия.

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

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

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

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

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

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, это даже не половина всех существующих возможностей по построению и использованию сводных таблиц.

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

Оригинал публикации на блоге Netpeak

Перепубликация статьи на CMSmagazine

ИНСТРУМЕНТАЛЬНЫЕ СРЕДСТВА ИНФОРМАЦИОННЫХ ТЕХНОЛОГИЙ

БАЗОВЫЕ ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ. ЭЛЕКТРОННЫЕ ТАБЛИЦЫ

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

к выполнению лабораторной работы

Брянск 2018

ЦЕЛЬ РАБОТЫ

Наиболее распространенными средствами обработки информации и оперативного выполнения расчетов являются электронные таблицы. Целью данной работы является получение навыков работы при обработке информации в LibreOffice Calc.

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

Продолжительность лабораторной работы – 2ч

ОБЩИЕ СВЕДЕНИЯ ОБ ЭЛЕКТРОННОЙ ТАБЛИЦЕ CALC ПАКЕТА LIBREOFFICE.

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

Рисунок 1 — Главное рабочее окно LibreOffice Calc

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

бухгалтерский и банковский учет;
планирование распределение ресурсов;
проектно-сметные работы;
инженерно-технические расчеты;- обработка больших массивов информации; — исследование динамических процессов.

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

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

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

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

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

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

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

Z, и комбинациями букв АА, АВ,…, IV,

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

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

Модель ячейки в Calc

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

адрес;
содержимое;
изображение;
формат;
имя;
примечание (комментарий).

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

Calc позволяет использовать стиль ссылок А1.

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

Адресация указывается как буква обозначающая столбец и цифра обозначающая номер строки.

=A2 * B1

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

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

Абсолютный вид ссылок

=$A$2 * $B$1 имя столбца, имя строки, которые останутся неизменным, при копировании формулы.

Смешанный вид ссылок

=$A2 * B$1 =A$2 * $B1

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

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

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

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

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

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

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

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

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

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

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

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

Основными объектами, над которыми производятся действия в электронных таблицах, являются ячейки и диапазоны ячеек (блоки).

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

Примеры блоков: А1 (ячейка); А1:А9 (столбец); В2:Z2 (строка); B2:D4 (прямоугольная область).

Неотъемлемым элементом рабочего поля таблицы является курсор. В ЭТ термин “курсор” используется в следующих случаях:

курсор ЭТ — жирная рамка вокруг текущей ячейки, перемещается с помощью клавиш управления курсором;
текстовый курсор — мигающая (или не мигающая) черточка, отмечающая положение текущего символа при редактировании содержимого ячейки.

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

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

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

число
формула
текст

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

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

десятичные числа записываются через запятую: 3,5; -0,0045, либо через точку: 3.5; -0.0045, в зависимости от установленных параметров. Изменение вида разделителя целой и дробной части производится в меню Сервис/ Параметры/ Международные.

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

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

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

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

Убедитесь в том, что активна (выделена курсивной рамкой) та ячейка, в которой вы хотите получить результат вычислений.
Ввод формулы начинается со знака “=”. Этот знак вводится с клавиатуры.
После ввода знака “=” Calc переходит в режим ввода формулы. В этом режиме, при выделении какой-либо ячейки, ее адрес автоматически заносится в формулу. Это позволяет избавить пользователя от необходимости знать адреса ячеек и вводить их в формулу с клавиатуры.
Находясь в режиме ввода формулы, вы последовательно указываете левой кнопкой мыши на ячейки, хранящие некие числовые значения, и вводите с клавиатуры знаки операций между исходными значениями.

§ Знаки операций должны вводиться между адресами ячеек.

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

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

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

§ “Зеленая галочка” на панели ввода формулы завершает ввод формулы, и оставляют курсор в той же ячейке.

Например, если в ячейке D2 должна помещаться разность чисел из ячеек B2 и C2, то после установки курсора на D5 следует указать мышью на B2, ввести с клавиатуры знак “-”, указать мышью на C2 и нажать или “зеленую галочку”.

В формулах можно использовать числовые константы (-4,5), ссылки на блоки (D4),

(A3:D8), знаки арифметических операций, встроенные функции (СУММ, МАКС, SIN и т.д.) Возведение в степень ^

=3^2

Умножение *

=А8*С6

Деление /

=D4/N5

Сложение +

=B2+5

Вычитание —

=9-G6

Равно =

Меньше

Больше

Меньше или равно

Больше или равно = Не равно Диапазон : =СУММ(А1:С10), если какая то ячейка пустая в диапазоне, то автоматически считается, что она равна 0.

Объединение диапазонов ;

=СУММ(А1;А2;А6:D8)

Максимум

МАКС

=МАКС(А3:С5), если какие то ячейки пустые, но есть не пустые, то за максимум берется самое максимальное значение, если все пустые, то возвращается 0.

Минимум

МИН

=МИН(Е2:Р7)

Функция ЕСЛИ

=ЕСЛИ(A1=5;A2+A3;B2+100) – если A1=5 то сложить A2 и A3 иначе B2+100.

=ЕСЛИ(A1

Функция среднего значения СРЗНАЧ:

СРЗНАЧ(A3:D7)

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

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

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

Щелкнете левой клавишей мыши в строке формул.
Нажмите .
Дважды щелкните мышью на ячейке.

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

Неверный формат ячейки может быть изменен только выбором другого формата в меню Формат / Ячейка.

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

Не пытайтесь исправить ошибку непосредственно в ячейке, вряд ли это удастся, так как скрытый формат этой ячейки уже сформирован. Поэтому нужно исправлять сначала формат ячейки на правильный с помощью меню Формат / Ячейка / Число.

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

Примеры ошибок:

#ИМЯ?

адрес ячейки введен с клавиатуры в режиме кириллицы #ЗНАЧ!

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

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

От проведения однотипных действий в каждой ячейки строки (или столбца) избавляет следующий прием копирования формулы:

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

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

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

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

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

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

Для фиксации адреса ячейки используется знак “$”.

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

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

Например, при копировании формулы = $A4+$A5, находящейся в ячейке A2, в ячейку

B3 получим в этой ячейке формулу =$A5+$A6, при копировании = A4+$A5, получим = B5+

$A6, при копировании = A4+A$5, получим =B5+B$5, при копировании = $A$4+$A$5, получим = $A$4+$A$5. Копирование помогает избежать ввода однотипной формулы вручную для обработки целого столбца или строки однотипных данных каждого элемента строки или столбца. Варьирование меняющейся и фиксированной ссылки на ячейку позволяет управлять процессом организации формул расчета для групп данных в столбцах, строках и таблицах. Копирование можно осуществить с помощью мышки или используя клавиатуру — Ctrl-Ins, и вставку Shift-Ins.

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

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

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

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

Специальные термины, применяемые при построении диаграмм:

-Ось X называется осью категорий и значения, откладываемые на этой оси, называются категориями.

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

-Легенда – расшифровка обозначений рядов данных на диаграмме.

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

Последовательность действий, при построении диаграммы

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

После построения диаграммы можно изменить:

-размеры диаграммы, потянув за габаритные обозначения, которые появляются тогда, когда диаграмма выделена;

-положение диаграммы на листе, путем перетаскивания объекта диаграммы мышью;

-шрифт, цвет, положение любого элемента диаграммы, дважды щелкнув по этому элементу левой кнопкой мыши;

-тип диаграммы, исходные данные, параметры диаграммы, выбрав соответствующие пункты из контекстного меню (правая кнопка мыши).

Диаграмму можно удалить: выделить и нажать .

Диаграмму, как текст и любые другие объекты в LibreOffice Calc, можно копировать в буфер обмена и вставлять в любой другой документ.

Задание.

Для выполнения заданий вам могут пригодиться функции работы с базами данных, например, dcount, dmax, dmin, daverage, являющиеся аналогами функций без буквы d вначале названия, при этом они позволяют вычислять те же самые параметры, но с условием по какомулибо полю таблицы, при этом условия должны записываться в отдельных ячейках. Так, функция dcount -считает число элементов, dmax — максимальный элемент, dmin — минимальный, daverage — среднее значение.

Типичная форма записи таких функций такая: d****(диапазон ячеек базы данных;имя поля по которому производится расчет;диапазон ячеек с условиями); звездочками обозначено имя функции, которая вычисляет требуемые значения.

	A	B
1	Color	Weight
2	red	2
3	white	3
4	red	4
5	white	1
6	green	3
7	red	4
8
9	Color	Weight
10	= «red»

Например для расчета среднего веса красных деталей запишем функцию =DAVERAGE(A1:B7; «Weight»; A9:B10)

Можно ставить условие в виде значение,

На листе 1 создать удобочитаемую таблицу (таблицы) в соответствием заданием и вашим вариантом. При создании таблицы руководствоваться тем, что столбцов должно быть не менее 7, таблица должна быть красиво оформлена и возможен перерасчет при изменении какихлибо параметров. В качестве общего задания необходимо описать стоимость типовых товаров, их виды, имеющиеся на складе, проданные, провести расчет общей стоимости проданных товаров за какой то период времени, перерасчет стоимости товаров в евро, графики продаж за каждый год в штуках. Разместить в таблице не менее 30 товаров. Рассчитать прибыль от продажи каждого вида товара и построить графики. Рассчитать среднюю цену на каждый вид товара. Оценить процентное соотношение цен однотипных товаров различных фирм друг относительно друга (хотя бы трех фирм). Рассчитать процентное соотношение стоимости проданного товара за год от нереализованного. Учесть что на проданный товар делается процентная надбавка стоимости, на некоторые товары установлена скидка (скидки и надбавки указать в процентах, добавить столбцы с указанием стоимости продажи). Произвести расчет прибыли за какой то период времени. Для того, чтобы отметить проданный товар можно воспользоваться дополнительным столбцом, в котором будет указан данный факт с помощью выбранной вами метки. Построить круговые диаграммы продаж какой-либо марки товара, чтобы оценить долю каждого по продажам.

Вариант 1.

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

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

Отобразить табличные данные в виде диаграмм. Отразить график роста цены в зависимости от числа комнат.

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

Вариант 2.

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

Вариант 3.

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

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

Вариант 4.

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

Вариант 5.

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

Вариант 6.

Фирма занимается продажей бытовой техники. Утюги, Вентиляторы, Кондиционеры, и

т. д. Указать несколько фирм производителей.

Вариант 7.

Фирма занимается продажей продуктов питания. Указать производителей на различные виды продуктов. (Молоко, Хлеб, Масло, и т.д.)

Вариант 8.

Фирма занимается продажей бытовой химии. Несколько фирм производителей и различные виды бытовой химии (Ацетон, Стеклоотчистители и т.д.)

Вариант 9.

Фирма занимается реализацией напитков. Указать несколько производителей (Соки, Вина, Различные виды соков и вин).

Вариант 10.

Фирма занимается реализацией спортивных товаров. Указать фирмы производители и виды товаров.

ИСПОЛЬЗОВАНИЕ CALC КАК БАЗЫ ДАННЫХ

Фильтрация данных

Дана таблица с шапкой как в примере представленном на рисунке 2, необходимо дополнить ее до 15-20 записей:

Рисунок 2 — Таблица с результатами экзамена

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

Рисунок 3 — Добавление столбца с нумерацией

В одной из ячеек на рисунке 2 осуществлен перенос внутри ячейки, это осуществляется с помощью вызова меню Формат-Ячейки, в результате появляется следующее окно (рисунок 3), необходимо выбрать вкладку выравнивание и установить флажок переносов.

Рисунок 4 — Форматирование ячейки

Используя меню «Данные Фильтр-Автофильтр» вывести данные по студентам оценка, которых выше 4. Выбрать студентов оценка которых выше 2 и меньше 5. Для этого необходимо выделить всю таблицу и выбрать «Данные Фильтр-Автофильтр».

Рисунок 5 — Автофильтр

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

Рисунок 6 — Условие на поле таблицы

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

Используя «данные – фильтр — расширенный фильтр» сформировать таблицу, где имена студентов Иван или Петр, а оценка выше 3. Ниже приведен пример, где задаются условия для расширенного фильтра. При этом должны быть указаны имена столбцов, для которых проводится фильтрация (полное совпадение имени и формата названия), а также условия, условия расположенные по строкам определяют операцию «И», условия по столбцам дают условие «Или. При применении сравнения со строковыми константами необходимо помнить, что они помещаются в кавычки – “строка”. То есть условия задаются в ячейках Сalc, необходимо в ячейках указать нужные нам имена полей, причем поля должны совпадать с названиями полей в таблице для которой мы проводим фильтрацию, а ниже в ячейке указывается условие, больше , меньше =, меньше или равно

Рисунок 7 — Использование расширенного фильтра

Пример:
Имя	оценка	оценка
Иван		3
Петр

Создать еще одну таблицу на основе предыдущей, где фамилия, имя, отчество стоит в одном столбце, для этого использовать функцию CONCATENATE(СЦЕПИТЬ) (текст1;текст2;…). Текст1, текст2, … — это от 1 до 30 элементов текста, объединяемых в один элемент текста.

Синтаксис:

CONCATENATE(«Текст1»; …; «Текст30»)

Текст 1; текст 2; …: до 30 текстовых элементов, которые требуется объединить в одну строку.

Пример:

=CONCATENATE(«Доброе «;»утро «;»миссис «;»Доу») возвращает значение «Доброе утро, миссис Доу».

Элементами текста могут быть текстовые строки, числа или ссылки, которые ссылаются на одну ячейку. Строковая константа записывается с использованием кавычек («строка»). Шапка будет состоять из столбцов в порядке – номер студента, ФИО, группа, оценка.

Необходимо подсчитать средний балл для студентов. Отсортировать по группам, в группах по ФИО.

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

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

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

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

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

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

Таблица 1 — Рабочая таблица о продажах телефонов

Выделяем всю таблицу. Вызываем Данные+Сводная+Cоздать таблица. В появившемся окне мастера выбираем текущее выделение и нажимаем Ok.

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

Рисунок 8- Установка параметров сводной таблицы

В результате на новом листе будет получена таблица со следующими данными.

Таблица 2 — Сводная таблица
Сумма по полю Итого	Марка
Магазин	Нокия	Самсунг	Общий итог
1	4884	300	5184
2	726	6060	6786
3		854	854
Общий итог	5610	7214	12824

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

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

Итоговые поля и группировка

Просматривать и создавать Итоговые поля и проводить группировку по какому-то полю можно, используя Данные+Промежуточные Итоги. Необходимо выделить исходную таблицу и затем выбрать Данные+Промежуточные Итоги и в появившемся окне можно выбрать операцию группировки (например, Сумма), а также поля, по которым необходимо получить итоговые значения (рисунок 28).

Рисунок 9 — Группировка и итоги

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

Таблица 3 — Результирующая таблица
			Продажа
Дата	Мага зин	Марка	Серия	штуки)	рубли	Итого
12.12.02	1	Самсунг	с300	3	100	300
12.12.02	1	Нокия	с200	4	1221	4884
12.12.02	2	Самсунг	с300	5	1212	6060
12.12.02	2	Нокия	с200	6	121	726
12.12.02	3	Самсунг	с300	7	122	854
12.12.02 Сумма						12824
Общий итог						12824

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

Таблица 4 — Таблица с итогами по сериям

	Продажа

Дата	Магазин	Марка	Серия	(штуки)	Цена(рубли)	Итого
12.12.02	1	Нокия	с200	4	1221	4884
12.12.02	2	Нокия	с200	6	121	726
			с200 Результат	10	1342
12.12.02	1	Самсунг	с300	3	100	300
12.12.02	2	Самсунг	с300	5	1212	6060
12.12.02	3	Самсунг	с300	7	122	854
			с300 Результат	15	1434
			Общий итог	25	2776

Также можно убирать из таблицы итоговые или промежуточные поля, в примере ниже представлены только итоговые результаты группировки. Для этого необходимо щелкнуть на кнопках 1, 2 или 3, а также +, -, которые находятся слева.

Рисунок 10 — Пример итоговой таблицы без исходных данных

Кроме того, можно группировать сначала по одному полю, затем по другому. Вот пример группировки сначала по марке, затем по серии, одну из серий «самсунга» мы сделали с200.

39 40 41 42 43 44 45 46 47 48 49 Следующая 50 51 52 53 54 5

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

Самостоятельно создайте документ используя средства автоматизации верстки.

Вопросы к подготовке:

1.Общие сведения об электронной таблице calc пакета libreoffice.

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

1.2.Построение диаграмм

2.Использование calc как базы данных

2.1. Фильтрация данных

2.2. Сводные таблицы.

2.3. Итоговые поля и группировка

Источник

Notice: fwrite(): write of 334 bytes failed with errno=28 No space left on device in /var/www/html/!php-gen-lang/v1-core/function_main.php on line 385