Как работать в таблице эксель: Формулы Excel для вычисления разных типов данных с примерами и описанием

Содержание

Работа со сводными таблицами Excel

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

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

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

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

1. Как сделать сводную таблицу Excel

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

пример исходных данных сводной таблицы

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

Пример создания сводной таблицы

Пример создания сводной таблицы в excel

В версиях MS Excel 2019, 2016, 2013, 2010 и 2007 создание сводной таблицы выполняется практически идентично.

2. Как построить сводную таблицу эксель.

После создания сводной появляется поля сводной таблицы. Поля сводной таблицы – это названия столбцов таблицы исходных данных. Построение сводной таблицы эксель заключается в настройке столбцов, строк значений и фильтров.

Пример построения сводной таблицы

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

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

Строки – для полей сводной таблицы, которые нужно группировать по строкам

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

Настройка параметров полей значений

3. Как обновить данные в сводной таблице excel.

Если в исходных данных произошли изменения, то нужно сделать обновление данных в сводной таблице excel.

Для этого достаточно в любом месте сводной таблицы нажать правой клавишей мыши и выбрать «Обновить»

Обновление данных в сводной таблице excel

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

У нас есть online курс Умные и сводные таблицы, пройдя который вы получите практические навыки в обработке больших массивов данных в том числе с помощью сводных.

Как создать таблицу в Excel • Обучение компьютеру

Программа Microsoft Excel предоставляет поистине безграничные возможности в работе с электронными таблицами. В ней можно проводить математические, экономические, хозяйственные и прочего рода расчёты, представляя различные расчёты с использованием весьма эффектной графики. С этими возможностями мы знакомимся на страницах этого блога, и в этой публикации мы рассмотрим азы работы с Microsoft Excel, а именно, как создать таблицу в данной программе. Азы рассматривать мы будем на примере ритейл-версии Microsoft Excel 2019, но большая часть её возможностей присутствует также и в двух других актуальных ритейл-версиях Excel – 2016 и 2013.

Содержание

Как создать документ Microsoft Excel
Как создать таблицу в Excel: способ №1
Как создать таблицу в Excel: способ 2
Базовые операции в Excel
Виды документа Excel

Как создать документ Microsoft Excel

Таблица в контексте программы Microsoft Excel имеет у нас два значения. Первое – это непосредственно таблицы, создаваемые внутри документа Excel. Второе – сам документ Excel принято называть таблицей, т. к. формат документа являет собой бесконечную вниз и вширь таблицу с идентичной функциональностью её ячеек. Хотя сама компания Microsoft определяет тип документа Excel как книга. Так, при создании нового документа Excel по умолчанию он называется «Книга (1, 2 и т.д.)». Книга – это потому, что внутри документа Excel имеется несколько листов, и мы можем таких листов добавлять в книгу сколь-угодно. Первое, что мы делаем при открытии Microsoft Excel – это создаём сам документ (он же у нас книга, он же таблица). На стартовой странице выбираем «Пустая книга».

Далее сохраняем его, делаем это стандартно через меню «Файл».

Выбираем «Сохранить как», кликаем «Обзор».

Выберем место на компьютере, где хотим сохранить документ Excel. И сохраним с типом файла по умолчанию «Книга Excel».

Важный нюанс: по умолчанию документ Excel сохраняется автоматически у нас каждые 10 минут.

При потребности, если есть риск потери внесённых важных данных, мы можем вручную сохранить документ клавишами Crtl+S или кнопкой в виде дискетки на панели быстрого доступа. Но мы также можем установить меньший временной промежуток для автосохранения документа. Нажмём меню «Файл», кликаем на панели слева внизу «Параметры». В параметрах идём во вкладку «Сохранение» и выберем для графы «Автосохранение каждые …», скажем, 5 минут. Сохраним настройки.

И можем приступать непосредственно к созданию таблиц внутри документа. А создавать таблицы можно разными способами.

Как создать таблицу в Excel: способ №1

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

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

Как создать таблицу в Excel: способ 2

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

И сразу же можем выбрать стиль для таблицы.

Эффектные стили – пожалуй, самое примечательное в данном способе создания таблицы, поскольку все интегрированные операции в таблице не всегда нужны. Интегрированные операции – это отдельные функции сортировки и фильтров Excel в выпадающем списке каждого столбца таблицы. Ничего примечательного в такой интеграции нет, все эти операции есть в главном меню программы, но, возможно, кто-то найдёт в этом своё удобство.

Ну и далее можем перезаписывать дефолтные надписи столбцов «столбец (1, 2, 3 и т.д.)» своими наименованиями, заполнять значениями, применять стили и форматировать.

Базовые операции в Excel

Ну и давайте теперь рассмотрим базовые операции с таблицами в Excel.

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

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

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

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

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

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

Виды документа Excel

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

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

По умолчанию таблица имеет формат листа А4. Но и формат листа, и его поля, и ориентацию листа с книжной на альбомную и наоборот – всё это всегда можно поменять в меню «Разметка страницы».

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

Исследуйте шаблоны, заприметьте интересные в них вещи – стили, форматы, графику, чтобы потом воссоздать в своих наработках.

7 советов по работе с несколькими листами в Excel

Работать с MS Excel достаточно сложно, так как с одним листом. Но когда вы работаете с несколькими листами, сложность работы с этими данными увеличивается в десять раз с каждым добавленным листом.

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

1. Просмотр нескольких листов одновременно

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

а) Откройте рабочую книгу в Excel.

b) Нажмите кнопку Новое окно для каждого листа, который вы хотите просмотреть в этой книге.

c) В каждом новом окне щелкните вкладку листа, который вы хотите просмотреть.

d) Когда рабочие листы отобразятся, нажмите кнопку Кнопка «Упорядочить все» в окне «Вид»

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

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

2. Просмотр дополнительных вкладок рабочего листа

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

Чтобы увидеть больше, просто наведите курсор на 3 точки слева от полосы прокрутки. Затем нажмите и перетащите его вправо. Вы сразу же начнете видеть больше вкладок вашего рабочего листа.

3. Группировка листов вместе

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

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

b) Затем нажмите + CTRL (для несмежных вкладок рабочего листа) или щелкните + Shift (для смежных рабочих листов) на других вкладках рабочего листа, чтобы добавить его в группу.

c) При необходимости измените ячейку. Нажмите на ввод.

Чтобы разгруппировать листы, щелкните правой кнопкой мыши и выберите параметр Разгруппировать листы . Это может показаться базовым советом, но он очень мощный!

4. Копирование формул между рабочими листами

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

а) Когда рабочий лист, содержащий формулу, которую вы хотите скопировать, открыт, CTRL + щелкните на вкладке рабочего листа, в который вы хотите его скопировать.

б) Щелкните или перейдите к ячейке с нужной формулой или данными (в открывшемся листе).

c) Нажмите F2 , чтобы активировать ячейку.

г) Нажмите Enter. Это повторно введет формулу или данные, а также введет их в ту же соответствующую ячейку на другом выбранном листе.

5. Использование формул для связывания рабочих листов

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

Этот учебник от ExcelFormulas дает четкое и понятное объяснение того, как этот трюк может облегчить вашу задачу. Он делает все возможное и даже показывает, как одни и те же методы можно применять к нескольким книгам.

6. Консолидация данных из нескольких листов

Если вам нужно объединить данные из разных листов, это руководство от Tuts+ Computer Skills покажет вам, как шаг за шагом объединить данные из нескольких листов в одну сводную таблицу.

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

7. Импорт нескольких листов Excel из нескольких книг Excel

данные с разных листов и книг на отдельный лист с помощью PowerQuery.

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

Какие у вас есть советы по работе с несколькими листами Excel?

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

Урок 13: Работа с таблицами

/en/excel2007/aligning-text/content/

Введение

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

Рабочие листы

Загрузите пример для работы вместе с видео.

Именование листов

Когда вы открываете книгу Excel, есть три листа по умолчанию , а имена на вкладках по умолчанию — Лист1, Лист2 и Лист3. Это не очень информативные имена. Excel 2007 позволяет создавать понятные имена для каждого листа в книге, чтобы вы могли быстро находить информацию.

Чтобы назвать рабочий лист:

Щелкните правой кнопкой мыши вкладку листа , чтобы выбрать его.
Выберите Переименовать в появившемся меню. Текст выделен черным прямоугольником.

Введите новое имя рабочего листа.

Нажмите на вкладку. Рабочий лист теперь принимает заданное описательное имя.
ИЛИ
Нажмите команду Формат в группе Ячейки на вкладке Главная.
Выберите Переименовать лист . Текст выделен черным прямоугольником.
Введите новое имя рабочего листа.
Нажмите на вкладку. Рабочий лист теперь принимает заданное описательное имя.

Вставка листов

Вы можете изменить количество отображаемых листов по умолчанию, нажав кнопку Microsoft Office и выбрав Параметры Excel . У вас также есть возможность вставлять новые рабочие листы , если это необходимо во время работы.

Чтобы вставить новый рабочий лист:

Щелкните левой кнопкой мыши значок Вставить рабочий лист . Появится новый лист. Он будет называться «Лист4», «Лист5» или любым другим номером следующего последовательного листа в книге.

ИЛИ

Нажмите клавиши Shift и F11 на клавиатуре.

Удаление рабочих листов

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

Чтобы удалить один или несколько рабочих листов:

Щелкните лист(ы), которые хотите удалить.
Щелкните лист(ы) правой кнопкой мыши, и появится меню.
Выбрать Удалить .

ИЛИ

Выберите лист, который хотите удалить.
Щелкните стрелку раскрывающегося списка рядом с Удалить в группе Ячейки на вкладке Главная.
В появившемся меню выберите Удалить лист.

Группировка и разгруппировка рабочих листов

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

Рабочие листы можно объединять в группы. Группировка рабочих листов позволяет применять одинаковые формулы и/или форматирование для всех рабочих листов в группе. Когда вы группируете рабочие листы, любые изменения, сделанные на одном рабочем листе, будут изменены на любых других рабочих листах в группе .

Для группировки смежных рабочих листов:

Выберите первый лист , который вы хотите сгруппировать.
Нажмите и удерживайте клавишу Shift на клавиатуре.
Щелкните последний лист , который вы хотите сгруппировать.

Отпустите клавишу Shift.
Листы теперь сгруппированы . Все листы между первым и последним выбранным листом являются частью группы. Вкладки листов будут отображаться белыми для сгруппированных листов.

Внесите любые изменения в один лист, и изменения появятся во всех сгруппированных листах.

Чтобы сгруппировать несмежные листы:

Выберите первый лист , который вы хотите сгруппировать.
Нажмите и удерживайте клавишу Ctrl на клавиатуре.
Щелкните следующий лист , который вы хотите сгруппировать.
Продолжая щелкать листы, которые вы хотите сгруппировать.
Отпустите клавишу Control.
Теперь листы сгруппированы. Вкладки листов будут отображаться белыми для сгруппированных листов. Только выбранные листы являются частью группы.
Внесите любые изменения в один лист, и изменения появятся во всех сгруппированных листах.

Чтобы разгруппировать рабочие листы:

Щелкните правой кнопкой мыши один из листов.
Выберите Разгруппировать из списка.

Закрепление областей рабочего листа

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

Чтобы заморозить строку:

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

Щелкните вкладку Просмотр .
Щелкните команду Freeze Pane в группе Window.

Выберите Заморозить панели . Появляется тонкая черная линия ниже всего, что застыло на месте.

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

Чтобы разморозить панель:

Щелкните команду Закрепить панель .
Выберите команду Разморозить .

Чтобы заморозить столбец:

Выберите столбец справа от столбцов, которые вы хотите заморозить. Например, если вы хотите, чтобы столбцы A и B всегда отображались слева, выберите столбец C.
Щелкните вкладку View .
Щелкните команду Freeze Pane в группе Window.