полезные статьи

Как освоить Microsoft Office Excel

Электронные таблицы - мечта ботаника, основанная на данных. Однако для большинства обычных людей это сложный беспорядок. К счастью, они не должны быть. Вот как согнуть данные по вашему желанию с помощью Microsoft Excel 2016.

Быстрое начало работы с Excel

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

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

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

Как выполнять самые распространенные и важные задачи в Microsoft Excel

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

Как применить условное форматирование

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

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

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

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

  1. Перейдите на вкладку «Главная».
  2. В разделе «Редактирование» нажмите кнопку «Очистить».
  3. В раскрывающемся меню выберите «Очистить форматы».

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

Замораживание строк и столбцов для упрощения просмотра

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

На вкладке «Вид» в разделе «Окно» вы увидите кнопку «Стоп-панели». Если вы нажмете здесь выпадающее меню, вы найдете три варианта:

  • Стоп-панели: эта опция блокирует все строки над курсором и любые столбцы слева от курсора. Так, например, если вы хотите, чтобы первая строка и первый столбец вашей таблицы были видны, поместите курсор в ячейку B2 и нажмите эту кнопку.
  • Freeze Top Row: эта опция замораживает только первую верхнюю строку таблицы.
  • Заморозить первый столбец: этот параметр замораживает крайнюю левую ячейку в электронной таблице.

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

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

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

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

=F29+F31

Это сложило бы два числа вместе - 373 + 414 долларов - чтобы получить общую общую сумму 787 миллионов долларов (каждая ячейка представляет миллионы).

В Excel также есть множество готовых функций для более сложной математики. Например, скажем, вы хотите узнать, сколько денег в среднем заработали фильмы X-Men в глобальном масштабе в приведенной выше таблице. Для этого вы можете использовать функцию AVERAGE. Чтобы взять среднее значение всех этих фильмов, вы должны использовать следующую строку:

=AVERAGE(F26:F33)

Двоеточие в этой строке указывает, что функция AVERAGE должна включать весь диапазон ячеек между первой и последней ячейкой. Таким образом, эта функция будет принимать среднее значение каждого значения для фильма X-Men в столбце F.

Вы также можете вкладывать функции друг в друга. Например, скажем, я хочу узнать средний рейтинг пользователя Rotten Tomatoes для каждого фильма X-Men. Средняя оценка всех ячеек с H26 по H33 на скриншоте выше заканчивается тремя десятичными знаками. Вместо этого я хочу округлить это число до одного десятичного знака. Согласно документации Excel, функция ROUND должна быть отформатирована следующим образом:

=ROUND(number, num_digits)

Здесь «число» указывает число, которое я хочу округлить, а «num_digits» представляет количество цифр, до которого я хочу округлить число. В этом случае число, которое я хочу округлить, является результатом функции AVERAGE. Итак, чтобы убрать лишние десятичные точки из среднего значения, я буду использовать следующую функцию:

=ROUND( (AVERAGE(H26:H33)), 1)

Это то, что называется вложенной функцией. Во-первых, Excel вычисляет среднее количество ячеек с H26 по H33 (выделено жирным шрифтом выше). Затем он использует это среднее значение в качестве аргумента в функции ROUND. 1 в конце строки указывает, что число должно быть округлено до одной десятичной точки. Таким образом, вместо получения среднего рейтинга 78, 875, который является фактическим средним рейтингом, вы видите 78, 9 на последнем листе.

Как видите, формулы и функции могут варьироваться от очень простых до невероятно сложных. Эти примеры едва касаются поверхности того, что вы можете делать с формулами и функциями. Вы можете проверить это руководство в How-To Geek для более глубокого погружения в то, что вы можете делать с функциями. Вы также можете просмотреть встроенные в Excel функции и узнать, как их использовать, здесь.

Используйте сводные таблицы, чтобы сделать значимые выводы

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

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

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

По умолчанию в таблице будет показана сумма каждой строки. Хотя это было бы удобно для чего-то вроде общей кассовой выручки, в этом случае сумма результатов обзора бесполезна. Мне нужно настроить это. Для этого я щелкну стрелку раскрывающегося списка рядом с «Сумма метакритики» в поле «Значения». Затем я нажму Настройки поля значения. Это даст мне новые возможности отображения данных.

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

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

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

Моя новая сводная таблица намного удобнее для чтения. Вы можете использовать сводные таблицы для проверки ваших данных различными способами без изменения листа, на котором хранятся ваши данные. Например, я могу быстро добавить столбцы для данных кассовых сборов или рейтингов Rotten Tomatoes. Я также мог бы перетащить поле «Фильм» из поля «Строки» в поле «Фильтры», чтобы в моей таблице отображались только итоги для каждого столбца, а не отдельные строки.

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

Вставьте разрывы страниц, чтобы упростить печать электронных таблиц

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

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

Если вы хотите добавить больше разрывов страниц, выполните следующие действия:

  1. Поместите курсор на строку или столбец, где вы хотите создать новый разрыв страницы.
  2. Перейдите на вкладку «Макет страницы» в интерфейсе ленты.
  3. Нажмите кнопку Breaks.
  4. В раскрывающемся раскрывающемся списке нажмите «Вставить разрыв страницы».

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

Создайте диаграммы для визуализации ваших данных

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

Чтобы создать диаграмму, сначала выберите ячейки, которые вы хотите включить. В некоторых случаях вам может понадобиться отформатировать ячейки определенным образом, чтобы Excel мог их понять, но вы можете прочитать о том, как отформатировать ячейки для каждого типа диаграммы, здесь. В этом примере мы будем использовать данные фильма для кинематографической вселенной Marvel на снимке экрана выше. Я выберу все в столбцах A, E и F.

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

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

Первая проблема проста. Вы можете щелкнуть заголовок диаграммы, чтобы изменить его. Чтобы изменить метки оси, вам нужно сначала добавить одну. Нажмите где-нибудь в пустом месте на графике, чтобы выбрать сам график. Три кнопки появятся рядом с верхним правым углом графика. Нажмите первую кнопку с зеленым плюсом. Здесь вы можете добавить такие элементы, как заголовок оси. Затем вы можете щелкнуть ярлык, чтобы редактировать его так же, как вы редактировали заголовок.

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

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

Лучшие новые возможности Excel 2016

Основы электронных таблиц так же фундаментальны, как и сама математика. Несмотря на это, Microsoft удалось добавить новые полезные функции в Excel 2016. Вот некоторые из лучших инструментов в последней версии:

  • Вы можете искать на ленте. Как и в других новых приложениях Microsoft Office, вы можете искать на ленте, если не можете найти нужную функцию. Нажмите на зеленое поле в верхней части экрана с надписью «Скажите, что вы хотите сделать». Введите ключевые слова или опишите, что вы хотите сделать, и Excel предложит вам функции.
  • Сотрудничайте с другими в режиме реального времени. Еще одна функция, общая для всех приложений Office, позволяет обмениваться документами с учетной записью OneDrive и одновременно работать с другими пользователями над одним и тем же документом. Как и в Документах Google, каждый может видеть изменения друг друга, когда они происходят.
  • Исследование в приложении с помощью Smart Lookup: если вам нужно быстро найти некоторую информацию о ячейке в документе, вы можете использовать инструмент Smart Lookup, чтобы найти его, не выходя из приложения. Вы можете получить ссылки на Википедию, определения слов и результаты поиска изображений Bing в Excel.
  • Нарисуйте свои формулы с помощью чернильных уравнений. Некоторые математические уравнения легче написать, чем набирать. В Excel 2016 вы можете нарисовать математические уравнения, чтобы добавить их в электронную таблицу. Это особенно удобно, если на вашем компьютере есть стилус или сенсорный экран.

Есть намного больше похороненных старых и новых особенностей, похороненных под поверхностью. Вы можете узнать больше о новейших версиях на веб-сайте Microsoft.

Работайте быстрее в Excel с помощью этих сочетаний клавиш

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

  • Ctrl + N / Ctrl + O / Ctrl + S: создание, открытие и сохранение документа.
  • Ctrl + X / Ctrl + C / Ctrl + V: вырезать, скопировать, вставить
  • Ctrl + B / Ctrl + I: полужирный, курсив
  • Ctrl + Page Up / Page Down: следующий / предыдущий лист
  • Ctrl + F1: развернуть или свернуть ленту
  • Alt + H: перейти на вкладку «Главная» на ленте.
  • Ctrl + стрелки: переместить к краю сечения
  • Shift + Пробел: выбрать всю строку
  • Ctrl + Пробел: выбрать весь столбец
  • Ctrl + ;: Введите текущую дату.
  • Ctrl + Alt + ;: введите текущее время.

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

Дополнительное чтение для опытных пользователей

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

  • Четыре навыка, которые превратят вас в таблицу ниндзя : Овладение таблицами - это искусство. Наш гид расскажет вам о формах ввода, статистических вычислениях, сводных таблицах и макросах.
  • Узнайте, как освоить VLOOKUP, чтобы легко находить данные : одна из самых полезных функций в Excel называется VLOOKUP. Это позволяет вам искать одну часть данных в вашей электронной таблице на основе другой части данных, которую вы уже знаете. Например, если у вас есть документ, в котором указаны название, цена и номер продукта, вы можете создать формулу, которая определяет цену при вводе имени или номера продукта.
  • Загрузите бесплатные шаблоны Excel, чтобы управлять временем, деньгами или производительностью : нет необходимости изобретать велосипед. Мало того, что в Excel имеется огромный встроенный репозиторий шаблонов электронных таблиц, вы также можете найти гораздо больше онлайн в соответствии с вашими потребностями. Найдите тот, который вам нравится, прежде чем пытаться создать свой собственный с нуля.
  • Формулы Excel: зачем вам формулы и функции? : Этот сборник руководств How-To Geek охватывает уроки среднего и продвинутого уровня о том, как использовать функции Excel и создавать собственные формулы.

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

открыто