Excel работа с таблицами – Работа в Excel с формулами и таблицами данных

Работа в Excel с формулами и таблицами данных

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

Как работать в Excel на офисе

Поиск значения в диапазоне таблицы Excel по столбцам и строкам
Оптимальный поисковый анализ по приблизительному значению в диапазоне матрицы данных. Создание инструмента для планирования и установки целей по таблице с возможностью массивного поиска самых оптимальных показателей по отношению к исходному значению. Как выделить повторяющиеся значения в Excel разными цветами?
Пример удобного макроса для автоматической подсветки добирающихся значений ячеек разными цветами. Как выделить разным цветом дубликаты ячеек? Как сделать график отпусков в Excel скачать бесплатно шаблон
Как автоматически составить график отпусков с определенными условиями для контроля количества сотрудников на работе? Удобный и наглядный график для планирования отпуска сотрудников фирмы.  Удаление дубликатов в Excel с помощью таблиц
Качественное удаление дубликатов средствами умных таблиц. Альтернативные инструменты удаления дубликатов. Формулы для поиска одинаковых значений. Подсветка строки или столбца цветом для активной ячейки
Пример комбинации возможностей условного форматирования и VBA-макроса для автоматической подсветки цветом целой строки или столбца в месте нахождения активной ячейки. Пример функции РАЗНДАТ для расчета времени между датами
Как посчитать количество пройденных лет, месяцев и дней между двумя датами? Примеры формул с использование функции РАЗНДАТ для расчета возраста, сроков и стажа. Формула расчета статистических выбросов с выборкой
Примеры формул с квартилями для определения и расчета статистических выбросов с целью выборки для последующего удаления или их анализа. Сортировка по месяцам и списки порядка сортировки
Сортирование значений по названиям месяцев с использованием настраиваемых списков порядков. Продвинутая универсальная сортировка данных в диапазоне по любым критериям. Возрастная половая пирамида населения
Пример построения возрастной пирамиды с разделением статистики по полу населения на мужчин и женщин. Возрастная гистограмма по статистическим данным в таблице. Обучение сводным таблицам на примерах
Формирования отчетов с помощью сводной таблице на примере работы с клиентской базой данных. Изменение структуры отчетов в настройках полей сводной таблицы. Печать шапки таблицы на каждой странице листа
Настройки параметров страницы, чтобы распечатать шапку таблицы на каждом листе бумаги для последующего удобного просмотра данных. Сквозные строки и сквозные листы в параметрах страницы. БИЗВЛЕЧЬ работа с функциями базы данных
Выборка значений или целых строк из базы данных по одному и более критериев. Практические примеры использования функции БИЗВЛЕЧЬ при работе с базой. Для чего нужен примеры использования формул в жизни
Полезные примеры использования формул и функций для решения повседневных задач в обычной жизни современного человека. Как нам помогают электронные таблицы решать задачи на работе и в быту? Как создать новую вкладку и кнопки
Создание пользовательских вкладок и групп инструментов. Добавление кнопок, которых нет на ленте по умолчанию. Управление вкладками: добавление, удаление, скрытие и отображение.

exceltable.com

Срезы в Excel и другие приемы работы с таблицами

Дата: 22 ноября 2015 Категория: Excel

Поделиться, добавить в закладки или распечатать статью

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

Навигация по таблице

Перемещение по ячейкам таблицы почти не отличается от навигации в обычной табличной структуре Эксель. Вы, так же, можете активировать ячейку мышью, или перемещаться с помощью клавиатурных стрелок и кнопок PgUp, PgDn.

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

Перемещение таблицы

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

1. Перетягиванием. Выделите любую ячейку внутри таблицы и ведите мышь к любой из границ таблицы. Как только курсор примет вид четырёхнаправленной стрелки – зажимайте левую кнопку мыши и тяните таблицу в новое место
2. Вырезанием-вставкой:
   • Выделите всю таблицу любым способом. Например, мышью, или двойным нажатием Ctrl+A
   • Вырежьте таблицу с помощью команды Вырезать контекстного меню, комбинации Ctrl+X, или команды Главная – Буфер обмена – Вырезать
   • Вставьте табличку в новое место. Для этого, установите курсор в ту ячейку, где будет располагаться верхний левый угол таблицы. Выберите Вставить в контекстном меню, нажмите Ctrl+V или выполните Главная – Буфер обмена – Вставить

Благодаря этим простым действиям, вы можете перенести таблицу в любое место на листе.

Выделение элементов таблицы

Чтобы выделить всю строку полностью – наведите мышкой на левую границу первого столбца таблицы. Когда курсор станет чёрной стрелкой – жмите левую кнопку мыши, вся строка таблицы будет выделена. Или же выделите любую ячейку строки и нажмите Shift+Пробел. В принципе, правила выделения не отличаются от общепринятых в Эксель

Для выделения всего столбца – наведите мышкой на верхнюю грань таблицы (в нужном столбце). Как только курсор изменится на стрелку «Вниз» — жмите левой кнопкой мыши. Так же, можно активировать любую ячейку столбца и нажать Ctrl+Пробел. Нажмите эту комбинацию дважды, чтобы программа выделила и шапку.

Выделить всю табличку целиком – выберите любую ячейку таблицы и нажмите Ctrl+A. Для выделения диапазона вместе с шапкой – нажмите Ctrl+A еще раз.  Еще один способ – поместите стрелку мыши в верхний левый угол первого столбца шапки. Когда курсор превратится в диагональную стрелку – сделайте левый клик мышью. Чтобы выделить таблицу с шапкой – повторите такой клик.

Как добавить строку итогов

Обычно, мы вручную добавляем строку «Итого» в конец своей таблички. Если диапазон преобразован в таблицу Excel, программа добавит итоги одним нажатием мыши. Для этого, выделите любую ячейку таблицы и поставьте галочку: Конструктор – Параметры стилей и таблиц – Строка итогов

Вставка строки итогов

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

• Нет – оставить поле пустым
• Среднее – считать среднее значение по столбцу
• Количество – считать количество значений в столбце
• Количество чисел – подсчитать количество числовых значений
• Максимум – вывести максимальное значение в столбце
• Минимум – вывести минимальное значение в столбце
• Сумма – сумма всех значений
• Смещённое отклонение – просчитать соответствующий статистический показатель
• Смещённая дисперсия – считать статистический показатель
• Другие функции – выбрать другую функцию из полного списка

Функции в строке итогов

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

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

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

Как удалить дубликаты из таблицы

Удаление дубликатов в Excel – будь то обычный диапазон или таблица – задание очень простое. Если в вашей табличке есть лишние, «задвоенные» данные – выполните команду: Конструктор – Сервис – Удалить дубликаты. В появившемся окне выберите столбцы, по которым нужно определять дублированные данные и нажмите ОК. Дальше программа всё сделает за вас.

Удаление дубликатов из столбца «Продавец»

Сортировка данных в таблице Excel

Сортировку столбцов таблицы можно делать по одному столбцу или сразу по нескольким. Для этого предложу Вам 2 способа:

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

   Например, в таблице с рисунка нужно отсортировать по фамилии продавца, а потом сумму продаж по убыванию, чтобы увидеть в какие дни продавцы делали наибольшие продажи. Для этого, сначала отсортируем продажи по убыванию, а потом фамилии продавцов от «А» до «Я».

2. С помощью окна сортировки. Выполните команду Главная – Сортировка и фильтр – Настраиваемая сортировка.

Сортировка данных по нескольким параметрам

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

Фильтрация данных

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

Фильтрация с помощью автофильтра

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

Фильтр по активной ячейке

Чтобы отменить заданные фильтры и сортировки – выполните команду Главная – Сортировка и фильтр – Очистить.

Срезы в таблицах Эксель

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

Срезы в Excel

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

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

Вам так же может быть интересна статья о применении формул в таблицах Excel.

В следующем посте я детально расскажу о форматировании текста на листе. Возвращайтесь, будет интересно!

Поделиться, добавить в закладки или распечатать статью

officelegko.com

Как сделать таблицу в Excel. Пошаговая инструкция

Таблицы в Excel представляют собой ряд строк и столбцов со связанными данными, которыми вы управляете независимо друг от друга.

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

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

Как работать в Excel с таблицами. Пошаговая инструкция

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

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

1. Выделите область ячеек для создания таблицы

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

2. Нажмите кнопку “Таблица” на панели быстрого доступа

На вкладке “Вставка” нажмите кнопку “Таблица”.

3. Выберите диапазон ячеек

В всплывающем вы можете скорректировать расположение данных, а также настроить отображение заголовков. Когда все готово, нажмите “ОК”.

4. Таблица готова. Заполняйте данными!

Поздравляю, ваша таблица готова к заполнению! Об основных возможностях в работе с умными таблицами вы узнаете ниже.

Форматирование таблицы в Excel

Для настройки формата таблицы в Экселе доступны предварительно настроенные стили. Все они находятся на вкладке “Конструктор” в разделе “Стили таблиц”:

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

Помимо цветовой гаммы, в меню “Конструктора” таблиц можно настроить:

• Отображение строки заголовков – включает и отключает заголовки в таблице;
• Строку итогов – включает и отключает строку с суммой значений в колонках;
• Чередующиеся строки – подсвечивает цветом чередующиеся строки;
• Первый столбец – выделяет “жирным” текст в первом столбце с данными;
• Последний столбец – выделяет “жирным” текст в последнем столбце;
• Чередующиеся столбцы – подсвечивает цветом чередующиеся столбцы;
• Кнопка фильтра – добавляет и убирает кнопки фильтра в заголовках столбцов.

Как добавить строку или столбец в таблице Excel

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

 

• Выберите пункт “Вставить” и кликните левой клавишей мыши по “Столбцы таблицы слева” если хотите добавить столбец, или “Строки таблицы выше”, если хотите вставить строку.

• Если вы хотите удалить строку или столбец в таблице, то спуститесь по списку в сплывающем окне до пункта “Удалить” и выберите “Столбцы таблицы”, если хотите удалить столбец или “Строки таблицы”, если хотите удалить строку.

Как отсортировать таблицу в Excel

Для сортировки информации при работе с таблицей, нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

В окне выберите по какому принципу отсортировать данные: “по возрастанию”, “по убыванию”, “по цвету”, “числовым фильтрам”.

Как отфильтровать данные в таблице Excel

Для фильтрации информации в таблице нажмите справа от заголовка колонки “стрелочку”, после чего появится всплывающее окно:

• “Текстовый фильтр” отображается когда среди данных колонки есть текстовые значения;
• “Фильтр по цвету” также как и текстовый, доступен когда в таблице есть ячейки, окрашенные в отличающийся от стандартного оформления цвета;
• “Числовой фильтр” позволяет отобрать данные по параметрам: “Равно…”, “Не равно…”, “Больше…”, “Больше или равно…”, “Меньше…”, “Меньше или равно…”, “Между…”, “Первые 10…”, “Выше среднего”, “Ниже среднего”, а также настроить собственный фильтр.
• В всплывающем окне, под “Поиском” отображаются все данные, по которым можно произвести фильтрацию, а также одним нажатием выделить все значения или выбрать только пустые ячейки.

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

 

Как посчитать сумму в таблице Excel

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

В списке окна выберите пункт “Таблица” => “Строка итогов”:

Внизу таблица появится промежуточный итог. Нажмите левой клавишей мыши на ячейке с суммой.

В выпадающем меню выберите принцип промежуточного итога: это может быть сумма значений колонки, “среднее”, “количество”, “количество чисел”, “максимум”, “минимум” и т.д.

Как в Excel закрепить шапку таблицы

Таблицы, с которыми приходится работать, зачастую крупные и содержат в себе десятки строк. Прокручивая таблицу “вниз” сложно ориентироваться в данных, если не видно заголовков столбцов. В Эксель есть возможность закрепить шапку в таблице таким образом, что при прокрутке данных вам будут видны заголовки колонок.

Для того чтобы закрепить заголовки сделайте следующее:

• Перейдите на вкладку “Вид” в панели инструментов и выберите пункт “Закрепить области”:

• Выберите пункт “Закрепить верхнюю строку”:

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

Как перевернуть таблицу в Excel

Представим, что у нас есть готовая таблица с данными продаж по менеджерам:

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

• Выделить таблицу целиком (зажав левую клавишу мыши выделить все ячейки таблицы) и скопировать данные (CTRL+C):

• Переместить курсор мыши на свободную ячейку и нажать правую клавишу мыши. В открывшемся меню выбрать “Специальная вставка” и нажать на этом пункте левой клавишей мыши:

• В открывшемся окне в разделе “Вставить” выбрать “значения” и поставить галочку в пункте “транспонировать”:

• Готово! Месяцы теперь размещены по строкам, а фамилии продавцов по колонкам. Все что остается сделать – это преобразовать полученные данные в таблицу.

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

 

excelhack.ru

Как работать с таблицами в Excel

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

Основные понятия

Строки — строки в Excel нумеруются числами начиная от 1. Ячейки в первой строке будут A1, B1, C1 и так далее.
Столбцы — столбцы в Excel обозначаются латинскими буквами начиная с A. Ячейки в первом столбце будут A1, A2, A3 и так далее.
Выбранная ячейка отображает с какой ячейкой таблицы Вы сейчас работаете.
Ссылка на ячейку показывает адрес выбранной ячейки.
Панель формул отражает введенную в выбранную ячейку информацию, будь то число, текст или формула.

Как выбрать ячейку

Существует несколько способов, что бы выбрать ячейку:

• Что бы выбрать одну ячейку, просто кликните на нее левой кнопкой мышки
• Что бы выбрать всю строку, кликните на номер строки
• Что бы выбрать весь столбец, кликните на букву столбца
• Что бы выбрать несколько смежных ячеек, кликните на угловую ячейку, зажмите левую кнопку мышки и протяните влево/вправо, вверх/вниз.
• Что бы выбрать несколько несмежных ячеек, кликайте на них, зажав Ctrl.
• Чтобы выбрать каждую клетку на листе, нажмите в правом верхнем углу листа слева от «A»

Как внести данные в ячейку

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

Как редактировать содержимое ячейки.

Для того, что бы изменить содержимое ячейки в Excel, выберите ее двойным щелчком левой кнопкой мышки. Либо выберите стандартым методом, а затем нажмите F2. Также Вы можете менять данные в ячейки при помощи в панели формул.

Как переместиться между ячейками при помощи клавиатуры

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

Как переместить или копировать ячейку

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

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

Как вставить столбец или строку

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

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

 
  
Резюме

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

Если у Вас появились вопросы, задайте их в комментариях под статьей.

Похожие сообщения:

www.excelguide.ru

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

Пользователи создают сводные таблицы для анализа, суммирования и представления большого объема данных. Такой инструмент Excel позволяет произвести фильтрацию и группировку информации, изобразить ее в различных разрезах (подготовить отчет).

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

Как сделать сводную таблицу из нескольких файлов

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

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

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

1. В ячейке-мишени (там, куда будет переноситься таблица) ставим курсор. Пишем = — переходим на лист с переносимыми данными – выделяем первую ячейку столбца, который копируем. Ввод. «Размножаем» формулу, протягивая вниз за правый нижний угол ячейки.
2. По такому же принципу переносим другие данные. В результате из двух таблиц получаем одну общую.
3. Теперь создадим сводный отчет. Вставка – сводная таблица – указываем диапазон и место – ОК.

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

Покажем, к примеру, количество проданного товара.

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



Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Обновление данных:

Курсор должен стоять в любой ячейке сводного отчета.

Либо:

Правая кнопка мыши – обновить.

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

1. Курсор стоит в любом месте отчета. Работа со сводными таблицами – Параметры – Сводная таблица.
2. Параметры.
3. В открывшемся диалоге – Данные – Обновить при открытии файла – ОК.

Изменение структуры отчета

Добавим в сводную таблицу новые поля:

1. На листе с исходными данными вставляем столбец «Продажи». Здесь мы отразим, какую выручку получит магазин от реализации товара. Воспользуемся формулой – цена за 1 * количество проданных единиц.
2. Переходим на лист с отчетом. Работа со сводными таблицами – параметры – изменить источник данных. Расширяем диапазон информации, которая должна войти в сводную таблицу.

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

Инструкция по добавлению пользовательского поля:

1. Определяемся, какие функции будет выполнять виртуальный столбец. На какие данные сводной таблицы вычисляемое поле должно ссылаться. Допустим, нам нужны остатки по группам товаров.
2. Работа со сводными таблицами – Параметры – Формулы – Вычисляемое поле.
3. В открывшемся меню вводим название поля. Ставим курсор в строку «Формула». Инструмент «Вычисляемое поле» не реагирует на диапазоны. Поэтому выделять ячейки в сводной таблице не имеет смысла. Из предполагаемого списка выбираем категории, которые нужны в расчете. Выбрали – «Добавить поле». Дописываем формулу нужными арифметическими действиями.
4. Жмем ОК. Появились Остатки.

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

Скачать пример работы

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

exceltable.com

работа с таблицами, графиками, сортировкой данных и математическими расчетами

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

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

Подробная информация о базовых функциях и возможностях табличного процессора MS Excel. Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Кстати, чтобы эффективнее работать с таблицами Exel можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Работа с ячейками. Заполнение и форматирование

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

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

Рис. 1 – пример заполнения ячеек

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

Выберем ячейку «Доля акций». Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое. Формул у этой клетки нет, поэтому они не показываются.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

Сортировка данных

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

Перед вами уже заполненная таблица (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию. Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

• Выделить таблицу или блок информации;
• Открыть кладку «Данные»;
• Кликнуть на иконку «Сортировка»;

Рис. 3 – вкладка «Данные»

• В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь).
• Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
• Подтвердите действие, нажав на «ОК».

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

Математические расчеты

Для этого, вам необходимо кликнуть на третью ячейку, в которую будет вписан конечный результат вычислений. Затем нажмите на значок функции f(x), как показано на рисунке ниже. В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее. Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ».

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2». Выберите первое поле и кликните мышкой на ячейку с цифрой «2». Её адрес запишется в строку аргумента. Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно. Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3», «Число 4» и так далее.

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

Рис. 7 – результат выполнения подсчетов

Создание таблиц

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

Таблицы в Excel имеют преимущество перед аналогичной опцией в Word и других офисных программах. Здесь у вас есть возможность создать таблицу любой размерности. Данные заполняются легко. Присутствует панель функций для редактирования содержимого. К тому же, готовую таблицу можно интегрировать в файл docx с помощью обычной функции копирование-вставка.

• Откройте вкладку «Вставка». В левой части панели опций выберите пункт «Таблица». Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
• С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
• Нажмите ОК, чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

Практичные советы — Как объединить ячейки в Excel

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

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

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

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

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

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

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

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

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

Как закрепить строку в Excel — Подробные инструкции

Выпадающий список в Excel — Инструкция по созданию

 

 

Рекомендовать

ddr64.ru

Таблица Эксель — Cоздание и настройка работы

Таблица Эксель позволяет удобно хранить разные данные.

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

Все пользователи хоть раз стыкались с таким действием, как создание таблицы.

Для этой цели применяются самые разные утилиты, но чаще всего используют приложения из пакета Microsoft Office.

Многие говорят, что легче всего справиться с задачей помогает текстовый редактор Ворд, но для таблиц создана специальная программа Эксель.

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

Содержание:

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

Создание таблицы

В Экселе сделать таблицу можно двумя вариантами.

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

Вставка элемента:

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

Вставка обычной таблицы

Совет! Для этого действия также подходят комбинации кнопок CTRL+L и CTRL+T.

• Когда в диапазоне есть данные, которые необходимы для применения в заголовке таблицы, то надо оставить отметку возле строки «Таблица с заголовками»;

Выбор наличия заголовков

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

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

Когда табличка уже появилась на листе Excel, то активируется иконка «Работа с таблицами» в которой есть пункт «Конструктор».

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

Важно! Функционал в разных версиях Excel похожий, но в новых продуктах нет строки со звездочкой (*), которая в версии 2003 года выполняла функцию быстрого добавления строк и столбцов.

вернуться к меню ↑

Редактирование информации в форме таблицы

Для этой цели инструкция содержит всего три пункта:

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

Выбор стиля элемента

• Выбираем необходимый стиль, тут программа предлагает светлые, средние и темные варианты оформления.

Также есть возможность создания пользовательских стилей. Если такой занесен в программу, то он будет доступен в разделе «Пользовательские».

вернуться к меню ↑

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

Для этой цели:

• Нажимаем мышкой в любом участке таблицы. Это действие выведет на экран меню «Работа с таблицами» и вкладку «Конструктор».

Меню «Конструктор»

• После открытия этой вкладки нажимаем на «Сервис» и выбираем строчку «Преобразовать в диапазон».

Преобразование в диапазон

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

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

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

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

Кнопка «Отменить»

Если вам интересны другие статьи на эту тему, читаем здесь:

вернуться к меню ↑

Удаление таблицы

Если с вопросом, как сделать таблицу в Excel мы уже разобрались, то вопрос с ее удалением все еще актуален.

На самом деле все очень просто, особых манипуляций совершать не нужно:

1. Выделяем элемент на странице.
2. Нажимаем кнопку «Delete».

Клавиша «Delete» на клавиатуре ноутбука

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

вернуться к меню ↑

Правила работы с умными таблицами

Простая таблица с данными – это не то что принесло программе Excel известность, простой элемент и в Ворде доступен.

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

Умные они потому, что сами могут рассчитать определенное действие.

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

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

Поэтому необходимо ознакомиться с общими правилами создания умных таблиц.

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

• Каждый столбец должен получать уникальное имя колонок.
• В готовой таблице не должны оставаться пустые места, это навредит работе формулы.
• В колонке не должны встречаться разнородные данные. Например, если в колонке указаны доходы семьи в рублях, то не стоит в те же строки вносить данные в долларах или другой валюте.
• Без потребности не стоит объединять ячейки таблицы.
• Таблица должна выглядеть одинаково во всех участках. Для этого надо рисовать сетку и выделять одним цветом заголовки.
• Чтобы заголовок оставался в поле зрения, надо закреплять области.
• Выбирать стандартный фильтр.
• Использовать строку подитогов.

Промежуточные итоги

• Умно применять абсолютные и относительные ссылки в формулах действий. Это надо делать так, чтобы ссылки протягивались без изменения данных.
• Не затрагивать столбцы и строки, которые находятся вне зоны таблицы. Часто люди желают окрасить лишь один столбец на 10-20 ячеек, но при этом случайно затрагивают сотни сторонних элементов и потом удивляются, почему файл Exel отнимает так много места.

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

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

Видео по теме:

Таблица Эксель — Cоздание и настройка

Проголосовать

geek-nose.com