10 лайфхаков, которые помогут упростить работу в Excel
Мы все практически каждый день пользуемся программой Excel и, порой, ситуация складывается таким образом, что задачи, которые перед нами возникают, нужно выполнить в максимально кратчайшие сроки. Для того, чтобы Вы смогли успешно преодолеть возникающие трудности, мы предлагаем Вам несколько полезных лайфхаков, которые существенно упростят Вашу работу в Excel.
Лайфхаки сделаны на примере EXCEL 2013-2016, но будут работать и в более ранних/поздних версиях.
1. Сохранить формулу как значение. Копировать только данные без форматирования
Довольно часто мы работаем со сложными документами, которые содержат ряд многоступенчатых расчетов, происхождение которых нас мало интересует. А главная цель – скопировать необходимую информацию в наш файл. Если мы данные ячеек, которые вычислялись при помощи формул, скопируем в свой документ при помощи команд Копировать
+ Вставить
, то получим не совсем то, что ожидали:
Это происходит потому, что данная команда копирует содержимое ячейки, а оно, в свою очередь, представляет собой формулу.
Чтобы этого избежать, нужно воспользоваться комбинацией команд Копировать
+ Вставить как значения
:
Команду Вставить как значения
можно вызвать при помощи нажатия правой клавиши мыши и вызова контекстного меню Специальная вставка
.
Ускорить это процесс можно вынесением данной команды на панель быстрого запуска:
Найти эту команду можно следующим образом: нажать стрелочку на данной панели ➤ выбрать меню Другие команды
➤ в разделе Все команды
найти необходимую и добавить на панель. После этого данной командой можно будет воспользоваться кратким сочетанием клавиш <ALT>+порядковый номер данной команды на ленте. В моем случае <ALT>+1.
2. Скопировать формулу так, чтобы сохранились оригинальные ссылки
Мы уже говорили, что при копировании ячеек, которые содержат формулы, комбинация команд Копировать
+ Вставить
копирует именно формулы. Но, важно знать, что при их копировании смещаются ссылки на ячейки, собственно поэтому мы вначале получили нули:
Чтобы формула заработала в скопированной ячейке нужно выполнить следующую последовательность действий: на исходной ячейке В1 нажимаем кнопку F2 ➤ в строке формул копируем данную формулу
При помощи команды Вставить
вставляем данную формулу в нужную ячейку.
И получаем нужные данные:
3. Быстро найти ячейки на листе, содержащие формулы
Если Вы работаете в своем документе, то вопрос этот скорее всего не возникнет. Но если нужно быстро разобраться в чужом – Вам эти знания скорее всего пригодятся. Чтобы буквально за одну секунду получить данные о том, какие ячейки содержат формулы, необходимо: зайти на вкладку Главная
➤ группа Редактирование
➤ меню Найти и выделить
➤ Формулы
. Сразу по факту запуска команды на листе будут выделены все ячейки, содержащие формулы.
4. Создать в ячейке список
Очень часто, когда нужно стандартизировать заполнение документа, чтобы каждый пользователь заполнял данные в строго указанном формате, проще сделать список:
Как это сделать: заходим на вкладку Данные
➤ группа Работа с данными
➤ Проверка данных
➤ и в открытом окне на вкладке Параметры
для типа данных выбираем Список
, а источником может быть либо часть Вашего документа, либо введенные вручную через запятую данные.
Также на этой вкладке Вы сможете для себя найти еще много интересных возможностей.
5. Быстро вставить или удалить строку/столбец
Когда работаете в таблице с большим количеством строк и столбцов, удобно работать с клавиатуры и не прибегать к помощи мыши. Для того, чтобы работа велась оперативно, предлагаем Вам ознакомиться с несколькими клавиатурными сокращениями:
- <Shift>+<пробел> — выделить всю строку
- <Ctrl>+<пробел> -выделить весь столбец
- <Ctrl>+<-> — удалить выделенный столбец/строку
- <Ctrl>+<+> — добавить строку/столбец перед выделенным
6. Сортировка по нескольким столбцам
Чтобы отсортировать таблицу, которая имеет большое количество столбцов по нескольким (к примеру, по трем) столбцам, можно это сделать вручную по порядку сортируя сначала первый, затем второй и так далее. Но если столбцов для сортировки достаточно много и стоят они не по порядку, то очень легко запутаться.
Допустим в ниже приведенной таблице мы хотим отсортировать данные сначала по менеджерам, затем по сумме, а после этого по городам:
Для этого, предварительно выделив нужную нам таблицу, проще воспользоваться командой Сортировка
(вкладка Данные
➤ группа Сортировка и фильтр
). В открывшемся окне мы в таком же порядке добавляем поля таблицы:
И в итоге получаем таблицу в ожидаемом виде:
7. Автоматически заполнить дни недели / рабочие дни и пр
Для того, чтобы мы не писали все эти данные вручную, создатели Excel о нас уже позаботились и нам достаточно в первую ячейку ввести Пн
и за нижний правый уголок ячейки протянуть курсор до нужного места, после чего в возникшем контекстном меню выбрать нужную нам команду, в данному случае — команду Заполнить
:
Точно также можно заполнить в таблице по порядку даты, например, только рабочих дней (эти и некоторые другие команды Вы увидите во все том же контекстном меню):
8. Быстро
размножитьформулу на весь столбец
Если Вы имеете таблицу, в которой нет пустых строк, то для того, чтобы формула распространилась на весь столбец, достаточно левой клавишей мыши два раза кликнуть по нижнему правому уголку верхней ячейки:
Но если в таблице есть хоть одна пустая строка, именно на ней застопорится
распространение формулы:
Для этого проще поступить так: через команду Копировать
скопировать формулу верхней ячейки ➤ выделить весь интересующий диапазон ➤ воспользоваться командой Вставить
, тогда все формулы правильно проставятся в весь выделенный диапазон:
9.
Умная таблица
Excel предусмотрела возможность работать как с диапазонами, так и с умными таблицами
. В предыдущем пункте мы создали таблицу, представляющую диапазон данных. Но если мы хотим в один клик придать ему приятное форматирование, чтобы в выбранном формате при необходимости добавлялись столбцы и строки, то можно преобразовать диапазон в умную
таблицу. Для этого в данном диапазоне выделяем ячейку и на вкладке Вставка
➤ группа Таблицы
➤ нажимаем команду Таблица
. Весь наш диапазон выделится автоматически и таблица сразу преобретет новое форматирование:
Помимо вышеописанных моментов (быстрого форматирования и пр.), для умной таблицы
можно добавлять срезы (о них более детально напишем в последующих блогах), в один клик создавать сводную таблицу, которая будет автоматически обновлять диапазон данных по мере увеличения данных в нем, а также можно добавить строку итогов и выбрать способ его расчета для каждой колонки таблицы:
10.
Сделать так, чтобы шапкадокумента печаталась на каждой странице
Если Ваша таблица занимает несколько листов, то вполне логично, что Вы хотите, чтобы ее шапка
разместилась на каждом из них. Для этого можно воспользоваться командой Печатать заголовки
на вкладке Разметка страницы
в группе Параметры страницы
. Так, для необходимого элемента (в нашем случае строка) выделяем диапазон:
После этого шапка
таблицы распечатается на каждом листе документа:
Вернуться к списку статей
Создание и ведение таблиц Excel
Возможности Excel » Диапазоны » Создание умных таблиц
Листы Excel, состоящие из ячеек, сами являются таблицами, в которых в крайнем слева столбце уже стоят номера строк, а в верхней строке – буквы, обозначающие названия столбцов. Но на практике требуются таблицы определенного размера, то есть содержащие заданное число строк и столбцов.
Итак, таблица в Excel – это прямоугольная область листа, в которой каждая строка представляет собой набор данных, а в ячейке на пересечении данной строки и каждого столбца находится единица данных. Каждому столбцу присваивается уникальное имя. Столбцы таблицы называются полями, а строки – записями. В таблице не может быть записей, в которых нет данных ни в одном поле.
Если на листе Excel выбран прямоугольный диапазон ячеек, его легко преобразовать в таблицу, и, наоборот, с записями таблицы можно работать как с обычным диапазоном ячеек.
В таблицах можно использовать функции и формулы Excel, в частности, можно вставить строку (запись), в которой подсчитываются общие или промежуточные итоги.
Создание таблицы
- Выделить любую ячейку, содержащую данные, которые должны будут войти в таблицу.
- В ленте меню выбрать вкладку Вставка [Insert], в раскрывшейся группе команд Таблицы [Tables] необходимо выбрать команду Таблица [Table].
- Появится диалоговое окно, в котором Excel автоматически предложит границы диапазона данных для таблицы
Если этот диапазон нужно изменить, достаточно выделить нужный диапазон данных курсором.
- ОК.
Присвоение имени таблице
По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.
- Выделить ячейку таблицы.
- На вкладке Конструктор [Design], в группе Свойства [Properties] ввести новое имя таблицы в поле
Требования к именам таблиц аналогичны требованиям к именованным диапазонам.
Форматирование таблиц
В созданной таблице можно изменить цвет, шрифт, вид границ и прочее.
С помощью форматирования можно добиться того, что информация будет легче восприниматься. Эти изменения можно выполнить самостоятельно или выбрать готовый стиль оформления.
- Выделить ячейку таблицы.
- На вкладке Конструктор [Design] выбрать нужное оформление в группе Стили таблиц [Table Styles].
Вычисления в таблицах
К таблице можно добавить дополнительную, итоговую строку, в которой будут размещены результаты выполнения различных функций, примененных к данным некоторых или всех полей.
Порядок действия следующий:
- На вкладке Конструктор [Design] в группе Параметры стилей таблиц [Table Style Options], выбрать Строка итогов [Total Row].
- В появившейся новой строке Итог [Total] выбрать поле, в котором нужно обработать данные, и в раскрывающемся меню выбрать нужную функцию.
Для ввода в конец таблицы новых записей следует выделить строку итогов и воспользоваться правой кнопкой мыши.
Появится контекстное меню, в нем нужно выбрать пункт Вставить. Если в появившуюся новую строку ввести данные, они будут автоматически участвовать в пересчете итогов.
Для того чтобы сократить время на добавление строк в таблицу, можно отключить строку итогов и довалять новые данные с новой строки. В этом случае таблица будет автоматически расширять свой диапазон.
Для проведения расчетов и помещения результатов в новое поле, достаточно ввести формулу в одну ячейку этого поля. Excel автоматически размножит ее по всем ячейкам данного поля. Если правильно установлены параметры Excel, при вводе формулы в нее записываются не адреса ячейки, а имена полей.
Если вместо имени поля на экране в формулах указаны адреса ячеек, необходимо изменить настройку:
- Выбрать вкладку Файл [File] или кнопку Офис [Office], в зависимости от версии Excel; затем вкладку Параметры [Options].
- В разделе Формулы [Formulas], в группе Работа с формулами [Working with formulas], отметить пункт Использовать имена таблиц в формулах [Use table name in formulas].
- OK.
Преобразование таблицы в обычный диапазон
При работе с таблицами, наряду с преимуществами, действует ряд ограничений: нельзя объединять ячейки, нельзя добавлять промежуточные итоги и т.д. Если расчеты в таблице завершены и нужны только данные из нее и, возможно, оформление, таблицу можно быстро преобразовать в обычный диапазон данных.
Для этого необходимо выполнить следующие шаги:
- На вкладке Конструктор [Design] выбрать группу Сервис [Tools].
- Выбрать вкладку Преобразовать в диапазон [Convert to Range].
- Нажать на кнопку Да [Yes].
Рекомендуем к прочтению
Заливка чередующихся строк в Excel
Как сделать отступ в ячейке Excel
Сортировка данных в Excel
Фильтрация данных в Excel
Комментарии:
Please enable JavaScript to view the comments powered by Disqus.
comments powered by Disqus
Использовать вычисляемые столбцы в таблице Excel
Excel
Импорт и анализ данных
Столы
Столы
Использовать вычисляемые столбцы в таблице Excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Дополнительно…Меньше
Вычисляемые столбцы в таблицах Excel — отличный инструмент для эффективного ввода формул. Они позволяют вам ввести одну формулу в одну ячейку, а затем эта формула автоматически расширится до остальной части столбца сама по себе. Нет необходимости использовать команды «Заполнить» или «Копировать». Это может невероятно сэкономить время, особенно если у вас много строк. И то же самое происходит, когда вы меняете формулу; изменение также распространится на остальную часть вычисляемого столбца.
Примечание. Снимки экрана в этой статье были сделаны в Excel 2016. Если у вас другая версия, представление может немного отличаться, но, если не указано иное, функциональность такая же.
Создать вычисляемый столбец
Создать таблицу. Если вы не знакомы с таблицами Excel, вы можете узнать больше по ссылке: Обзор таблиц Excel.
Вставить новый столбец в таблицу. Вы можете сделать это, введя текст в столбце справа от таблицы, и Excel автоматически расширит таблицу за вас. В этом примере мы создали новый столбец, введя «Итого» в ячейку D1.
Советы:
Вы также можете добавить столбец таблицы с вкладки Домашняя страница .
Просто нажмите на стрелку Вставить > Вставить столбцы таблицы слева .
Введите формулу, которую вы хотите использовать, и нажмите Введите .
В этом случае мы ввели =sum( , затем выбрали столбцы Qtr 1 и Qtr 2 . В результате Excel построил формулу: =СУММ(Таблица1[@[Квартал 1]:[Квартал 2]]) . Это называется формулой структурированной ссылки , которая уникальна для таблиц Excel. Формат структурированной ссылки позволяет таблице использовать одну и ту же формулу для каждой строки. Обычная формула Excel для этого будет =СУММ(B2:C2) , которую затем вам нужно будет скопировать или заполнить до остальных ячеек в столбце
. Дополнительные сведения о структурированных ссылках см. в разделе Использование структурированных ссылок с таблицами Excel.Когда вы нажимаете Enter, формула автоматически заполняется всеми ячейками столбца — выше и ниже ячейки, в которую вы ввели формулу. Формула одинакова для каждой строки, но, поскольку это структурированная ссылка, Excel внутренне знает, какая строка какая.
Просто нажмите на стрелку Вставить > Вставить столбцы таблицы слева .
Дополнительные сведения о структурированных ссылках см. в разделе Использование структурированных ссылок с таблицами Excel.Примечания:
При копировании или заполнении формулы во все ячейки пустого столбца таблицы также создается вычисляемый столбец.
Если ввести или переместить формулу в столбец таблицы, который уже содержит данные, вычисляемый столбец не создается автоматически.
Если вы введете новую формулу, которая отличается от существующих формул в вычисляемом столбце, столбец будет автоматически обновлен новой формулой. Вы можете отменить обновление и сохранить только одну новую формулу из кнопки Параметры автозамены . Как правило, это не рекомендуется, поскольку это может помешать вашему столбцу автоматически обновляться в будущем, поскольку он не будет знать, какую формулу расширять при добавлении новых строк.
Если вы ввели или скопировали формулу в ячейку пустого столбца и не хотите сохранять новый вычисляемый столбец, дважды щелкните Отменить .
Вы также можете нажать Ctrl + Z дважды
Вы также можете нажать Ctrl + Z дваждыВычисляемый столбец может содержать ячейку с формулой, отличной от остальных. Это создает исключение, которое будет четко отмечено в таблице. Таким образом, непреднамеренные несоответствия могут быть легко обнаружены и устранены.
Примечание. Исключения вычисляемого столбца создаются при выполнении любого из следующих действий:
Введите данные, отличные от формулы, в ячейку вычисляемого столбца.
Введите формулу в ячейку вычисляемого столбца, а затем нажмите Отменить на панели быстрого доступа .
Скопируйте в вычисляемый столбец данные, которые не соответствуют формуле вычисляемого столбца.
Примечание. Если скопированные данные содержат формулу, эта формула перезапишет данные в вычисляемом столбце.
Удалить формулу из одной или нескольких ячеек вычисляемого столбца.
Примечание. Это исключение не отмечено.
Переместите или удалите ячейку в другой области рабочего листа, на которую ссылается одна из строк в вычисляемом столбце.
org/ListItem»>Введите новую формулу в вычисляемый столбец, который уже содержит одно или несколько исключений.
Уведомление об ошибке появится только в том случае, если у вас включена опция фоновой проверки ошибок. Если вы не видите ошибку, перейдите к File > Options > Формулы > убедитесь, что установлен флажок Включить фоновую проверку ошибок .
Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel > Формулы .
Если вы используете Mac, перейдите к Excel в строке меню, а затем нажмите Настройки > Формулы и списки > Ошибка проверки .
Параметр автоматического заполнения формул для создания вычисляемых столбцов в таблице Excel включен по умолчанию.
Если вы не хотите, чтобы Excel создавал вычисляемые столбцы при вводе формул в столбцах таблицы, вы можете отключить параметр заполнения формул. Если вы не хотите отключать этот параметр, но не хотите всегда создавать вычисляемые столбцы во время работы с таблицей, вы можете запретить автоматическое создание вычисляемых столбцов.
Включение или выключение вычисляемых столбцов
На вкладке Файл щелкните Параметры .
Если вы используете Excel 2007, нажмите кнопку Office , затем Параметры Excel .
org/ListItem»>Нажмите Проверка .
В разделе Параметры автозамены щелкните Параметры автозамены .
Перейдите на вкладку Автоформат при вводе .
Под Автоматически при работе , установите или снимите флажок Заполнить формулы в таблицах для создания вычисляемых столбцов , чтобы включить или выключить эту опцию.
Совет: Можно также нажать кнопку Параметры автозамены , которая отображается в столбце таблицы после ввода формулы. Нажмите «Управление параметрами автозамены», а затем снимите флажок «Заполнить формулы в таблицах для создания вычисляемых столбцов», чтобы отключить этот параметр.
Остановить автоматическое создание вычисляемых столбцов
После ввода первой формулы в столбце таблицы нажмите отображаемую кнопку Параметры автозамены , а затем щелкните Остановить автоматическое создание вычисляемых столбцов .
Если вы используете Mac, перейдите к Excel в главном меню, затем Настройки > Формулы и списки > Таблицы и фильтры > Автоматически заполнять формулы .
Вы также можете создавать настраиваемые вычисляемые поля с помощью сводных таблиц, где вы создаете одну формулу, а Excel применяет ее ко всему столбцу. Узнайте больше о вычислении значений в сводной таблице.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
См. также
Обзор таблиц Excel
Отформатировать таблицу Excel
Изменение размера таблицы путем добавления или удаления строк и столбцов
Суммируйте данные в таблице Excel
Как использовать формулу в таблице Excel (4 подходящих примера)
Таблица Excel позволяет создавать формулы, которые применяются ко всей таблице, легко копируются и имеют больше возможностей, чем традиционные формулы, которые называются структурированными ссылками. Эта статья поможет вам понять все удивительные возможности таблиц Excel и убедит вас использовать формулы в таблицах Excel.
Скачать книгу с практиками
Что такое таблица Excel?
Создать формулу и применить к таблице Excel
Еще 3 примера с формулой в таблице Excel
1.
Формула СУММЕСЛИ с одним критерием
2. Формула СУММЕСЛИМН с несколькими критериями
3. Формула COUNTIF для подсчета на основе критериев
Вывод
Статьи по Теме
Загрузить практическое пособиеВы можете скачать бесплатный шаблон Excel отсюда и попрактиковаться самостоятельно.
Что такое таблица Excel?
Можно сказать, что Таблицы Excel являются контейнерами для наших данных. Представьте, что у вас есть кладовая, и там вы расставите свои книги на полке, электроинструменты на другой полке. Таблицы Excel похожи на эти полки. Это помогает содержать и организовывать данные на ваших листах таким образом. Таблицы управляют Excel тем, что все данные связаны.
Создание формулы и применение к таблице Excel
То, как мы используем формулу Excel на типичном листе Excel и в таблице Excel, немного отличается, но не так сложно.
Я покажу несколько простых примеров, которых будет достаточно, чтобы научиться этому. Начнем с функция СУММ . Перед этим я сначала познакомлю вас с моим набором данных. Он представляет информацию о продаже: идентификатор заказа, дата, проданная единица и тип продажи компании.
Сначала мы преобразуем наш набор данных в таблицу.
Шаг 1:
⏩ Выберите любую ячейку из набора данных.
⏩ Затем щелкните следующим образом: Вставка > Таблицы > Таблица
Шаг 2:
⏩ Теперь просто нажмите ОК здесь.
Смотрите, наш стол готов. Я изменил название таблицы на «Продажи». Для этого-
Шаг 3:
⏩ Нажмите любые данные в таблице и нажмите Дизайн таблицы > Имя таблицы
Теперь подсчитаем общее количество проданных единиц с помощью функции СУММ .
Шаг 4:
⏩ Активировать Ячейка D14 .
⏩ Введите =СУММ(Продажи[
Затем вы увидите, что он показывает имена заголовков нашей таблицы, как показано на рисунке ниже.
⏩ Выберите Продано единиц.
Он называется Структурированный справочник . Здесь нет необходимости использовать ссылки на ячейки.
Шаг 5:⏩ Наконец, закройте скобки и нажмите кнопку Enter .
Теперь у нас есть общее количество проданных единиц.
Рассчитать общую стоимость:
Далее я покажу, как использовать итоговый столбец в формуле. Для этого я изменил таблицу. Я добавил цены на товары и стоимость их доставки. Затем, чтобы рассчитать итог, я добавил новый столбец.
Шаги:
⏩ Введите имя столбца рядом с последним заголовком и нажмите кнопку Введите , таблица Excel автоматически преобразует его в часть таблицы.
Вот наш новый столбец.
Кроме того, вы можете нажать- Главная > Ячейки > Вставить > Вставить столбцы таблицы справа , чтобы добавить столбец.
Теперь мы найдем общую стоимость каждого заказа, используя Структурированная ссылка .
Шаги:
⏩ В ячейке E5 введите приведенную ниже формулу —
=СУММ(Таблица1[@[Цена]:[Стоимость доставки]])
⏩ Нажмите кнопку Введите на клавиатуре.
Теперь посмотрите, мы нашли общие значения для всех строк. Здесь нет необходимости использовать инструмент Fill Handle . Это преимущество таблицы Excel.
Подробнее: Создать таблицу в Excel с помощью ярлыка (8 способов)
Еще 3 примера с формулой в таблице Excel
Давайте рассмотрим еще три примера использования формул в таблице Excel.
Допустим, мы посчитаем проданные единицы, которые были проданы в онлайне с функцией СУММЕСЛИ . Функция СУММЕСЛИ возвращает сумму ячеек, удовлетворяющих одному условию.
Шаги:
⏩ Введите данную формулу в ячейку D14 –
=СУММЕСЛИ(Продажи1[Тип продажи],"Онлайн",Продажи1[Проданная единица])
⏩ Затем нажмите кнопку Введите .
Вот наш вывод-
Подробнее: Как использовать таблицу Excel с VBA (9 возможных способов)
Аналогичные показания
- Как создать таблицу в Excel (с настройкой)
- Использование вычисляемого поля в сводной таблице Excel (8 способов)
- Вычисленная сумма полей, деленная на количество в сводной таблице
- Как пользоваться справочной таблицей Excel (10 примеров)
- Код Excel VBA для каждой строки в таблице (добавление, перезапись, удаление и т. д.)
д.) 2. Формула СУММЕСЛИМН с несколькими критериями
Здесь мы будем считать единицы с несколькими критериями. Мы будем считать только те единицы, которые являются продуктами питания и продаются в магазине, используя функцию СУММЕСЛИМН . Функция СУММЕСЛИМН возвращает сумму ячеек, удовлетворяющих нескольким условиям.
Шаги:
⏩ Активировав Cell D14 написать данную формулу-
=СУММЕСЛИМН(Продажи2[Проданная единица],Продажи2[Категория],"Еда",Продажи2[Тип продажи],"Магазин")
⏩ Позже просто нажмите кнопку Enter .
Мы закончили операцию.
Подробнее: Существует ли функция ТАБЛИЦА в Excel?
3. Формула СЧЁТЕСЛИ для подсчета на основе критериев
В нашем последнем примере мы будем считать онлайн-заказы с функция СЧЁТЕСЛИ .