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

Базовые функции

С количественными данными удобно работать в табличных процессорах вроде Microsoft Excel (iWork Numbers, OpenOffice Calc, LibreOffice Calc). Возьмем, например, данные о школьном и университетском образовании, медицинской страховке и пр. в Англии в 1920-30 гг. с сайта Quantitative Skills for Historians. Кстати, там же есть отличный краткий гид по работе с количественными данными в исторических науках!

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

Начнем с простейших встроенных функций: попробуем посчитать сумму мужчин и женщин, которым полагалась медицинская страховка в 1914 году. Для этого нужно поставить курсор в ячейку E7 инайти справа на панели инструментов вкладки "Главная" кнопку "(Авто)сумма" и нажать ее.

Мы увидим в выбранной ячейки формулу: это значит, что ее значение будет равняться сумме значений ячеек от B7 до D7. Но все ли здесь в порядке? Функция "Сумма" складывает значения всех предыдущих ячеек в строке или столбце (если они не пустые), а у нас в ячейке B7 не количество людей, а год -- совершенно другой тип данных. Значит, нужно вручную исправить формулу и вместо B7 написать C7.

Исправив формулу, нажимаем Enter и смотрим на результат.

Допустим, мы решили точно так же посчитать сумму за каждый год в таблице: для этого не нужно каждый раз заново писать формулу суммы -- достаточно просто выделить первую ячейку и, потянув за правый нижний уголок, выделить все ячейки, к которым нужно применить подобную формулу. При этом Excel поймет, что во второй строке нам нужна сумма не C7 и D7, а C8 и D8 -- то есть, нужные адреса ячеек подставятся в формулу сами. Цветной уголок слева обычно означает, что в данной ячейке есть комментарий (в данном случае Excel недоумевает, почему я не хочу учитывать столбец с годами, ведь это тоже числовые данные).

Лирическое отступление про формат данных

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

Теперь допустим, что мы хотим посчитать сумму людей за все годы и отдельно за периоды с 1914 по 1923, с 1924 по 1933 и с 1934 по 1938 и отобразить все это в таблице. Сначала посчитаем итоги по десятилетиям:

1. Выделим строку 17 и вставим пустую строку (Ctrl Shift +) со сдвигом ячеек вниз. Теперь 17 строка пустая, а данные, которые были в ней раньше, сдвинулись на строку вниз.
2. Поставим курсор в ячейку С17 и применим автосумму. Формула будет такая: =СУММ(C7:C16)³
3. Применим аналогичные формулы к ячейкам D17 и E17, потянув ячейку C17 за уголок.
4. Заголовок 1914-1923 в ячейке В17 для удобства можно выделить цветом.

Получится примерно вот так:

То же самое нужно проделать для 1924-1933 гг и 1934-1938 гг.

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

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

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

Теперь нам нужно посчитать общую сумму -- напишем для этого формулу вручную. Можно складывать конкретные ячейки с помощью +, а не только диапазон. Напишем в ячейке C35 формулу =СУММ(C17+C28+C34) и применим подобную формулу к двум соседним ячейкам, D35 и E35. Получиться должно вот так.

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

1. В ячейке F17 написать формулу деления =C17/E17, а затем применить ее к ячейкам F28 и F34.
2. Получившийся результат -- десятичная дробь с одним знаком после запятой, но нам нужны проценты. Чтобы получить их, достаточно просто изменить формат ячейки. Мы увидим, что на самом деле результаты немного различаются, а то, что на предыдущем шаге во всех ячейках было 0,7, объясняется округлением.

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

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

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

Давайте по втоой половине таблицы посчитаем среднее за 1914-1923, максимум за 1924-1933 и минимум за 1934-1938.

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

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

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

Перейдем на лист Secondary schools и выведем в отдельной колонке F различные результаты, если число новых учеников меньше или больше 21%: в первом случае в ячейке должен появиться результат деления, во втором -- текст "Сколько новых учеников!".

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

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

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

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

Наконец, отсортируем таблицу не по годам (столбец B), а по количеству фулл-тайм студентов (столбец C): "Главная > Сортировка и фильтр > Настраваемая сортировка". Там нужно выбрать столбец, по которому мы будем сортировать таблицу, и направление сортировки (по убыванию или по возрастанию). Не забудьте выделить всю таблицу перед тем как сортировать ее!

Теперь таблица выглядит вот так:

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

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

Вернемся к сортировке по годам. Для этого можно отменить последнее действие (Ctrl+Z) или просто заново применить нужный тип сортировки к таблице. Убедимся, что тип даных в столбце с годами -- текстовый. Осталось выделить нужные ячейки с помощью зажатой клавиши Ctrl и мыши и выбрать на вкладке "Вставка > Диаграммы" подходящий тип (например, гистограмму).

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

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

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