Работа с таблицами: 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
  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-файла".

results matching ""

    No results matching ""