Создать таблицу libreoffice. Табличный процессор LibreOffice Calc

Для тех, кто работает с электронными таблицами каждый день, вопрос «Как сделать таблицу в LibreOffice Calc» звучит минимум странно. Ведь любой процессор электронных таблиц, не важно MS Excel, OpenOffice, LibreOffice, Gnumeric — это и есть двухмерная таблица. Делать таблицу в нём уже поздно. В статье мы будем обсуждать придание диапазону внешнего эстетического вида таблицы. Так как вариантов выполнить одну и ту же задачу существует масса, то разговор получится длинный.

Начнём от простого к правильному. Список тем будет следующий:

«Быстрое» создание таблицы в Calc

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

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

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

Эти задачи при создании таблиц можно решить при помощи боковой панели. Если панель отключена, вернуть её можно в меню Вид → Боковая панель.

Я не буду подробно её описывать, так как из дальнейшего текста станет многое понятно и так. Замечу лишь, что квадратик с минусом у заголовка позволяет свернуть раздел, а значок окошка с противоположной стороны от заголовка вызывает окно формат ячейки. Иконка A↔B активизируется при входе в ячейку и позволяет задавать расстояние между символами. Боковая панель очень удобная вещь и привыкнув вы найдёте много плюсов в ней. Но всё-таки при помощи панелей нельзя задать следующие вещи:

• Во-первых, этим способом не задать проверку орфографии для отдельной ячейки (в LibreOffice Calc это тоже стиль ячейки);
• Во-вторых, не настроить тонко формат числа, можно использовать только стандартные форматы;
• В-третьих, не выбрать стили надчеркивания и зачеркивая;
• В-четвёртых, при помощи панели не удастся защитить или отменить защиту выбранных ячеек (и это стиль);
• И в конце концов вам не удастся сделать тремя кликами следующую таблицу.

Именно об этом всём и об оптимизации создания таблицы в LibreOffice Calc и пойдёт речь ниже. Путь который я предлагаю для создания таблиц в Calc, на первый взгляд кажется не быстрым и может даже несколько запутанным. Но если вы его освоите и адаптируете к своим потребностям, то он окупится с лихвой.

Обрамление ячеек и диапазонов при создании таблицы

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

• Убрать обрамление — убирает всё обрамление с ячейки, то есть его можно использовать для очистки обрамления ячейки;
• Обрамление со всех сторон — ячейка в этом случае будет окружена линией со всех сторон;
• Обрамление слева и справа — обрамление ячеек будет применено только к левой и правой границе;
• Обрамление сверху и снизу - обрамление ячеек будет применено только к верхней и нижней границе;
• Установить только диагональные линии — нужна если вам необходимо явно указать что эти ячейка в таблице не участвует, но формат не позволяет её объединить с другой ячейкой, также этот способ используется при симуляции диагонально разделённой ячейки.

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

Если вы поняли как работает обрамление одной ячейки, думаю, что сложностей со многими у вас не возникнет. Хочу лишь обратить внимание на пятые иконки. Они позволяют применить обрамление по краям выделенного диапазона не меняя обрамление ячеек внутри него. Это очень удобно, когда мы уже отредактировали обрамление у многих ячеек и не хотим его сбить. Подраздел «Особое» позволяет нам настроить обрамление ячейки особым образом, если нам недостаточно настроек по умолчанию. Пользоваться им приходится не редко, поэтому я расскажу о нём подробно. На скриншоте выше показано 3 возможных значения линии в этом поле. По диагонали белые полосы — это значит, что этих линий не будет. То есть если у вас они уже есть, то они пропадут. Верхняя и правая граница отмечены черными линиями, теми которые мы хотим сделать или поменять. Левая и нижняя граница имеют серые линии — это говорит о том, что эти линии останутся без изменений. Хочу особо отметить последний вариант, он очень важен при создании обрамления таблицы если мы не хотим изменять стиль каких-то линий. При помощи этого поля мы явно указываем у какой конкретной линии мы хотим поменять стиль, а какую оставить без изменения. Для того чтобы можно было выбрать несколько линий сразу, можно нажать на их пересечение, при этом нажатие в угол поля не будет выделять диагональную линию. Чтобы выделить диагональные линии нужно указывать их по отдельности. На мой взгляд очень удобно, с одной стороны мы можем выделить несколько линий сразу, с другой, диагональные линии нам нужны при создании таблицы редко. Стили линии задаются в подразделе «Эффекты» раздела «Линия». Как видно из скриншота доступно семь видов линий. Восьмая линия с названием «нет» убирает любой стиль выделенной линии. Ниже располагается поле толщины линии, а под ним выбор цвета линии. Думаю, что это интуитивно понятные вещи, поэтому останавливаться на них не буду. Раздел отступы от содержимого позволяет нам настроить пространство между краем ячейки и её содержимым. Нужно учитывать, что это отступ будет работать только при «оптимальной» ширине и высоте столбца и объекте, выровненном вертикально и горизонтально по середине. При отсутствии этих условий вы можете получать весьма странные результаты, иногда вовсе непонятные, иногда даже удивительные. Для того чтобы сделать таблицу в LibreOffice Calc с трехмерным эффектом, мы можем воспользоваться иконками в разделе «Стиль тени». На этих иконках показано, куда будет падать тень от ячейки. Если тень падает на ячейку с таким же стилем, то она отображаться на соседней ячейке не будет. Поэтому можно выделить всю таблицу и к ней применить это свойство, тень появится только у крайних ячеек с той стороны, где она должна падать.

Заливка ячеек в таблице

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

Шрифт ячейки в таблице

На вкладке «Шрифт» окна «Формат ячеек» LibreOffice Calc можно задать сам шрифт, его начертание и размер. Результат будет виден в нижнем поле.

Особо хочу подчеркнуть поле «Язык». При помощи этого поля мы можем выбрать язык ячейки для проверки орфографии. Например, весь ваш документ по умолчанию имеет проверку орфографии на русском языке. Но в нашей создаваемой таблице есть ячейки с английскими словами. Задайте в этом поле «Английский (США)» для этих ячеек. Это позволит избежать лишней работы по проверке и будет показывать только неправильно введённые слова. Маленькое примечание: К сожалению, сейчас это поле не всегда корректно работает, что не до конца снимает трудности проверки орфографии в документе со многими языками. Надеюсь, это в скором времени поправят.

Эффекты шрифта

Для придания особого вида шрифту при создании таблиц, таких как подчеркивание, цвет, рельеф, можно использовать вкладку «Эффекты Шрифта» окна «Формат ячеек» LibreOffice Calc. Раздел «Параметры» этой вкладки содержит «Цвет шрифта» для задания цвета, «Рельеф», «Контур» и «Тень». Если вы не помните, то все цвета с названием «Автоматически» задаются в окне «Параметры» в разделе «Внешний вид» («Сервис» → «Параметры» → «LibreOffice» → «Внешний вид»). По умолчанию для шрифта используется черный. В выпадающем списке «Рельеф» можно указать эффекты приподнятого шрифта или вдавленного. Если был сделан выбор одного из них, то пункты «Контур» и «Тень» становятся недоступными. Сами же контур и тень можно использовать вместе. Все эти эффекты реально нужны только тогда, когда нам нужно сделать красивый отчет, и мы не хотим использовать дополнительные инструменты. «Надчеркивание» и «Подчеркивание» текста имеют по 16 способов, «Зачеркивание» имеет 5 способов. Все они имеют понятные названия, поэтому я не буду их описывать каждый отдельно. Хочу обратить ваше внимание на флажок «Только слова». Он становится доступным только при выборе какого-нибудь пункта выше (например «Зачеркивание») и позволяет исключить пробелы из эффекта. Справа от «Надчеркивание» и «Подчеркивание» находятся выпадающие списки с выбором цвета для них. Они тоже становятся доступны, только если соответствующий эффект был выбран. Поле внизу позволяет нам увидеть будущий результат изменений.

Выравнивание

Вкладка «Выравнивание» отвечает за выравнивание и направление текста в ячейке. Чаще всего нам хватает элементов на панели инструментов, но когда нам нужно сделать таблицу в LibreOffice Calc с более хитрым форматированием чем обычно, то без этой вкладки нам не обойтись. Список выравнивания текста по горизонтали имеет семь пунктов:

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

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

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

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

Числовой формат

По поводу числовых форматов в LibreOffice Calc можно написать отдельную большую статью. Официальную справку можно посмотреть на этой странице . Поэтому я коснусь этой темы коротко, и дам пару наводящих примеров. Более подробно будет позже. На вкладке «числа» слева находится поле с категориями форматов. Названия интуитивно понятны, сделаю лишь одно замечание: под словом «Дробь» подразумеваются обыкновенные дроби (то есть ½ или 12¾). В поле формат можно выбрать конкретное представление числа, входящего в выбранную категорию. Список «Язык» позволяет задать форматирование, свойственное для региона и языка. Так например, в русском число 1 200,00 представляется именно так, то есть разделитель разрядов это пробел, а разделитель для дробной части запятая. Для английского языка в США характерна другая запись 1,200.00, то есть для разделителей разряда используется запятая, а для дробной части точка. Этот функционал нужен не часто, только если вам необходимо именно в конкретном случае использовать другое форматирование. Форматирование по умолчанию для всех документов LibreOffice или только для текущего документа выбирается в разделе Сервис → Параметры → Настройки языка → Языки → Локальные настройки. На той же вкладке можно выбрать валюту по умолчанию. Раздел «Параметры» доступен в трёх категориях: Числовой, Процентный, Денежный. И если в «Особом» у вас есть свои числовые форматы, то может быть доступен и в нём. В этом разделе в поле «Дробная часть» можно указать сколько нужно показывать знаков после запятой, при этом происходит округление. «Ведущие нули» указывает сколько необходимо показывать нулей перед запятой, при этом количество отображаемых порядков может уменьшиться только вплоть до первой цифры больше нуля, т. е. смысл числа не изменится. Однако увеличение поля «ведущие нули» сверх имеющихся порядков числа приведет к появлению дополнительных нулей слева. Флажок «Отрицательные красным» имеет смысл ставить, если вам нужно выделить отрицательные числа красным цветом. Флажок «Разделитель разряда» ставит разделители разряда, например тысяч. Поле «Код формата» можно редактировать, если вы знаете способы указания формата, то его можно внести туда руками. В поле под списком «Язык» будет показан результат того, что вы выбрали. Небольшой пример. В таблице выше было поле в котором указано красным цветом: «Итого: -10 руб». Оно организовано так, что в поле вводится только число, остальное подставляется автоматически, так же автоматически выбирается цвет, черный для положительных и красный для отрицательных чисел. Код формата для этого поля выглядит так: [$Итого: ]# ##0,00" руб.";[$Итого: ]-# ##0,00" руб."

Защита ячейки

Одним из очень специфических стилей ячейки является защита её от изменений. Да, защищённая ячейка это стиль, как бы это удивительно не звучало. И мы можем использовать это свойство как и обычные свойства (например, форматирование, шрифт, кегль...) Для того чтобы защита ячейки начала работать, её нужно включить, сделать это можно пройдя в меню Сервис → Защита → Защитить Лист. Параметр «Скрыть всё» позволяет скрыть всю информацию, содержащуюся в выбранной ячейке. То есть, после включения защиты листа, её содержимого не будет видно, в том числе и при печати. Эта функция очень удобна для того, чтобы служебные диапазоны не мешали человеку пользоваться документом, не отвлекали его внимания. Если вам нужно скрыть информацию только при печати, например, когда вы сами используете модель и вам нужно видеть служебные диапазоны, но на печать вам выводить их не нужно, то можно воспользоваться самым нижним параметром «Скрыть при печати». Тогда вся информация при работе с документом будет видна, но при печати она не будет выводиться. «Скрыть формулу» позволяет нам скрыть только формулу в ячейке, но не скрывать её результат. Все эти вещи удобно использовать, если нам нужно передать файл для просмотра или работы другому человеку, и мы хотим, чтобы он случайно его не сломал. Например, очень полезно защищать ячейки с формулами, чтобы человек случайно не повредил расчеты. Я всегда использую защиту для рабочего документа со сложными моделями, когда она уже готова, и документ начинает использоваться только для целей, которых он создавался, чтобы самому не повредить случайно модель. Хочу заметить, что пароль при защите можно не вводить. Если поля оставить пустыми, то информацию в ячейке нельзя будет изменить, но снять защиту можно будет без пароля.

Использование стилей ячейки

«И это был быстрый способ?» - спросите вы. Нет, это был не быстрый способ, это было только начало. Но скоро мы действительно сможем создать таблицу в три клика. Сейчас мы, наверное, подошли к святому святых электронных таблиц при создании их в LibreOffice Calc. Всё что было сказано выше — это был стиль ячейки. Естественно пользоваться им в таком виде чаще всего не удобно, поэтому мы сделаем следующий шаг в сторону оптимизации нашего труда. Нажмите F11, или на панели форматирования на самую левую иконку с всплывающей подсказкой «Стили», или что часто удобнее, на боковой панели иконку «Стили и форматирование». Если вы отключили боковую панель, вернуть её можно в меню Вид → Боковая панель. Я настоятельно рекомендую обратить пристальное внимание на неё, она фактически не занимает рабочего места на экране в свёрнутом положении, но имеет колоссальный функционал и, на мой взгляд, очень удобна. Здесь мы раздерём работу со стилями ячейки. Полный рассказ о работе со стилями это очень увлекательный и достаточно долгий разговор. Когда у вас появилось окно «Стили и форматирование», нажмите правой кнопкой мыши на любой стиль, например «Базовый». Появится контекстное меню с тремя пунктами: «Создать...», «Изменить...», «Скрыть». На стилях, которые вы будете создавать сами, будет ещё четвертый пункт - «Удалить». Все эти пункты понятны и без объяснений, поэтому пойдём дальше. Нажмите правой кнопкой на стиле «Базовый» и выберите «Изменить...». Перед вами появится окно «Стиль ячейки: Базовый». При работе со стилями в заголовке окна кроме его названия присутствует и название стиля, в данном случае «Базовый». Поле «Название» и «Категории» не могут быть отредактированы в стилях, устанавливаемых по умолчанию вместе с LibreOffice. В создаваемых пользователем они редактируются. «Наследует из» - это поле говорит о том, какой стиль является родительским. Наверное, этот пункт нужно подробно объяснить. Когда мы открываем новый документ, все ячейки по умолчанию имеют стиль «Базовый». То есть, в LibreOffice Calc не может быть ячейки без стиля. Это очень важно. Стиль есть всегда, даже когда вы полностью уверены, что его нет. Стиль «Заголовок1» является потомком для стиля «Базовый». Так же как и у людей, он наследует от родителя часть информации. У нас это гены, а тут это свойства элементов стиля (например шрифт, фон ячейки, стиль обрамления и др.), но некоторые свойства у него могут быть индивидуальные. «Базовый» стиль ячейки является основным предком для всех стилей ячейки, все остальные стили будут так или иначе его потомками. Преимущество такой иерархии заключается в том что, если мы решили, например, поменять шрифт во всех наших стилях, включая «Базовый», то нам нужно поменять его только в стиле «Базовый». Все остальные стили поменяют шрифт автоматически. Исключением будут стили (и их потомки естественно), в которых вы указали другой шрифт. Также будет работать любая характеристика, включая защиту ячейки. Ещё более важно отметить, что как только вы сменили какую либо характеристику стиля, все ячейки, имеющие этот стиль, её тоже меняют во всех документах. В разделе «Параметры» коротко перечислены характеристики стиля (шрифт, цвет, фон и другие). Остальные вкладки этого окна повторяют выше рассмотренные, поэтому я не буду повторяться. Пункт «Создать...» в контекстном меню создаст наследника к тому стилю, на который мы нажали правой кнопкой мыши. Откроется знакомое нам окно, в котором название будет «Безымянный1». Как я говорил, в создаваемых стилях название можно менять. Давайте им короткие, но понятные названия, это поможет в последующем вам ориентироваться. Кроме того, продумайте пути наследования наилучшим образом подходящие для вас, это тоже сильно поможет. Делать это приходится всего один раз, а потом остается только радоваться, что это всё уже сделано. Используйте пункт «Скрыть» в контекстном меню для стилей, которые используете редко, или не будете использовать долгое время. Если в нижнем выпадающем списке окна стилей выбрать «Скрытые», вы все их сможете увидеть и при необходимости через контекстное меню вернуть. Если вы скрываете родительский стиль, все наследники тоже скроются, в списке скрытых наследники не появятся, но при восстановлении вся структура вернётся на место. Когда мы используем панели форматирования для того, чтобы сделать таблицу в LibreOffice Calc, мы фактически создаём потомка применённого стиля (если мы не пользуемся стилями, то это потомок стиля «Базовый»). При ручном форматировании стиль ячейки наследует все параметры, которые не были указаны явно. Но минус в том, что нам этот стиль ячейки в списке не видно, и поэтому мы не можем им управлять. Чтобы сбросить всё ручное форматирование можно щелкнуть на ячейку правой кнопкой мыши и в контекстном меню выбрать «Очистить форматирование». Эта операция сбросит все изменения, которые были наложены на стиль «руками», но оставит всё, что было отформатировано стилем. Чтобы сбросить стиль до «Базовый» нужно просто применить «Базовый» стиль. В окне стилей существует две кнопки, которые могут вам пригодиться. Это «Создать стиль из выделенного» и «Обновить стиль по образцу». На скриншоте окна «Стили и форматирование» эти кнопки располагаются справа и выглядят как значок параграфа с черточками с правой стороны. На первых порах они могут помочь создать дерево стилей из имеющихся документов. Как применить стиль? Выберите диапазон, к которому вы хотите применить стиль, откройте стили и выполните двойной щелчок по желаемому стилю. Всё. Правда же, это намного проще чем тыкать в кнопки, пытаясь добиться того же результата что и вчера? Стили сохраняются отдельно для каждого документа. Поэтому нам нужно использовать шаблоны, если мы хотим делать серии документов на одних и тех же стилях. Если мы хотим, чтобы стили были доступны во всех вновь создаваемых документах, то нам следует обновить шаблон по умолчанию. Если мы решим изменить стили в шаблоне, то при открытии документа, сделанного на основе этого шаблона, появится окно с предложением обновить стили. Импорта стилей в настоящее время из документов LibreOffice Calc нет. Но недостающие стили добавляются при копировании ячеек и вставке листов. Если после этого их удалить, стили всё равно останутся в документе. Обновление стилей при этом не происходит, к сожалению. Поэтому если нам нужно импортировать обновленный стиль, единственный способ это удалить прежний, а затем вставить обновленный вышеописанным способом. Другим способом может быть переименование стилей, но при большом дереве стилей он трудозатратен. У стилей есть три минуса:

• Первый, они должны быть созданы, а это потребует сил на организацию своего труда.
• Мы не можем сделать стиль таблицы, мы можем сделать только стиль заголовка, результата, поля данных. Если все эти элементы имеют разные стили, то нам нужно будет использовать минимум три стиля.
• На сегодняшний день, не реализован вменяемый механизм импорта/экспорта подобный используемому в Writer.

Автоформат

Это функция позволяет разом сделать таблицу из выделенного диапазона. Мы просто выделяем диапазон, выбираем вид таблицы, подтверждаем выбор, и вуаля. Пройдите в меню «Формат» → «Автоформат...». Откроется следующее окно. В поле «Формат» содержатся имена сохранённых шаблонов таблиц. Справа поле, в котором можно посмотреть, как это должно выглядеть в теории. То есть оно не покажет вам результат, оно просто показывает как выглядит модель формата таблицы. Раздел «Форматирование» позволяет выбрать какие параметры мы хотим из текущего шаблона автоформата примерить к будущей таблице. Например, нам всё нравится в текущем шаблоне кроме обрамления: снимаем галочку, и обрамление останется таким как было до применения автоформата. Кнопки на мой взгляд понятны: «Ok» — подтвердить выбор, «Отмена» — закрыть окно без применения автоформата, «Справка» — вызывает справку по автоформату...

• «Добавить» — это очень полезная кнопка позволяет добавить свой шаблон таблицы в автоформат. Если у вас постоянно одинаковый стиль у таблицы, оформите её полностью. Затем выделите диапазон, пройдите Формат → Автоформат... и нажмите кнопку добавить. Появится окно, в которое нужно ввести название будущего шаблона. И нажмите «Ok».
• «Удалить» — позволяет удалить ненужный больше шаблон автоформата. Хочу отметить, что можно удалить любой шаблон кроме «Базовый», так что можно капитально почистить список при необходимости.
• «Переименовать» — позволяет переименовать шаблон, тоже не работает с «Базовым», со всеми остальными работает.

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

Разбить ячейку по диагонали

Когда нужно сделать в таблице ячейку разделённую по диагонали, мы с сталкиваемся с препятствием, что нам можно только её зачеркнуть. Добиться этого можно лишь созданием визуального эффекта разделённой ячейки. Способ не новый и был описан ещё для OpenOffice.org. На картинке хорошо видно как добиться эффекта. Нужно смежные по диагонали ячейки перечеркнуть, а в другой паре диагональных ячейкек ввести нужные слова. Сложность возникает в том, что ячейки должны быть равной ширины и высоты, иначе линия будет ломанная. Проще всего этого добиться, выделив их вместе и, нажав в заголовке столбцов, выбрать «Ширина столбцов...» и в появившемся окне задать необходимую ширину. Но можно несколько упростить задачу. Хотя, чтобы создать шаблон автоформата нужна таблица 4х4, для того, чтобы применить автоформат нужна таблица 3х3. Поэтому, создайте сегмент с обрамлением верхнего левого угла, как показано на скриншоте ниже, выделите диапазон 4х4 и создайте шаблон автоформата. А когда будете применять его, выделяйте диапазон 3х3 и снимайте галочки все кроме обрамления. Последний совет, который можно дать в этой теме, вынесите кнопку «Автоформат» на панель, чтобы не нужно было за ней тянуться в главное меню: Сервис → Настройка, Вкладка «Панели инструментов». Нужно выбрать панель и нажать кнопку добавить, откроется список, в котором вы сможете выбрать кнопку для добавления.

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

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

Рис. 1 Использование таблиц в текстовых документах

В общем-то, для создания таблиц с расчетами существует другая программа из пакета LibreOffice Calk (бесплатный аналог Microsoft Office Excel). Именно эта программа позволяет создать таблицы, в которых все расчеты происходят автоматически по введенным формулам. Но и у LibreOffice Writer есть подобные инструменты, которые было бы очень хорошо научиться их использовать.

Создаем таблицу

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

• Первый способ - использование команды в главном меню Вставка → Таблицу...

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

• Второй - в том же самом меню Таблица → Вставить → Таблицу... Или же просто нажать на клавиатуре комбинацию Ctrl + F12 .

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

Рис. 3 Параметры создаваемой таблицы

Уникальный способ создания таблиц

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

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

Рис. 4 Набранный текст

Выделим набранный текст, после чего исполним команду главного меню:

Таблица → Преобразовать → Текст в таблицу .

Рис. 5 Преобразование текста в таблицу

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

Рис. 6 Параметры преобразования

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

Рис. 7 Полученная таблица

Форматируем созданную таблицу при помощи автоформата

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

Рис. 8 Использование автоформата

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

Создание своего автоформата

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

Рис. 9 Форматируем таблицу самостоятельно

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

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

Рис. 10 Сохраняем созданный вариант форматирования

Дополнительные возможности

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

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

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

Рис. 11 Использование формул в таблице

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

Рис. 12 Итоговая таблица

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

Рабочее поле и выбор параметров страницы

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

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

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

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

Инструменты

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

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

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

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

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

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

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

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

Вкладка Обрамление позволяет задать тип обрамления ячеек при выводе на печать. Дело в том, что те линии, которые придают рабочему полю LibreOffice Calc вид таблицы, на самом деле видны только на экране монитора, а при печати на принтере пропадут.

Ячейки можно объединять и разделять. Делается это с помощью пункта главного меню Формат -> Объединить ячейки . Бывает полезно, например, при занесении в ячейку достаточно длинного текста.

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

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

Вычисления в ячейках

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

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

Проси, что хочешь, сказал он.

Сесса ответил:

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

Раджа был разочарован:

Ты так мало просишь...

Заголовок и шапка таблицы заполняются обычным текстом. Для ячеек установлена категория Текст , но даже если этого не сделать, то ничего особенного не произойдет, LibreOffice Calc прощает такие недочеты. Заголовок размещен в объединенной ячейке (Формат -> Объединить ячейки ), а для шапки выбран режим Перенос по словам в окне Формат ячеек на вкладке Выравнивание .

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

Самое интересное связано с тем, как производятся вычисления.

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

На иллюстрации в ячейке С5 записано математическое выражение =B5*0,035 , которое означает, что надо взять содержимое ячейки B5 (количество зерен) и умножить его на число 0,035 (вес одного зерна в граммах). В ячейке виден результат этой операции.

Правила ввода просты.

• Все выражения начинаются со знака равенства.
• Названия ячеек, которые участвуют в выражении, записываются так, как они выглядят на экране монитора - большими латинскими буквами и цифрами.
• Основные арифметические операции обозначаются так (без кавычек):
• + сложение;
• - вычитание;
• * умножение;
• / деление;
• ^ возведение в степень;
• Имеются и другие операции, посмотреть которые можно нажав на кнопку со знаком функции Мастер функций (на иллюстрации обведена красным кружком)
• Порядок выполнения математических операций обычный - сначала возведение в степень, затем умножение и деление, затем сложение и вычитание. Порядок выполнения можно изменять с помощью скобок.

После завершения записи выражения надо нажать Enter . Если ошибок нет, то в ячейке появится результат в виде числа. Иногда вместо результата виден символ "#". Это просто означает, что число не поместилось и ячейку надо увеличить, раздвинув соответствующую колонку.

Строка для второй клетки шахматной доски производит вычисления с результатом, записанным для первой - количество зерен умножается на 2. Все остальные строки получены копированием ячеек второй строки, кроме номера клетки. Для этого надо выделить три левые ячейки второй строки, скопировать их (Ctrl+C), а затем выделить аналогичные ячейки в других строках и сделать вставку (Ctrl+V).

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

Клетка 26 принесла тонну зерна, а 34 - столько, что для этого количества потребуется товарный поезд. Граница достигнута на клетке 55. Количество зерна превысило все, что было собрано во всем мире в 2005 году, который считается одним из самых урожайных (тогда собрали 615 млн. тонн пшеницы). А ведь для получения окончательного результата все, что "находится" в каждой клетке надо бы еще сложить.

Файл с электронной таблицей для шахматной задачи можно скачать .

Сохранение

Сохранение электронной таблицы в "родном" для LibreOffice Calc формате ods обеспечивает наиболее надежный результат. Поэтому для хранения или переноса рекомендуется использовать именно его.

Для обмена документами (но не для хранения) файл может быть сохранен как xls (Microsoft Excel 97/2000/XP/2003) или xlsx (Microsoft Excel 2007/2010 XML). Файл формата xlsx все еще остается менее предпочтительным, так как он может не открыться на старых компьютерах. Оба формата можно рекомендовать только для ситуации, когда офисный пакет LibreOffice у адресата отсутствует.

Возможен экспорт электронной таблицы LibreOffice Calc в формат PDF. Этот процесс не отличается от рассмотренного ранее в разделе Подготовка текстовых документов в LibreOffice Writer . Но надо помнить, что в этом формате таблица имеет вид бумажного документа и магия вычислений в ней не работает.

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

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

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

Помимо разбиения таблицы на несколько отдельных, у этой проблемы есть ещё три решения.

Решение 1 — С использованием связанных фреймов

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

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

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

Решение 2 — С добавлением надписи в верхнюю строку таблицы

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

Чтобы это сделать, я убрал у верхней строки границы (сделал их невидимыми) и объединил ячейки.

У данного способа есть несколько минусов:

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

Решение 3 — Добавление надписи с помощью врезки

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

Не самое аккуратное решение, но в некоторых случаях вполне пригодное.