Как создать таблицу в excel: пошаговая инструкция

Что я могу делать в Excel?

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


  1. Работа с числовыми данными. Например, составление самых различных бюджетов, начиная от домашнего, как самого простого, и, заканчивая бюджетом крупной организации.
  2. Работа с текстом. Разнообразный набор инструментов для работы с текстовыми данными дает возможность представить даже самые сложные текстовые отчеты.
  3. Создание графиков и диаграмм. Большое количество инструментов позволяет создавать самые различные варианты диаграмм, что дает возможность представить ваши данные наиболее ярко и выразительно.
  4. Создание схем и рисунков. Помимо графиков и диаграмм, Excel позволяет вставлять на рабочий лист множество различных фигур и рисунки SmartArt. Эти инструменты значительно увеличивают возможности визуализации данных в программе.
  5. Организация списков и баз данных. В Microsoft Office Excel изначально была заложена структура строк и столбцов, поэтому организовать работу со списками или создать базу данных является для Excel элементарной задачей.
  6. Импорт и экспорт данных.Excel позволяет обмениваться данными с самыми различными источниками, что делает работу с программой еще более универсальной.
  7. Автоматизация однотипных задач. Использование макросов в Excel позволяет автоматизировать выполнение однотипных трудоемких задач и свести участие человека до единственного щелчка мышью для запуска макроса.
  8. Создание панелей управления. В Excel существует возможность размещать элементы управления прямо на листе, что позволяет создавать наглядные интерактивные документы.
  9. Встроенный язык программирования. Встроенный в приложение Microsoft Excel язык программирования Visual Basic for Applications (VBA) позволяет расширить возможности программы как минимум в несколько раз. Знание языка открывает для вас совершенно новые горизонты, к примеру, создание своих пользовательских функций или целых надстроек.

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

Раздел 5: Продвинутая работа с формулами в Excel

  1. Решаем задачи с помощью логических функций
    • Как задать простое логическое условие в Excel
    • Используем логические функции Excel для задания сложных условий
    • Функция ЕСЛИ в Excel на простом примере
  2. Подсчет и суммирование в Excel
    • Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ
    • Суммирование в Excel, используя функции СУММ и СУММЕСЛИ
    • Как посчитать накопительную сумму в Excel
    • Вычисляем средневзвешенные значения при помощи СУММПРОИЗВ
  3. Работа с датами и временем в Excel
    • Дата и время в Excel – основные понятия
    • Ввод и форматирование дат и времени в Excel
    • Функции для извлечения различных параметров из дат и времени в Excel
    • Функции для создания и отображения дат и времени в Excel
    • Функции Excel для вычисления дат и времени
  4. Поиск данных
    • Функция ВПР в Excel на простых примерах
    • Функция ПРОСМОТР в Excel на простом примере
    • Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах
  5. Полезно знать
    • Статистические функции Excel, которые необходимо знать
    • Математические функции Excel, которые необходимо знать
    • Текстовые функции Excel в примерах
    • Обзор ошибок, возникающих в формулах Excel
  6. Работа с именами в Excel
    • Знакомство с именами ячеек и диапазонов в Excel
    • Как присвоить имя ячейке или диапазону в Excel
    • 5 полезных правил и рекомендаций по созданию имен ячеек и диапазонов в Excel
    • Диспетчер имен в Excel – инструменты и возможности
    • Как присваивать имена константам в Excel?
  7. Работа с массивами в Excel
    • Знакомство с формулами массива в Excel
    • Многоячеечные формулы массива в Excel
    • Одноячеечные формулы массива в Excel
    • Массивы констант в Excel
    • Редактирование формул массива в Excel
    • Применение формул массива в Excel
    • Подходы к редактированию формул массива в Excel

Внесение настроек в таблицу

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

15

Быстро сделать привлекательный дизайн таблицы не составит никакого труда благодаря готовым шаблонам. Их можно найти в группе «Стили таблиц». Если же нужно внести свои изменения в дизайн, это также легко сделать.

16

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

17

Но одна из самых интересных возможностей любой таблицы – срезы.

18

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

19

После этого в отдельной панели появляется перечень уникальных значений определенной колонки.

20

Чтобы применить фильтр, необходимо кликнуть по нужной категории.

21

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

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

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

22

Обучение основам Excel: ячейки и числа

Многие начинающие пользователи, после запуска Excel — задают один странный вопрос: «ну и где тут таблица?». Между тем, все клеточки, что вы видите после запуска программы — это и есть одна большая таблица!

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

слева: в ячейке (A1) написано простое число «6»

Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число «6».

справа: в ячейке (C1) с виду тоже простое число «6», но если выбрать эту ячейку, то вы увидите формулу «=3+3» — это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например «=3+5+8» (без кавычек). Результат вам писать не нужно — Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму — можно написать формулу двумя способами:

  • первый: «=5+6» (не совсем удобно, представьте, что в ячейке A1 — у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
  • второй: «=A1+B1» — а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!).

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

  1. в строке 2 написать формулу «=A2+B2», в строке 3 — «=A3+B3» и т.д. (это долго и утомительно, этот вариант никогда не используют);
  2. выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! (Примечание: так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Растягивание формулы

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 — то увидите формулу «=A2+B2» (т.е

Excel автоматически подставляет формулы и сразу же выдает результат).

Как задать константу (ячейку, которая не будет меняться при копировании формулы)

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

Далее в ячейке E2 пишется формула «=D2*G2» и получаем результат. Только вот если растянуть формулу, как мы это делали до этого,  в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу «D3*G3», в 4-ю строку: «D4*G4» и т.д. Надо же, чтобы G2 везде оставалась G2…

Чтобы это сделать — просто измените ячейку E2 — формула будет иметь вид  «=D2*$G$2». Т.е. значок доллара $ — позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже).

Константа / в формуле ячейка не изменяется

Как удалить сводную таблицу?

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

  • Если таблица находится на отдельном листе, просто удалите этот лист.
  • Ежели она расположена вместе с некоторыми другими данными на листе, выделите всю её с помощью мыши и нажмите клавишу Delete.
  • Щелкните в любом месте в сводной таблице, которую хотите удалить, перейдите на вкладку «Анализ» (см. скриншот выше) => группа «Действия», нажмите небольшую стрелку под кнопкой «Выделить», выберите «Вся сводная таблица», а затем нажмите Удалить.

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

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

Возможно, вам также будет полезно:

Форматирование таблицы excel для печати

Обработка и редактирование списков и таблиц обычно заканчивается их печатью. Для этого необходимо поработать с меню ФАЙЛ – Параметры страницы.

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

Важно! При работе с программой Microsoft Excel нужно усвоить:

  1. все действия выполняются с текущей или выделенной ячейкой;
  2. выделять можно строку, столбец и диапазон ячеек;
  3. команды меню можно применять как к текущей ячейке, так и к выделенной области.

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

Таблица и именованный диапазон

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

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

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

= СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)

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

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

Примеры использования формул

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

Арифметика

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

  1. Создайте таблицу с какими-нибудь условными данными.
  1. Для того чтобы высчитать сумму, введите следующую формулу. Если хотите прибавить только одно значение, можно использовать оператор сложения («+»).

=СУММ(B3:C3)

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

=B3-C3

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

=B3/C3%

  1. Для определения среднего значения используйте следующую формулу.

=СРЗНАЧ(B3:C3)

  1. В результате описанных выше выражений, вы увидите следующий итог.

Условия

Считать ячейки можно с учетом определенных условий.

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

=СУММЕСЛИ(B3;»>3″;B3:C3)

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

=СУММЕСЛИМН(B3:B9;B3:B9;»>2″;B3:B9;»<6″)=СУММЕСЛИМН(C3:C9;C3:C9;»>2″;C3:C9;»<6″)

  1. Также можно посчитать количество элементов, которые удовлетворяют какому-то условию. Например, пусть Эксель посчитает, сколько у нас чисел больше 3.

=СЧЁТЕСЛИ(B3:B9;»>3″)=СЧЁТЕСЛИ(C3:C9;»>3″)

  1. Результат всех формул получится следующим.

Математические функции и графики

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

В качестве примера попробуем построить графики для экспоненты и какого-нибудь уравнения. Инструкция будет следующей:

  1. Создадим таблицу. В первой графе у нас будет исходное число «X», во второй – функция «EXP», в третьей – указанное соотношение. Можно было бы сделать квадратичное выражение, но тогда бы результирующее значение на фоне экспоненты на графике практически пропало бы.
  1. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.

=EXP(B4)=B4+5*B4^3/2

  1. Дублируем эти выражения до самого конца. В итоге получаем следующий результат.
  1. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».
  1. Выбираем тип «Линия». Для продолжения кликаем на «OK».

  1. Результат получился довольно-таки красивый и аккуратный.

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

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

Добавление графиков/диаграмм

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

Чтобы создать диаграмму/график, нужно:

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

Рис. 11 – выбор типа используемой диаграммы

Кликните на необходимую форму. На листе автоматически отобразится уже заполненная диаграмма:

Рис. 12 – результат добавление диаграммы

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

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

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

Тематические видеоролики:

Microsoft Excel для Начинающих #1

Microsoft Excel для Начинающих #2

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

Редактор таблиц Microsoft Excel имеет очень широкий набор возможностей для решения задач самой разной сложности в различных сферах деятельности. Именно благодаря этому Эксель стал таким популярным среди пользователей по всему миру. Одним из базовых навыков работы с программой является проведение простейших вычислений и математических операций. В этой статье подробно разберём, как выполнять сложение, вычитание, умножение и деление в Excel. Давайте же начнём! Поехали!

Математические операции выполняются без использования калькулятора

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

Обратите внимание на то, что каждая ячейка таблицы имеет свой адрес, который определяется буквой и цифрой. Каждая буква соответствует столбцу, а каждая цифра — строке

Начнём с самых простых операций — сложения и вычитания. Для сложения чисел можно использовать, так называемую функцию «Автосумма». Ей удобно пользоваться в случаях, когда необходимо посчитать сумму чисел, которые стоят подряд в одной строке, столбце либо в выделенной вами области. Чтобы воспользоваться этим инструментом, перейдите во вкладку «Формулы». Там вы обнаружите кнопку «Автосумма». Выделив участок таблицы со значениями, которые нужно сложить, кликните по кнопке «Автосумма». После этого появится отдельная ячейка, содержащая результат вычисления. Это был первый подход.

Второй подход заключается в том, что формула для расчёта вводится вручную. Допустим, перед вами стоит задача вычислить сумму чисел, разбросанных по таблице. Для этого сделайте активной (кликните по ней левой кнопкой мыши) ячейку, в которую желаете поместить результат вычисления. Затем поставьте знак «=» и по очереди вводите адрес каждой ячейки, содержимое которой нужно просуммировать, не забывая ставить знак «+» между ними. К примеру, у вас должно получиться: «=A1+B7+C2+B3+E5». После того как будет введён адрес последней ячейки, нажмите на клавиатуре «Enter» и вы получите сумму всех отмеченных чисел. Необязательно вводить каждый адрес вручную. Достаточно кликнуть по определённой ячейке и в поле для формул сразу отобразится её адрес, ставьте после него «+» и переходите к следующей.


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

Вычитание в Excel выполняется таким же способом, как и сложение. Вам понадобится ввести формулу, указав необходимые ячейки, только вместо знака «+» между адресами ставится «–».

Чтобы умножить числа в Экселе, напишите формулу, отмечая нужные данные и ставя между ними знак «*». Формула будет иметь следующий вид: «=A3*A7*B2».

Деление производится аналогичным образом, только используется знак «/». Также вы можете выполнять несколько арифметический операций сразу. Формулы строятся по математическим правилам. Например: «=(B2-B4)*E8/(A1+D1)*D4». Построенная вами формула может быть любой сложности, главное, не забывать основные математические правила, чтобы расчёт был выполнен верно.

Синтаксис формулы массива

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

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

Затем ставим курсор в строку формул, и там записываем =C3:C8*D3:D8.

3

Превращаем формулу в формат массива. Нужно нажать Ctrl + Shift + Enter.

После этого мы получаем таблицу с готовыми промежуточными итогами.

4

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

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

5

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

  1. Выделяем ту ячейку, которая будет содержать результат вычисления по формуле массива.
  2. Вводим туда формулу. В нашем случае мы будем суммировать значения из целевого диапазона, поэтому используем формулу СУММ(C3:C8*D3:D8).
  3. С использованием комбинации Ctrl + Shift + Enter осуществляем трансформацию стандартной формулы в ту, которая работает с массивами данных.

По итогу, получаем следующий результат.

6

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

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

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

Составные части формул массива:

  1. Функция массива. Это описание той операции, которую Эксель должен выполнить. 
  2. Массивы диапазона. Это непосредственно те диапазоны, которые будут обрабатываться формулой. 
  3. Оператор массива – знак двоеточия. Обозначает расстояние в определенное количество ячеек между конкретными адресами. 

Чтобы было проще понять, вот небольшой рисунок.

7

Эксель — ваш универсальный электронный помощник

При помощи Excel вы сможете выполнять сложные расчёты, не прибегая к программированию в большинстве случаев. Впрочем, возможность использовать дополнительный код тоже заложена — при помощи внутреннего языка программирования VBA (расшифровывается «Visual Basic for Application»).

Excel позволяет работать с тремя типами данных:

  • числа
  • текст
  • формулы

Рабочее поле Excel понятно организовано: все столбцы пронумерованы буквами латинского алфавита, а строки — цифрами.

Формулы позволяют автоматизировать подсчёты. Если, например, в ячейке А1 меняется значение, то во всех остальных ячейках, которые содержат формулы с обозначением ячейки А1, идёт автоматический пересчёт данных.

Примеры задач, которые могут быть решены функционалом Excel:

  • Однотипные сложные расчёты
  • Автоматизация вычислений
  • Наглядное представление данных в диаграммах
  • Статистический анализ
  • Подготовка таблиц и диаграмм к печати
  • Создание отчётов
  • Решение задач на оптимизацию

С этим читают