Офисные технологии

Работа с таблицами: Microsoft Excel

С количественными данными удобно работать в табличных процессорах, например:

  • Microsoft Excel
  • iWork Numbers
  • OpenOffice Calc
  • LibreOffice Calc
  • Google Spreadsheets and etc.

Для чего их можно использовать:

  • формат таблиц
  • пристейшие вычисления
  • анализ данных
  • визуализация данных

Базовые манипуляции

Возьмем, например, данные о численности работников и заработной плате в организациях культуры за 2008-2015 года с сайта Главного информационно-вычислительного центра Министерства культуры Российской Федерации (ГИВЦ).

Откроем первую страницу скачанной по ссылке выше книги Excel. Данные располагаются в ячейках, и у каждой ячейки есть уникальный адрес, где буква обозначает столбец, а цифра -- строку. Чтобы выделить несколько ячеек подряд, можно либо навести на начальную ячейку и с зажатой левой клавишей тянуть до конечной, либо выбрать начальную ячейку, зажать Shift и выбрать конечную. Если нужно выделить несколько ячеек, которые не идут в ряд, то выбираем первую ячейку, зажимаем Ctrl, выбираем следующую и т.д. Кроме того, можно выбрать строку или столбец целиком, для этого нужно нажать на обозначение (цифру или букву, соответственно). Для строк и столбцов функции Shift и Ctrl тоже действуют!

Формат данных

Каждая ячейка в таблице имеет свой формат. Например, если он числовой, над ее содержимым можно производить математические операции, а если текстовый -- нет; если же в ячейке записана формула, Excel понимает, что нужно показать не ее текст, а результат в числовом формате. По умолчанию всем ячейкам присваивается т.н. "общий" формат и Excel сам определяет, какой в ней тип данных. В нашем примере Excel посчитал, что годы -- это числовые данные, но на самом деле это не так. Из доступных в Excel типов для этого столбца больше всего подходит "текстовый", и это можно указать вручную на панели инстументов вкладки "Главная". Подробнее про форматирование ячеек можно почитать на странице поддержки MS Office.

Представление данных

На первом листе представлены данные о численности работников в 2015 по субъектам РФ.

Таблица большая и без заголовков непонятно, какая цифра что значит. Давайте зафиксируем заголовки: во вкладке «Вид» выбираем Закрепить области → Закрепить верхнюю строку.

Фильтры

Допустим, мы хотим видеть только те области, в которых есть люди, которые работают в сфере кино; для это настроим фильтр. Выделяем интересующий нас столбец, во вкладке "Главная" выбираем Сортировка и фильтр, далее Фильтр. В выбранном столбце появилась стрелка с выпадающим меню, снимем галку со значения 0,0 и получим список областей, где число работников, занятых в сфере кино, не равно нулю. Если мы хотим настроить фильтр более дробно, то можно воспользоваться функциями Числовые фильтры. Чтобы удалить фильтр, опять выделяем столбец, нажимаем Сортировка и фильтр, далее Фильтр.

Сортировка

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

Формулы

Начнем с простейших встроенных функций: попробуем посчитать сумму работников по округам.

Начнём с работников театров (столбец C). Если мы выделим необходимые ячейки и нажмем символ суммы, то внизу выделенного фрагмента появится число. Другой способ: выбираем нужную ячейку, нажимаем символ суммы, в ячейке появляется формула. Теперь нам нужно её заполнить: выделяем нужный нам фрагмент (получается =СУММ(C3:C20)) и нажимаем Enter.

Допустим, мы решили точно так же посчитать сумму в каждой категории учреждений культуры: для этого не нужно каждый раз заново писать формулу суммы -- достаточно просто выделить первую ячейку и, потянув за правый нижний уголок, выделить все ячейки, к которым нужно применить подобную формулу. Посмотрим на формулу в колонке D: =СУММ(D3:D20). Так получается потому, что ссылки на ячейки в формулах референциальные, т.е. эксель запоминает место, где находится выбранная ячейка относительно ячейки, где находится формула. Проделаем это для всех федеральных округов (важно! здесь просто копирование формулы не сработает: длина интервала отличается!). Затем посчитаем сумму для всех федеральных округов РФ.

Теперь можно сгруппировать строки, чтобы была возможность свернуть данные и увидеть только подытоги по федеральным округам. Для этого нужно выделить ячейки, в которых содержатся субъекты, принадлежащие к одному федеральному округу, и выбрать команду "Группировать" в меню "Структура" на вкладке "Данные" (в Office 365 "Структура > Группировать"). Обратите внимание, что строку подытогов мы не выделяем!

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

Если мы свернем сгруппированные данные по округам, нажав на минус, останутся только строки с подытогами.

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

Напишем в ячейке C93 формулу =СУММ(C92+C82+C69+C62+C47+C39+C32+C20) и применим подобную формулу к соседним ячейкам.

Откроем следующий лист рабочей книги. На нем представлена динамика численности работников и заработной платы с 2008 по 2015 год.

Посчитаем долю показателей 2015 года от показателей 2014 гола в процентах.

Чтобы заполнить ее, нужно

  1. В ячейке J2 написать формулу деления =I2/H2, а затем применить ее к остальным ячейкам столбца J.

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

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

Вернемся к стандартным функциям. Кроме суммы на панели инструментов вкладки "Главная" можно выбрать какую-нибудь другую функцию -- например, среднее значение (СРЗНАЧ), минимум (МИН) или максимум (МАКС) и др. Если вы считаете значение функций по диапазону ячеек, между ними будет двоеточие, а если нет -- точка с запятой.

  • =СРЗНАЧ(G7:G16) -- среднее значение всех ячеек от G7 до G16
  • =СРЗНАЧ(G7;G16) -- среднее значение ячеек G7 и G16

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

Еще одна стандартная функция -- условие ЕСЛИ. В качестве аргументов в скобках через ; пишутся

  • логическое условие
  • то, что нужно вывести, если оно истинно
  • то, что нужно вывести, если оно ложно

Функцию если можно найти на главной там же, где сумм и среднее значение, а можно добавить через "Формулы > Вставить функцию".

Вставим столбец слева от столбца с процентами, в ячейку J2 вставим условие: если значение за 2014 год больше значения за 2015, то пишем разницу в процентах от 2014 года, если нет, то текстовое значение "2015".

Условное форматирование и сортировка

Условное форматирование таблицы помогает лучше ориентироваться в большом количестве данных, изменяя вид ячеек в соответствии с определенными условиями. Попробуем в строках раскрасить в зелёный цвет ячейки со значением меньше 67 000. Для этого выбираем "Главная > Условное форматирование > Правила выделения ячеек > Меньше", пишем условие и выбираем тип форматирования.


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

Графики и диаграммы

Табличные данные очень удобно превращать в диаграммы, которые потом можно прямо из Excel скопировать в Word или PowerPoint. Графики -- это очень красиво, но не забывайте о том, что они должны быть информативными!

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

Для этого выделим интервал A10:I11, во вкладке "Вставить" выберем График.

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

Во всплывающем меню выбираем Подписи горизонтальной оси → Изменить

Прямо в таблице выбираем диапазон B1:I1 → OK → OK.

Также можно изменить цвет линий, легенды, осей, название и т.д.

Поиск и организация данных

VLOOKUP/ВПР
ВПР — это аббревиатура от вертикального просмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку).

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена и откройте вкладку Формулы - Вставка функции (Formulas - Insert Function). В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

  1. Искомое значение (Lookup Value) - то значение, которое функция должна найти в крайнем левом столбцезаданного интервала
  2. Таблица (Table Array) - таблица из которой берутся искомые значения
  3. Номер_столбца (Column index number) - порядковый номер (не буква!) столбца в диапазоне, из которого будет возвращено значение;
  4. Интервальный_просмотр (Range Lookup) - в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:

Если введено значение 0 или ЛОЖЬ (FALSE), то фактически это означает, что разрешен поиск только точного соответствия.

Если введено значение 1 или ИСТИНА (TRUE), то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

INDEX+MATCH

INDEX (массив_откуда_взять, [номер_строки], [номер_столбца])

Формула берет значение, которое находится в строке номер_строки и в столбце номер_столбца в массиве массив_откуда_взять.

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

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

MATCH (что_искать, где_искать, 0)

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

где_искать – массив данных, в котором ищем (самая левая колонка в случае с VLOOKUP (ВПР))

Remove Duplicates

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

Как загрузить данные из других форматов?

Табличные данные можно сохранять не только в формате Excel (.xls и .xlsx), но и в ряде более простых форматов, которые носят общее название DSV (Delimiter Separated Values). В частности, разновидностями этого формата являются .csv (Comma Separated Values) и .tsv (Tab Separated Values). Несложно догадаться, что называются они по типу разделителя между колонками и соотносятся с .xls примерно так же, как формат .txt соотносится с .doc.

Если просто так открыть такой файл с помощью Excel, все данные склеятся и попадут в одну колонку. Текстовые файлы нужно не просто открывать, а импортировать из них данные. Это делается с помощью команды "Данные > Из текстового/CSV-файла".



Если у вас все же "слиплись" столбцы, используйте функцию Текст по столбцам из вкладки "Данные".

Полезные ссылки

Тематические видеотуториалы для Mac и Windows
Видеоуроки
Advanced Excel
Official Support
Краткий гайд по графикам в экселе
Excel Tutorial
Курс на курсере
100 формул в экселе

results matching ""

    No results matching ""