Excel границы в формуле

Автоматическое определение диапазона «от-до» в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.

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

Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки «граница»). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.

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



Динамическое определение границ выборки ячеек

Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:

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

Следующим шагом будет динамическое определение адреса для выборки диапазона данных из исходного списка в соответствии с критерием отбора.

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

Как получить адрес диапазона ячеек в Excel?

После чего динамически определим адрес начальной ячейки, с которой будет начинаться диапазон. В C2 вводим следующую формулу:

Во втором аргументе функции НАИМЕНЬШИЙ указывается ссылка на ячейку C1, где находится порядковый номер интересующего нас сектора данных (диапазона). А для функции НАИМЕНЬШИЙ – это значение является порядковым номером наименьшего числа в диапазоне вспомогательного столбца $A$7:$A$22 (первый аргумент).

Аналогичным образом динамически определяем адрес последней ячейки, где должна заканчивается выборка. Для этого в C3 водим формулу:

Как не сложно догадаться во втором аргументе функции НАИМЕНЬШИЙ мы прибавляем единицу чтобы получить следующее по порядку наименьшее значение в вспомогательном столбце $A$7:$A$22. Все просто и красиво – такая должна быть магия!

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

Автоматическая подсветка цветом диапазонов ячеек по условию

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

1. Выделите диапазон ячеек C7:C22 и выберите иснтрумент «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».



2. В появившемя окне выберите опцию «Использовать формулу для определения форматируемых ячеек». Там же в поле ввода введите такую фомрулу:



3. Нажмите на кнопку формат и укажите цвет для подсветки соответствующих ячеек. Например, зеленый.

Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон. Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно.

Проверка вводимых значений в Excel на ошибки

Наконец, вы можете предупредить ошибку в случае ввода неверных (не соответствующего формата) значений (числа меньшего или равного нулю, большего, чем общее количество диапазонов) в качестве номера диапазона, который вы ищете. Все это можете сделать при помощи проверки данных. Перейдите на ячейку для ввода критериев выборки C1 и выберите инструмент: «ДАННЫЕ»-«Проверка данных».

Соответствующая формула «обеспечивающая безопасность» могла бы выглядеть так:

Нажмите ОК, после внесения всех изменений как показано выше на рисунке.

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

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

Именованный диапазон в MS EXCEL

​Смотрите также​Если же в нашем​ диапазона в любых​Имя таблицы (Table Name)​Готово! В результате мы​ по 4-ем магазинам​ для временных вычислений.​ номера диапазона, который​Изменяемое значение критерия для​

​(Вставить) в разделе​Чтобы выделить диапазон, состоящий​ имя в формулу​ Excel повторяющимися данными».​ Если диапазон находится​С помощью диапазона​ в любой строке​

​ Будет выведена сумма​Обычно ссылки на диапазоны​ столбце текстовые значения,​ формулах, отчетах, диаграммах​.​ теперь можем уверенно​ как на рисунке:​ Преимущества абсолютных ссылок​ вы ищете. Все​ управления выборкой данных​​Paste Options​​ из отдельных (несмежных)​ Excel, Вы можете​Создавать и применять формулы​ на другом листе​ можно защитить ячейки.​

​ ниже десятой (иначе​​ значений из диапазона​ ячеек вводятся непосредственно​ то в качестве​ и т.д. Для​Теперь можно использовать динамические​ работать с нашей​

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

Задача1 (Именованный диапазон с абсолютной адресацией)

​ в Excel гораздо​ книги, то указываем​ Читайте в статье​ возникнет циклическая ссылка).​

​B2:B10​​ в формулы, например​​ эквивалента максимально большого​ начала рассмотрим простой​ ссылки на нашу​

​ базой данных. Указываем​

• ​ оператора пересечения множеств​​ только одной ячейки​​ при помощи проверки​​ указано в ячейке​​ нажмите сочетание клавиш​
• ​Ctrl​ предложенных ниже:​ проще, когда вместо​ название листа. Нажимаем​
• ​ «Пароль на Excel.​Теперь введем формулу =СУММ(Сезонные_Продажи)​
• ​.​ =СУММ(А1:А10). Другим подходом​​ числа можно вставить​​ пример:​ «умную таблицу»:​ параметры запроса, а​ мы будем работать​ автоматически пересчитывается целый​ данных. Перейдите на​ C1. Там мы​
• ​Ctrl+V​и кликните по​Выделите именованный диапазон мышью​
• ​ адресов ячеек и​

​ «ОК».​ Защита Excel» тут.​​ в ячейку​​Также можно, например, подсчитать​ является использование в​ конструкцию ПОВТОР(“я”;255) –​Задача​Таблица1​​ в ячейке теперь​​ с этим отчетом​

​ диапазон ячеек без​ ячейку для ввода​ указываем порядковый номер​

​.​ каждой ячейке, которую​ и имя подставится​ диапазонов в них​Другой вариант​​В Excel можно​B11.​ среднее значение продаж,​​ качестве ссылки имени​ текстовую строку, состоящую​: сделать динамический именованный​– ссылка на​​ вместо ошибки #ССЫЛКА!​​ как с базой​

​ лишних изменений.​ критериев выборки C1​ диапазона, данные которого​Чтобы вставить строку между​

Задача2 (Именованный диапазон с относительной адресацией)

​ хотите включить в​ в формулу автоматически.​ используются имена. Имя​присвоить имя диапазону Excel​ выделить как смежные​​Затем, с помощью​​ записав =СРЗНАЧ(Продажи).​ диапазона. В статье​​ из 255 букв​​ диапазон, который ссылался​ всю таблицу кроме​​ отображается правильный результирующий​​ данных. В ячейках​

​Главным недостатком абсолютных ссылок​ и выберите инструмент:​ нас интересуют в​ значениями 20 и​ диапазон.​Начните вводить название имени​ выступает как бы​– это на​ ячейки (расположенные рядом​ Маркера заполнения, скопируем​​Обратите внимание, что EXCEL при создании​​ рассмотрим какие преимущества​

​ «я» — последней​

• ​ бы на список​​ строки заголовка (A2:D5)​​ ответ.​ A8 и B8​ является плохая читабельность​ «ДАННЫЕ»-«Проверка данных».​ конкретный момент.​ 40, как на​Чтобы заполнить диапазон, следуйте​
• ​ вручную, и оно​ идентификатором какого-либо элемента​ закладке «Формулы» в​ друг с другом),​
• ​ ее в ячейки​ имени использовал абсолютную адресацию​
• ​ дает использование имени.​ буквы алфавита. Поскольку​​ городов и автоматически​​Таблица1[#Все]​Примечание. Хотя списки​ мы создаем запрос​
• ​ формул. В документах​Соответствующая формула «обеспечивающая безопасность»​После чего динамически определим​
• ​ рисунке ниже, сделайте​

​ инструкции ниже:​ отобразится в списке​ рабочей книги. Имя​ разделе «Определенные имена»​ так и не​С11D11E11​​ $B$1:$B$10. Абсолютная ссылка​​Назовем Именованным диапазоном в​ при поиске Excel,​ растягивался-сжимался в размерах​– ссылка на​ можно и не​ к базе, а​ для долгосрочного использования​

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

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

Использование именованных диапазонов в сложных формулах

​ не рядом).​ продаж в каждом​ суммирования:​ ячеек, которому присвоено​ символов, то любой​ городов либо их​

​ (A1:D5)​

​ названия магазинов и​ получим результирующий ответ.​ лучше использовать имена.​0;$D$7​ начинаться диапазон. В​​3​​B2​​ списка​​ фигурам и т.д.​

​Как удалить диапазон Excel.​Чтобы быстро найти​ из 4-х сезонов.​​в какой ячейке на​​ Имя (советуем перед​ текст в нашей​ удалении.​​Таблица1[Питер]​​ месяцев вручную. Списки​ Сначала создадим все​ Они обладают теми​

​Нажмите ОК, после внесения​

​ C2 вводим следующую​.​.​Использовать в формуле​ Мы же рассмотрим​Заходим на закладку​

​ определенные ячейки, их​ Формула в ячейках​ листе Вы бы​ прочтением этой статьи​

excel2.ru

Диапазон в Excel.

​ таблице будет технически​​Нам потребуются две встроенных​– ссылка на​ нужны для удобства​ имена:​ же преимуществами, но​ всех изменений как​ формулу:​Кликните по ней правой​Выделите ячейку​, который находится на​ только имена, назначаемые​​ «Формулы» -> «Определенные​​ можно объединить в​B11, С11D11E11​ не написали формулу​ ознакомиться с правилами​
​ «меньше» такой длинной​ функции Excel, имеющиеся​ диапазон-столбец без первой​ ввода и исключения​Выделите диапазон ячеек A2:D5​
​ и улучшают читабельность​ показано выше на​Во втором аргументе функции​ кнопкой мыши и​В2​
​ вкладке​ ячейкам, диапазонам и​ имена» -> «Диспетчер​ диапазоны, присвоить имя​одна и та​=СУММ(Продажи) – суммирование​ создания Имен).​
​ «яяяяя….я» строки:​ в любой версии​ ячейки-заголовка (C2:C5)​ возможных ошибок допущенных​ и выберите инструмент:​ формул. Это существенно​ рисунке.​ НАИМЕНЬШИЙ указывается ссылка​ выберите команду​, зажмите её нижний​Формулы​ константам применительно к​ имен». Из списка​ диапазонам, сделать закладку​
​ же!​ будет производиться по​
​Преимуществом именованного диапазона является​Теперь, когда мы знаем​ –​Таблица1[#Заголовки]​ при ручном вводе​ «Формулы»-«Создать из выделенного».​ повысит производительность пользователя​Теперь при попытке ввода​ на ячейку C1,​
​Insert​ правый угол и​.​ формулам Excel.​ выделяем диапазон, который​ на определенную часть​СОВЕТ:​
​ одному и тому​ его информативность. Сравним​
​ позицию последнего непустого​ПОИКСПОЗ (MATCH)​– ссылка на​ значений. Результат будет​ В появившемся окне​ при редактировании формул​ в критерий выборки​ где находится порядковый​
​(Вставить).​ протяните вниз до​Итак, в данном уроке​Приведем небольшой пример. Представим,​ хотим удалить, нажимаем​ таблицы. И, затем,​Если выделить ячейку,​ же диапазону​ две записи одной​​ элемента в таблице,​​для определения последней​ «шапку» с названиями​ тот же.​ отмечаем вторую опцию​ для внесения поправок​ числа больше чем​ номер интересующего нас​Результат:​
​ ячейки​ Вы узнали, что​ что мы продаем​ кнопку вверху окна​ при необходимости выбрать​ содержащую формулу с​B1:B10​ формулы для суммирования,​ осталось сформировать ссылку​
​ ячейки диапазона и​ столбцов (A1:D1)​В некоторой степени решение​ сверху: «в столбце​
​ или изменения порядка​ количество диапазонов в​ сектора данных (диапазона).​Строки, расположенные ниже новой​В8​ такое имена ячеек​ элитную косметику и​
​ «Удалить». Здесь же​ ​ нужный диапазон, закладку.​​ именем диапазона, и​.​ например, объемов продаж:​ на весь наш​ИНДЕКС (INDEX)​
​Такие ссылки замечательно работают​
​ данной задачи можно​ слева».​ аргументов вычислений. Даже​ пределах «границ», будет​ А для функции​ строки, сдвигаются вниз.​.​ и диапазонов в​ получаем фиксированный процент​ можно изменить имя​
​ Как сделать закладки​​ нажать клавишу​Иногда выгодно использовать не​ =СУММ($B$2:$B$10) и =СУММ(Продажи).​
​ диапазон. Для этого​ ​для создания динамической​ в формулах, например:​​ выполнить и без​Выделите диапазон ячеек B1:D5​ спустя несколько лет​ предупреждение для пользователя:​ НАИМЕНЬШИЙ – это​ Аналогичным образом можно​Результат:​ Excel. Если желаете​ комиссионных с продаж.​ и состав диапазона.​ в таблице, читайте​F2​ абсолютную, а относительную​ Хотя формулы вернут​ используем функцию:​
​ ссылки.​=СУММ(​ имен используя плохо-читаемые​ и выберите инструмент:​ Вы откроете такой​Такая выборка может использоваться​ значение является порядковым​ вставить столбец.​Эта техника протаскивания очень​
​ получить еще больше​ На рисунке ниже​Найти диапазон в Excel​ в статье «Сделать​, то соответствующие ячейки​ ссылку, об этом​ один и тот​
​ИНДЕКС(диапазон; номер_строки; номер_столбца)​ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления)​Таблица1[Москва]​ абсолютные адреса ссылок​ «Формулы»-«Создать из выделенного».​ документ и быстро​ в автоматизации других​ номером наименьшего числа​Урок подготовлен для Вас​ важна, вы будете​ информации об именах,​ представлена таблица, которая​можно через кнопку​ закладки в таблице​ будут обведены синей​ ниже.​
​ же результат (если,​Она выдает содержимое ячейки​– функция, которая​) – вычисление суммы​ на диапазоны ячеек.​ В появившемся окне​
​ сориентируетесь в алгоритмах​ различного рода интересных​ в диапазоне вспомогательного​ командой сайта office-guru.ru​ часто использовать её​

excel-office.ru

Знакомство с именами ячеек и диапазонов в Excel

​ читайте следующие статьи:​ содержит объем продаж​ в строке адреса​ Excel».​ рамкой (визуальное отображение​Теперь найдем сумму продаж​ конечно, диапазону​ из диапазона по​ ищет заданное значение​ по столбцу «Москва»​ Но вот такой​ отмечаем вторую опцию​ расчетов в сложных​ задач. Здесь же​ столбца $A$7:$A$22 (первый​Источник: http://www.excel-easy.com/introduction/range.html​ в Excel. Вот​

​Как присвоить имя ячейке​ по месяцам, а​ ячейки.​Как выделить не смежные​ Именованного диапазона).​ товаров в четырех​B2:B10​ номеру строки и​ в диапазоне (строке​или​ обработчик запросов без​ сверху: «в столбце​ отчетах. Тем более​ приведен только базовый​

​ аргумент).​Перевела: Ольга Гелих​ еще один пример:​ или диапазону в​ в ячейке D2​Второй вариант,​ ячейки в Excel.​Предположим, что имеется сложная​ сезонах. Данные о​

​присвоено имя Продажи),​ столбца, т.е. например​ или столбце) и​=ВПР(F5;​ использования имен сделать​ сверху». Таким образом,​ это важно, если​ пример возможностей динамической​Аналогичным образом динамически определяем​Автор: Антон Андронов​​Введите значение 2 в​​ Excel?​ хранится процент комиссионных.​​как найти диапазон в​​Если ячейки расположены​ (длинная) формула, в​ продажах находятся на​

​ но иногда проще​ функция =ИНДЕКС(A1:D5;3;4) по​ выдает порядковый номер​Таблица1​ гораздо сложнее.​ у нас создались​ документ предназначен для​ выборки данных из​ адрес последней ячейки,​Автоматическое определение диапазона «от-до»​ ячейку​5 полезных правил и​ Наша задача подсчитать​

Простой способ выделить именованный диапазон в Excel

​ Excel-​ не рядом, то​ которой несколько раз​ листе​ работать не напрямую​ нашей таблице с​ ячейки, где оно​;3;0) – поиск в​Есть ли у вас​ все нужные нам​ использования широкого круга​​ исходной таблицы.​​ где должна заканчивается​ в исходной таблице​

​В2​

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

​ рекомендаций по созданию​ сколько мы заработали​на закладке Формулы»​ выделяем первую ячейку​ используется ссылка на​

1. ​4сезона​ с диапазонами, а​ городами и месяцами​
2. ​ было найдено. Например,​ таблице месяца из​ таблицы с данными​ имена. Чтобы убедиться​
3. ​ пользователей.​В основном пользователи Excel​​ выборка. Для этого​​ моно применять для​и значение 4​​ имен в Excel​​ за прошедший год.​

​ в разделе «Определенные​ будущего диапазона. Затем​ один и тот​(см. файл примера)​ с их именами.​ из предыдущего способа​ формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст​ ячейки F5 и​

• ​ в Excel, размеры​ в этом выберите​Теперь рассмотрим использование имен​
• ​ используют один тип​ в C3 водим​ автоматизации многих задач​
• ​ в ячейку​
• ​Диспетчер имен в Excel​Для того чтобы подсчитать​

​ имена» нажимаем на​ нажимаем клавишу «Ctrl»​
​ же диапазон:​

​ в диапазонах:​

office-guru.ru

Диапазон в Excel

• ​Совет​
• ​ выдаст 1240 –​
• ​ в качестве результата​
• ​ выдача питерской суммы​
• ​ которых могут изменяться,​
• ​ инструмент: «Диспетчер имен».​

​ как альтернативный вариант​ имен диапазонов. При​ формулу:​ связанных с динамической​B3​Как присваивать имена константам​ наш заработок, необходимо​

Ячейки, строки, столбцы

​ кнопку «Диспетчер имен».​ и, удерживая её,​=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)​

1. ​B2:B10 C2:C10 D2:D10 E2:E10​​: Узнать на какой диапазон​​ содержимое из 3-й​ число 4, т.к.​​ по нему (что​​ т.е. количество строк​​Перейдите в ячейку C8​​ для выше описанной​

   

2. ​ использовании имени в​​Как не сложно догадаться​​ выборкой значений. Рассмотрим​.​​ в Excel?​​ просуммировать объемы продаж​

   

3. ​ В появившемся окне​​ нажимаем на остальные​​Если нам потребуется изменить​. Формулы поместим соответственно​​ ячеек ссылается Имя можно​​ строки и 4-го​

   

Примеры диапазона

​ слово «март» расположено​ такое ВПР?)​ (столбцов) может увеличиваться​

1. ​ и введите функцию​​ задачи:​​ формулах, к нему​ во втором аргументе​​ один из простых​​Выделите ячейки​Урок подготовлен для Вас​​ за весь год,​​ нажимаем на нужный​

   

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

   

Заполнение диапазона

​ СУММ со следующими​Выделите диапазон ячеек F1:G2​

1. ​ обращаются как к​ функции НАИМЕНЬШИЙ мы​​ для понимания способов​​B2​

   

2. ​ командой сайта office-guru.ru​​ а затем полученный​​ диапазон. Адрес этого​ «Ctrl».​ данных, то это​B11C11 D11E11​​ расположенный в меню​​ D3. Если столбец​

   

   ​ счету ячейке в​

   

   ​ использовать при создании​ процессе работы? Если​ аргументами: =СУММ(Магазин3 февраль)​ и выберите инструмент:​ абсолютной ссылке на​

3. ​ прибавляем единицу чтобы​ реализации данной задачи.​​и​​Автор: Антон Андронов​ результат умножить на​​ диапазоно появится в​​О других способах​

   

4. ​ придется сделать 3​​.​​  Формулы/ Определенные имена/​​ всего один, то​​ столбце A1:A5. Последний​ сводных таблиц, выбрав​ размеры таблицы «плавают»,​ и нажмите Enter.​

   

   ​ «Формулы»-«Определенные имена»-«Создать из​ диапазон ячеек. Хотя​ получить следующее по​Задание является следующим. В​B3​Автор: Антон Андронов​
5. ​ комиссионные. Наша формула​ строке диалогового окна​​ выделения ячеек, столбцов,​​ раза. Например, ссылку ​По аналогии с абсолютной​​ Диспетчер имен.​​ его номер можно​ аргумент функции Тип_сопоставления​

   

6. ​ на вкладке​​ то придется постоянно​​Отлично! В результате мы​​ выделенного».​​ в предыдущем уроке​ порядку наименьшее значение​ одном из столбцов​, зажмите нижний правый​

   

Перемещение диапазона

​Ячейки, строки, столбцы​ будет выглядеть следующим​

1. ​ в строке «Диапазон».​ строк, листов, т.д,​

   

2. ​E2:E8​ адресацией из предыдущей​

   

Копировать/вставить диапазон

​Ниже рассмотрим как присваивать​ не указывать, т.е.​

1. ​ = 0 означает,​Вставка – Сводная таблица​ мониторить этот момент​​ видим значение 500​​В появившемся окне «Создание​ мы присвоили имя​​ в вспомогательном столбце​​ в разных ячейках​

   

2. ​ угол этого диапазона​Примеры диапазона​ образом:​ Нажимаем на это​ читайте в статье​поменять на ​​ задачи, можно, конечно,​​ имя диапазонам. Оказывается,​​ формула ИНДЕКС(A2:A6;3) выдаст​​ что мы ведем​ (Insert – Pivot​​ и подправлять:​​ ¬– прибыль магазина3​

   

Вставка строки, столбца

​ имен из выделенного​ не диапазону, а​ $A$7:$A$22. Все просто​ находятся какие-то значения​ и протяните его​

1. ​Заполнение диапазона​​Такая формула будет вычислять​​ адрес и этот​

   

2. ​ «Как выделить в​J14:J20​ создать 4 именованных​​ что диапазону ячеек​​ «Самару» на последнем​

   

   ​ поиск точного соответствия.​

   

   ​ Table)​ссылки в формулах отчетов,​ за февраль месяц.​ диапазона», отметьте первую​

​ числу (значению).​ и красиво –​
​ (в данном случае​
​ вниз.​

​Перемещение диапазона​

office-guru.ru

Формула для динамического выделения диапазона ячеек в Excel

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

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

​ которые ссылаются на​ Теперь нам только​ опцию: «в строке​Приготовьте лист, на котором​ такая должна быть​ текстовые строки «граница»).​Excel автоматически заполняет диапазон,​Копировать/вставить диапазон​ аргументы, используемые в​ будет выделен пунктирными​ др.».​Но, если перед составлением​ адресацией, но есть​ по разному: используя​Причем есть один не​ не указать, то​ умной таблицы в​ нашу таблицу​ осталось с помощью​

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

​ сложной формулы мы​

Динамическое определение границ выборки ячеек

​ решение лучше. С​ абсолютную или смешанную​ совсем очевидный нюанс:​ функция переключится в​ качестве источника данных:​исходные диапазоны сводных таблиц,​

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

​ Excel.​ присвоим диапазону ​ использованием относительной адресации​ адресацию.​ если ИНДЕКС не​ режим поиска ближайшего​

​Если выделить фрагмент такой​ которые построены по​ запросов, который так​ что значения в​ в другую.​ используйте свое воображение​ (диапазонов). Эти значения​ значений. Классно, не​ собой набор из​

Как получить адрес диапазона ячеек в Excel?

​ стала более понятной,​ еще в том​Нажимаем на выделенный​E2:E8​ можно ограничиться созданием​Пусть необходимо найти объем​

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

​ диапазон правой мышью,​ какое-нибудь имя (например, Цены),​ только​ продаж товаров (см.​ ячейку после знака​ это как раз​

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

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

Автоматическая подсветка цветом диапазонов ячеек по условию

​ алгоритме. Для этого:​ имен ячеек в​ изменяются. Поэтому курсы​ выборки диапазонов данных​

1. ​ разных ячейках. Их​Введите дату 13/6/2013 в​ статье дается обзор​
2. ​ имена. Например, назначим​ в таблице. Подробнее​ меню «Имя диапазона».​ диапазон придется менять​Именованного диапазона Сезонные_продажи.​ 1сезон):​
3. ​ а используется как​ использовать для нахождения​ типа, то при​диапазоны для выпадающих списков,​

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

Проверка вводимых значений в Excel на ошибки

​только 1 раз​Для этого:​Присвоим Имя Продажи диапазону​ финальная часть ссылки​ последней занятой ячейки​ дописывании новых строк​ которые используют нашу​ C8, а именно​ создано одновременно сразу​ формулы, чтобы при​ условию пользователя. Например,​ в них ячеек​В2​ операций с диапазонами.​Продажи_по_месяцам​ в статье «Как​ окне пишем имя​

​и даже не​выделите ячейку​B2:B10​

​ на диапазон после​

​ в нашем массиве.​ они автоматически будут​ таблицу в качестве​ так: =СУММ(ДВССЫЛ(A8) ДВССЫЛ(B8)).​

​ 2 имени. Ячейка​ их изменении не​ воспользуемся условным форматированием.​ также может быть​и дату 16/6/2013​Давайте начнем с выбора​

​, а ячейке В4​ найти скрытый текст​ диапазона. Мы написали​ в формуле, а​B11​. При создании имени​ двоеточия, то выдает​Суть трюка проста. ПОИСКПОЗ​

exceltable.com

Имена диапазонов Excel с абсолютным адресом

​ добавляться к диаграмме.​ источника данных​ И нажмите Enter.​ F2 получит имя​ пришлось редактировать каждую​Будем подсвечивать цвет диапазона,​ разным. Например, на​ в ячейку​ ячеек, строк и​ имя​ в Excel».​ — «январь».​

Преимущества имен диапазонов перед абсолютными ссылками

​ в Диспетчере имен!​, в которой будет​ будем использовать абсолютную​ она уже не​

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

​B3​ столбцов.​Комиссионные​В диапазон ячеек​Первый символ имени​=СУММ(Цены)+СРЗНАЧ(Цены)/5+10/СУММ(Цены)​ находится формула суммирования​ адресацию.​ содержимое ячейки, а​ ячейки в диапазоне​

​ прямые ссылки на​ не даст вам​ выдала ошибку: #ССЫЛКА!​ G2 – «Доллар».​Для решения данной задачи​ номеру указанном в​ сектор данных (диапазон)​

1. ​(на рисунке приведены​Для выбора ячейки​. Теперь нашу формулу​ можно вставить формулу​
2. ​ диапазона должен быть​Более того, при создании​ (при использовании относительной​Для этого:​ ее адрес! Таким​ сверху-вниз и, по​ элементы умной таблицы​ скучать ;)​
3. ​ Не переживайте по​Выделите диапазон C2:D5 и​

​ мы можем обойтись​ критериях выборки C1.​ номер 2.​ американские аналоги дат).​C3​ можно записать в​ массива. Что это​ буквой или символ​ формул EXCEL будет​

​ адресации важно четко​выделите, диапазон​ образом формула вида​ идее, должна остановиться,​ использовать нельзя, но​Гораздо удобнее и правильнее​ этому поводу, все​ выберите инструмент из​ без использования имен​Выделите диапазон ячеек C7:C22​Все, что следует сейчас​Выделите ячейки​кликните по полю​ следующем виде:​ за формулы, где​ подчерквания. Затем можно​ сам подсказывать имя​ фиксировать нахождение активной​B2:B10​ $A$2:ИНДЕКС($A$2:$A$100;3) даст на​ когда найдет ближайшее​ можно легко обойти​ будет создать динамический​ под контролем, делаем​ выпадающего меню: «Формулы»-«Определенные​

​ с помощью абсолютных​ и выберите иснтрумент​ сделать — это​B2​

1. ​ на пересечении столбца​Как видите, новая форма​ применяются, смотрите в​ писать и буквы,​
2. ​ диапазона! Для этого​ ячейки в момент​на листе​ выходе уже ссылку​ наименьшее значение к​ это ограничение с​ «резиновый» диапазон, который​ обработчик запросов далее.​ имена»-«Присвоить имя»-Применить имена».​ ссылок. Ниже приведем​ «ГЛАВНАЯ»-«Условное фомратирование»-«Создать правило».​ создать возможность легкого​и​C​ записи формулы стала​ статье «Формулы массива​
3. ​ и цифры, и​ достаточно ввести первую​ создания имени);​1сезон​
4. ​ на диапазон A2:A4.​ заданному. Если указать​ помощью тактической хитрости​ автоматически будет подстраиваться​Создадим еще 2 имени.​

​В появившемся окне выделите​ пример. Но имена​В появившемя окне выберите​ и быстрого выбора​B3​и строки​ более очевидной и​ Excel».​ подчеркивание. Длина названия​

​ букву его имени.​на вкладке Формулы в​;​И вот тут в​ в качестве искомого​ – использовать функцию​ в размерах под​ Выделите диапазон A2:A5​

​ сразу 2 имени,​

Использования имен в Excel при пересечении множеств

​ дают более изящное​ опцию «Использовать формулу​ диапазона, который нас​

​, зажмите нижний правый​3​ простой для восприятия.​С помощью имени​

​ диапазона не должна​Excel добавит к именам​ группе Определенные имена​на вкладке Формулы в​ дело вступает функция​ значение заведомо больше,​ДВССЫЛ (INDIRECT)​ реальное количество строк-столбцов​ чтобы присвоить ему​ а остальное оставьте​ решение данной задачи.​ для определения форматируемых​ интересует (вписывая номер​

1. ​ угол этого диапазона​.​ Можно пойти еще​ диапазона легко найти​ превышать​ формул, начинающихся на​ выберите команду Присвоить​
2. ​ группе Определенные имена​ ПОИСКПОЗ, которую мы​ чем любое имеющееся​, которая превращает текст​ данных. Чтобы реализовать​ имя «магазины». Для​ все по умолчанию​ Для сравнения рассмотрим​ ячеек». Там же​ диапазона в одну​ и протяните его​Чтобы выбрать столбец​
3. ​ дальше и для​ и очистить ячейки​225 символов​ эту букву, еще​ имя;​

​ выберите команду Присвоить​ вставляем внутрь ИНДЕКС,​ в таблице, то​ в ссылку:​ такое, есть несколько​ этого выберите инструмент:​ и нажмите ОК.​ оба варианта.​ в поле ввода​ из ячеек для​ вниз.​

1. ​C​ значения комиссионных создать​ таблицы. Например, таблица​. Если в имени​ и имя диапазона!​в поле Имя введите:​ имя;​ чтобы динамически определить​ ПОИСКПОЗ дойдет до​Т.е. ссылка на умную​
2. ​ способов.​ «Формулы»-«Присвоить имя». Заполните​Это только примитивный пример​Допустим, мы решаем данную​ введите такую фомрулу:​ выбора).​Чтобы переместить диапазон, выполните​, кликните по заголовку​ именованную константу. В​ в Excel «Домашний,​ диапазона больше обного​
3. ​Диапазон в Excel -​ Сезонные_Продажи;​в поле Имя введите:​ конец списка:​ самого конца таблицы,​ таблицу в виде​Выделите ваш диапазон ячеек​ диалоговое окно как​ преимущества использования имен​ задачу с помощью​Нажмите на кнопку формат​​ следующие действия:​
4. ​ столбца​ этом случае исчезнет​ семейный бюджет в​ слова, то соединяем​

​это несколько ячеек​в поле Область выберите​ Продажи;​=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР(«я»;255);A2:A100))​ ничего не найдет​ текстовой строки (в​ и выберите на​ на рисунке. А​ вместо абсолютных ссылок.​

​ абсолютной ссылки на​ и укажите цвет​Для наглядности приведем решение​Выделите диапазон и зажмите​C​ необходимость выделять под​ Excel» здесь. Заполняли​ слова знаком нижнего​ таблицы. Диапазон ячеек​ лист​в поле Область выберите​

​Осталось упаковать все это​ и выдаст порядковый​ кавычках!) превращается в​ вкладке​ потом выделите диапазон​ Вы без проблем​ ячейку со значением​ для подсветки соответствующих​ этой задачи с​ его границу.​

exceltable.com

Динамический диапазон с автоподстройкой размеров

​.​ нее отдельную ячейку​ год. Затем копируем​ тире так. Например:​ можно просто выделить,​4сезона​ лист​ в единое целое.​ номер последней заполненной​ полноценную ссылку, а​Главная – Форматировать как​ B1:D1 и присвойте​

• ​ можете менять курсы​ текущего курса. Тогда​ ячеек. Например, зеленый.​
• ​ использованием вспомогательного столбца.​Перетащите диапазон на новое​Чтобы выбрать строку​
• ​ на рабочем листе​ эту таблицу для​
• ​ «Число_месяцев». Пропусков в​ чтобы настроить формат,​(имя будет работать​1сезон​

​ Откройте вкладку​ ячейки. А нам​ уж ее выпадающий​

​ Таблицу (Home –​ ему имя «месяцы».​ валют (изменяя значения​ нам нужно сделать​Теперь мы изменим критерий​ В первую ячейку​ место.​3​ Excel.​

Способ 1. Умная таблица

​ следующего года. В​ имени не должно​ цвет ячеек, написать​​ только на этом​(имя будет работать​Формулы (Formulas)​​ это и нужно!​

​ список нормально воспринимает.​ Format as Table)​Создаем выпадающий список для​ ячеек F2 и​ так:​ выборки, например, на​​ в вспомогательном столбце​​Чтобы скопировать и вставить​, кликните по заголовку​Назначая имена ячейкам и​ скопированной таблице по​ быть.​ формулу, т.д. Можно​​ листе);​​ только на этом​​и нажмите кнопку​​Если в нашем массиве​

​Если превращение ваших данных​:​ безошибочного запроса к​

• ​ G2), а цены​​Запишем курсы ЕВРО и​ 1. Автоматически подсветился​ (A7) вводим формулу:​
• ​ диапазон, сделайте следующее:​​ строки​ диапазонам в Excel,​ имени диапазона можно​
• ​В строке «Область»​​ присвоить имя этому​убедитесь, что в поле​ листе) или оставьте​
• ​Диспетчер Имен (Name Manager)​​ только числа, то​ в умную таблицу​Если вам не нужен​

​ нашей мини базе​ будут автоматически пересчитаны.​

​ ДОЛЛАРА в отельных​​ зеленым цветом весь​​и копируем ее вниз​Выделите диапазон, кликните по​

​3​

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

​ по каким-либо причинам​ полосатый дизайн, который​ данных. Перейдите в​Примечание. Курсы валют можно​​ ячейках F2 и​ первый диапазон. Обратите​ в оставшиеся ячейки.​​ нему правой кнопкой​.​ одно очень полезное​

​ ячейки таблицы (смежные​ которую будет распространяться​ его в формулах,​ =’4сезона’!B$2:B$10​ имя было доступно​ нажмите кнопку​ искомого значения указать​ нежелательно, то можно​

​ добавляется к таблице​ ячейку А8 и​ хранить не только​ G2.​ внимание в нем​ Везде, где в​ мыши и нажмите​Диапазон представляет собой набор​​ преимущество – возможность​​ и не смежные),​ это имя. Например,​

​ условном форматировании, поиске,​нажмите ОК.​ на любом листе​Создать (New)​ число, которое заведомо​ воспользоваться чуть более​ побочным эффектом, то​

Способ 2. Динамический именованный диапазон

​ выберите инструмент: «Данные»-«Работа​ в значениях ячеек,​В ячейки C2 и​ на одну ячейку​ ячейках соседнего столбца​Copy​ из двух и​ быстро выделять эти​ не задевая формул.​ на всю книгу​ т.д.​Мы использовали смешанную адресацию​ книги;​, введите имя нашего​ больше любого из​ сложным, но гораздо​ его можно отключить​ с данными»-«Проверка данных».​ но и в​ D2 введем формулы,​ больше чем во​

​ находится значение «граница»,​​(Копировать) или сочетание​ более ячеек.​ области. Например, чтобы​О том, как​ (на все её​- именованный диапазон Excel​ B$2:B$10 (без знака​убедитесь, что в поле​

​ диапазона и формулу​ имеющихся в таблице:​ более незаметным и​ на появившейся вкладке​​ В появившемся окне:​​ самих именах. Просто​ которые ссылаются к​​ втором, но все​​ функция возвращает номер​ клавиш​

Ищем последнюю ячейку с помощью ПОИСКПОЗ

​Для выбора диапазона​​ выделить область, у​ создать таблицу в​ листы), или только​. В диапазон можно​ $ перед названием​ Диапазон введена формула​ в поле​Для гарантии можно использовать​ универсальным методом –​Конструктор (Design)​ «Проверка вводимых значений»​ в поле диапазон​ ценам в рублях​ работает безошибочно.​ строки. В противном​Ctrl+C​B2:C4​ которой есть имя,​ Excel, читайте в​ на этот лист,​ выделить всю таблицу​ столбца). Такая адресация​ =’1сезон’!$B$2:$B$10​Диапазон (Reference)​ число 9E+307 (9​ создать в Excel​. Каждая созданная таким​ внесите настройки, так​

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

​ ошибку в случае​ строку.​Выделите ячейку, где вы​ правому углу ячейки​ полю​ таблицу в Excel»​В строке «Примечание»​

​ имя.​ находящиеся в строках​Теперь в любой ячейке​Осталось нажать на​ в 307 степени,​ ссылающийся на нашу​ имя, которое можно​ рисунке. И нажмите​​

​ валютам через абсолютную​ ввода неверных (не​Следующим шагом будет динамическое​ хотите разместить первую​В2​Имя​ тут.​ можно описать этот​Подробнее о применении​2 310​ листа​ОК​ т.е. 9 с​ таблицу. Потом, как​ заменить на более​ ОК.​Теперь приведем более наглядный​

Формируем ссылку с помощью ИНДЕКС

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

​ диапазон, что в​

​ диапазона в Excel,​, в том столбце,​1сезон​и готовый диапазон​ 307 нулями) –​ и в случае​ удобное там же​Таким же образом создайте​ пример существенного преимущества​Скопируем диапазон ячеек C2:D2​ (числа меньшего или​ выборки диапазона данных​ кликните правой кнопкой​ мыши до ячейки​ списка выбрать нужное.​ быстро заполнить данными.​ нем, т.д.​ смотрите в статье​ в котором размещена​

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

​ в простом и​ любых формулах, выпадающих​ которым в принципе​ можно будет свободно​Конструктор (Design)​ месяцами в ячейке​

​Создайте отчет по продажам​

Создаем именованный диапазон

​Данное решение вполне рабочее​ чем общее количество​ в соответствии с​​ команду​​.​​Существует несколько способов вставить​​ заполнить таблицу в​ указываем адрес диапазона.​​ в Excel».​​ суммирования можно разместить​ наглядном виде: =СУММ(Продажи).​ списках или диаграммах.​​ может работать Excel.​​ использовать имя созданного​

​в поле​​ B8.​​ за первый квартал​ и его используют​ диапазонов) в качестве​ критерием отбора.​

planetaexcel.ru

​Paste​

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

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

Как выбирать ячейки, строки и столбцы

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

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

Чтобы выбрать ячейку, нужно нажать по ней. Каждая ячейка имеет адрес. Например, расположенная на пересечении колонки C и ряда 3 называется C3.

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

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

Диапазон ячеек: пример

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

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

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

Как заполнить диапазон

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

1. Ввести нужное значение в ячейку B2. Оно может быть как числовым, так и текстовым. Также возможно введение формулы. В нашем случае это цифра 2.
   
2. Далее нажимаем маркер автозаполнения (как раз тот самый квадратик, который мы ранее просили не нажимать) и перетаскиваем его вниз до конца диапазона.

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

Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.

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

Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.

После этого осуществляем уже знакомое нами перетаскивание.

Перемещение диапазона

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

Потом нужно просто переместить его в нужное место и отпустить мышь.

Копирование и вставка диапазона

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

Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена». 

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

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

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

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

Таким вот способом у нас получилось вставить строку.

Именованные диапазоны

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

Назначить имя диапазону можно через Диспетчер имен, который можно найти в меню Формулы – Определенные имена – Диспетчер имен.

Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.

Пример 1

Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.

В общем, наши действия следующие:

1. Выделить необходимый диапазон.
2. Перейти на вкладку «Формулы» и там найти команду «Присвоить имя».
3. Далее появится диалоговое окно, в котором необходимо указать имя диапазона. В нашем случае это – «Продажи».
4. Там еще находится поле «Область», которое дает возможность выбрать лист, на котором этот диапазон находится.
5. Проверьте, чтобы был указан правильный диапазон. Формула должна быть следующей: =’1сезон’!$B$2:$B$10
6. Кликните «ОК».
   

Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.

Аналогично можно осуществить расчет среднего объема продаж с помощью формулы =СРЗНАЧ(Продажи).

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

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

Пример 2

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

На этом скриншоте диапазоны следующие.

B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10

Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.

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

Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10

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

Далее методика действий та же самая. 

Теперь нам нужно ввести в ячейку B11 формулу =СУММ(Сезонные_Продажи). Далее, используя маркер автозаполнения, переносим ее в соседние ячейки, и получается такой результат.

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

Пример 3

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

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

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

Более того, если начать вводить имя диапазона, Excel его автоматически предложит вместе с другими формулам.

Автоматически изменяемые диапазоны

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

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

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

1. Открыть окно «Присвоить имя».
2. Заполнить поля так, как указано на скриншоте.
   

Важно обратить внимание, что вместо диапазона используется формула с функцией СМЕЩ совместно с функцией СЧЕТ.

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

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

Подсчет чисел, попадающих в диапазон, — обычная задача — с помощью функции СЧЁТЕСЛИ (). Усложняем задачу, делаем интервал легко настраиваемым.

В качестве примера подсчета чисел возьмем список с числовыми значениями от 4 до 30 (см. Файл примера).

Мы будем считать значения, попадающие в диапазон, например (4; 15. Кроме того, пределы диапазона «include » и «не включают ()» будут выбраны из раскрывающегося меню. (выпадающее меню.

Примечание. Решение без выбора диапазона = СЧЁТЕСЛИМН (A2: A12; «>» & D2; A2: A12; «Предполагается, что границы диапазона вводятся в ячейки D2 и F2. Эти ячейки не должны быть пустыми, даже если одна из the limits = 0. Если диапазон A2: A12 содержит числовые значения в текстовом формате, они будут проигнорированы.

Чтобы установить пределы диапазона, используйте проверку данных с типом данных List. В качестве источника мы указываем для левой границы>;> = и для правой СЧЁТЕСЛИМН (A2: A12; C2 & D2; A2: A12; E2 & F2)

• = COUNTIF ($ A $ 2: $ A $ 12; C2 & D2) — (COUNT ($ A $ 2: $ A $ 12) -COUNTIF ($ A $ 2: $ A $ 12; E2 & F2))
• Формула = РАСЧЕТ (A1: A12; A1; H2: I3) требует, чтобы вы сначала создали таблицу с условиями. Заголовки этой таблицы должны точно соответствовать заголовкам исходной таблицы.

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

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

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

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

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

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

Щелкните ОК.

Текст будет выровнен и равномерно распределен в выбранной части ячейки.

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

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

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

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

На вкладке «Выравнивание» в раскрывающемся списке «По вертикали» выберите «Распределенный.

Щелкните ОК.

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

Замена текста ячейки надписью

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

Дважды щелкните нужную ячейку, выделите весь текст и нажмите CTRL + X.

Щелкните другую ячейку, чтобы отменить выбор.

При необходимости измените высоту линии.

На вкладке Вставка щелкните Текстовое поле.

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

Щелкните текстовое поле, чтобы выделить его, затем нажмите CTRL + V, чтобы вставить скопированный текст.

Выделите весь текст в текстовом поле, щелкните правой кнопкой мыши и выберите в контекстном меню пункт «Абзац.

Выберите подходящий ведущий.

Совет: Вы также можете уменьшить интерлиньяж, уменьшив размер шрифта. Выделите текст, щелкните правой кнопкой мыши, выберите «Шрифт» в контекстном меню, затем выберите нужный размер шрифта.

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

У нас есть последовательность чисел, состоящая из практически независимых элементов, которые подчиняются заданному распределению. Обычно равномерно распределяется.

Есть несколько способов и способов генерировать случайные числа в Excel. Считайте только лучшее.

1. Функция RANDOM возвращает случайное и равномерно распределенное действительное число. Он будет меньше 1, больше или равен 0.
2. Функция RANDBETWEEN возвращает случайное целое число.

Давайте посмотрим на их использование на примерах.

Выборка случайных чисел с помощью СЛЧИС

Эта функция не принимает аргументов (RAND()).

Например, чтобы сгенерировать случайное действительное число в диапазоне от 1 до 5, мы применяем следующую формулу: = СЛУЧАЙ () * (5-1) +1.

Возвращенное случайное число равномерно распределяется в интервале [1,10].

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

1. Щелкаем по ячейке со случайным числом.
2. В строке формул выберите формулу.
3. Нажмите F9. И ВХОДИТЕ.

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

1. Формируем «карманы». Диапазоны, в которые будут попадать значения. Первый диапазон этого типа — 0-0,1. Для следующего: формула = C2 + $ C $ 2.
2. Мы определяем частоту случайных чисел в каждом диапазоне. Мы используем формулу массива .
3. Мы формируем интервалы с помощью знака «конкатенация» (= «[0,0 -» & C2&»]»).
4. Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().

Диапазон значений по вертикали — это частота. По горизонтали — «карманы».

Функция СЛУЧМЕЖДУ

Синтаксис функции СЛУЧМЕЖДУ: (нижняя граница; верхняя граница). Первый аргумент должен быть меньше второго. В противном случае функция выдаст ошибку. Предполагается, что пределы являются целыми числами. Формула отбрасывает дробную часть.

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

Случайные числа с точностью 0,1 и 0,01:

Как сделать генератор случайных чисел в Excel

Давайте создадим генератор случайных чисел, сгенерировав значение из определенного диапазона. Мы используем формулу вида: = ИНДЕКС (A1: A10; INT (RAND () * 10) +1).

Создадим генератор случайных чисел в диапазоне от 0 до 100 с шагом 10.

Выберите 2 случайных значения из списка текстовых значений. Используя функцию RANDOM, сравните текстовые значения в диапазоне A1: A7 со случайными числами.

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

Чтобы выбрать случайное значение из списка, примените следующую формулу: = ИНДЕКС (A1: A7; RANDBETWEEN (1; COUNT (A1: A7))).

Генератор случайных чисел нормального распределения

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

Читайте также: Как написать мессенджер для Android

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

Стоимость товара X 100 руб. Вся произведенная партия подлежит нормальному распределению. Случайная величина также подчиняется нормальному распределению вероятностей.

В этих условиях среднее значение диапазона составляет 100 руб. Формируем массив и строим график с нормальным распределением со стандартным отклонением 1,5 руб.

Мы используем функцию: = INV.NORM (RAND (); 100; 1.5).

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

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

1. Определите минимальное и максимальное значение в диапазоне, используя функции MIN и MAX.
2. Мы указываем размер каждого периода или отрывка. В нашем примере — 1.
3. Количество категорий — 10.
4. Нижняя граница таблицы с категориями — ближайшее кратное, округленное в меньшую сторону. В ячейке H1 введите формулу = ЭТАЖ (E1; E5).
5. В ячейке H2 и далее формула будет выглядеть так: = IF (G2; H1 + $ E $ 5; «»). То есть каждое последующее значение будет увеличиваться на размер шага.
6. Мы подсчитываем количество переменных в заданном диапазоне. Мы используем функцию ЧАСТОТА. Формула будет выглядеть так:

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

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

То же самое можно сделать гораздо проще. Использование пакета анализа данных. Выбираем «Сгенерировать случайные числа».

Прочтите, как связать стандартную настройку «Анализ данных» здесь.

Заполните параметры для генерации. Распределение «нормальное».

Щелкните ОК. Получаем набор случайных чисел. Вызовите инструмент анализа данных. Выбираем «Гистограмма». Установка параметра. Убедитесь, что установлен флажок «Устройство графического вывода».

График нормального распределения строится в Excel.

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

Содержание

• Кому важно знать формулы Excel и где выучить основы.

• Элементы, из которых состоит формула в Excel.

• Основные виды.

• Примеры работ, которые можно выполнять с формулами.

• 22 формулы в Excel, которые облегчат жизнь.

• Использование операторов.

• Использование ссылок.

• Использование имён.

• Использование функций.

• Операции с формулами.

• Как в формуле указать постоянную ячейку.

• Как поставить «плюс», «равно» без формулы.

• Самые распространенные ошибки при составлении формул в редакторе Excel.

• Коды ошибок при работе с формулами.

• Отличие в версиях MS Excel.

• Заключение.

Кому важно знать формулы Excel и где изучить основы

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

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

Элементы, из которых состоит формула в Excel

Формулы эксель: основные виды

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

Простые

Позволяют совершить одно простое действие: сложить, вычесть, разделить или умножить. Самой простой является формула=СУММ.

Например:

=СУММ (A1; B1) — это сумма значений двух соседних ячеек.

=СУММ (С1; М1; Р1) — сумма конкретных ячеек.

=СУММ (В1: В10) — сумма значений в указанном диапазоне.

Сложные

Это многосоставные формулы для более продвинутых пользователей. В данную категорию входят ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН. О них подробно расскажем ниже.

Комбинированные

Эксель позволяет комбинировать несколько функций: сложение + умножение, сравнение + умножение. Это удобно, когда, например, нужно вычислить сумму двух чисел, и, если результат будет больше 100, его нужно умножить на 3, а если меньше — на 6.

Встроенные

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

• кликните по нужной ячейке таблицы;

• нажмите одновременно Shift + F3;

• выберите из предложенного перечня нужную формулу;

• в окошко «Аргументы функций» внесите свои данные.

Примеры работ, которые можно выполнять с формулами

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

Поиск перечня доступных функций

Перейдите в закладку «Формулы» / «Вставить функцию». Или сразу нажмите на кнопочку «Fx».

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

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

Вставка функции в таблицу

Вы можете сами писать функции в Excel вручную после «=», или использовать меню, описанное выше. Например, выбрав СУММ, появится окошко, где нужно ввести аргументы (кликнуть по клеткам, значения которых собираетесь складывать):

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

Использование математических операций

Начинайте с «=» в ячейке и применяйте для вычислений любые стандартные знаки «*», «/», «^» и т.д. Можно написать номер ячейки самостоятельно или кликнуть по ней левой кнопкой мышки. Например: =В2*М2. После нажатия Enter появится произведение двух ячеек.

Растягивание функций и обозначение константы

Введите функцию =В2*C2, получите результат, а затем зажмите правый нижний уголок ячейки и протащите вниз. Формула растянется на весь выбранный диапазон и автоматически посчитает значения для всех строк от B3*C3 до B13*C13.

Чтобы обозначить константу (зафиксировать конкретную ячейку/строку/столбец), нужно поставить «$» перед буквой и цифрой ячейки.

Например: =В2*$С$2. Когда вы растяните функцию, константа или $С$2 так и останется неизменяемой, а вот первый аргумент будет меняться.

Подсказка:

• $С$2 — не меняются столбец и строка.

• B$2 — не меняется строка 2.

• $B2 — константой остается только столбец В.

22 формулы в Эксель, которые облегчат жизнь

Собрали самые полезные формулы, которые наверняка пригодятся в работе.

МАКС

МИН

СРЗНАЧ

СУММ

ЕСЛИ

Например:

=ЕСЛИ (В1>10;”больше 10″;»меньше или равно 10″)

В1 — ячейка с данными;

>10 — логическое выражение;

больше 10 — правда;

меньше или равно 10 — ложное значение (если его не указывать, появится слово ЛОЖЬ).

СУММЕСЛИ

Например:

=СУММЕСЛИ (С2: С6;»>20″)

С2: С6 — диапазон ячеек;

>20 —значит, что числа меньше 20 не будут складываться.

СУММЕСЛИМН

Например:

=СУММЕСЛИМН (D2: D6; C2: C6;”сувениры”; B2: B6;”ООО ХУ»)

D2: D6 — диапазон, где суммируются числа;

C2: C6 — диапазон ячеек для категории; сувениры — обязательное условие 1, то есть числа другой категории не учитываются;

B2: B6 — дополнительный диапазон;

ООО XY — условие 2, то есть числа другой компании не учитываются.

Дополнительных диапазонов и условий может быть до 127 штук.

СЧЕТ

=СЧЁТ (значение1; [значение2];…)Формула считает количество выбранных ячеек с числами в заданном диапазоне. Ячейки с датами тоже учитываются.

СЧЕТЕСЛИ и СЧЕТЕСЛИМН

Например:

ЕСЛИОШИБКА

ДНИ

КОРРЕЛ

ВПР

Например:

=ВПР (В1; С1: С26;2)

В1 — значение, которое ищем.

С1: Е26— диапазон, в котором ведется поиск.

2 — номер столбца для поиска.

ЛЕВСИМВ

ПСТР

ПРОПИСН

СТРОЧН

ПОИСКПОЗ

ДЛСТР

СЦЕПИТЬ

ПРОПНАЧ

ПЕЧСИМВ

Использование операторов

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

• скобки;

• экспоненты;

• умножение и деление;

• сложение и вычитание.

Арифметические

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

Оператор объединения текста

Операторы ссылок

Использование ссылок

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

Простые ссылки A1

Они используются чаще всего. Буква обозначает столбец, цифра — строку.

Примеры:

• диапазон ячеек в столбце С с 1 по 23 строку — «С1: С23»;

• диапазон ячеек в строке 6 с B до Е– «B6: Е6»;

• все ячейки в строке 11 — «11:11»;

• все ячейки в столбцах от А до М — «А: М».

Ссылки на другой лист

Если необходимы данные с других листов, используется формула: =СУММ (Лист2! A5: C5)

Выглядит это так:

Абсолютные и относительные ссылки

Относительные ссылки

Рассмотрим, как они работают на примере: Напишем формулу для расчета суммы первой колонки. =СУММ (B4: B9)

Нажимаем на Ctrl+C. Чтобы перенести формулу на соседнюю клетку, переходим туда и жмем на Ctrl+V. Или можно просто протянуть ячейку с формулой, как мы описывали выше.

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

Абсолютные ссылки

Чтобы при переносе формул ссылки сохранялись неизменными, требуются абсолютные адреса. Их пишут в формате «$B$2».

Например, есть поставить знак доллара в предыдущую формулу, мы получим: =СУММ ($B$4:$B$9)

Как видите, никаких изменений не произошло.

Смешанные ссылки

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

• $А1– сохраняются столбцы;

• А$1 — сохраняются строки.

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

Трёхмерные ссылки

Это те, где указывается диапазон листов.

Формула выглядит примерно так: =СУММ (Лист1: Лист5! A6)

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

Ссылки формата R1C1

Номер здесь задается как по строкам, так и по столбцам.

Например:

• R9C9 — абсолютная ссылка на клетку, которая расположена на девятой строке девятого столбца;

• R[-2] — ссылка на строчку, расположенную выше на 2 строки;

• R[-3]C — ссылка на клетку, которая расположена на 3 ячейки выше;

• R[4]C[4] — ссылка на ячейку, которая распложена на 4 клетки правее и 4 строки ниже.

Использование имён

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

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

Как присвоить имя:

• Выделите нужную ячейку/столбец.

• Правой кнопкой мышки вызовите меню и перейдите в закладку «Присвоить имя».

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

• Сохраните, нажав Ок.

Использование функций

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

Ручной ввод

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

Панель инструментов

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

Мастер подстановки

Кликните по любой ячейке в таблице. Нажмите на иконку «Fx», после чего откроется «Вставка функций».

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

Вставка функции в формулу с помощью мастера

Рассмотрим эту опцию на примере:

• Вызовите окошко «Вставка функции», как описывалось выше.

• В перечне доступных функций выберите «Если».

Теперь составим выражение, чтобы проверить, будет ли сумма трех ячеек больше 10. При этом Правда — «Больше 10», а Ложь — «Меньше 10».

=ЕСЛИ (СУММ (B3: D3)>10;”Больше 10″;»Меньше 10″)

Программа посчитала, что сумма ячеек меньше 10 и выдала нам результат:

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

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

Редактирование функций с помощью мастера

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

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

• Специальный мастер. Нажмите на иконку «Fx» и в появившемся окошке измените нужные вам аргументы. И тут же, кстати, сможете узнать результат после редактирования.

Операции с формулами

С формулами можно совершать много операций — копировать, вставлять, перемещать. Как это делать правильно, расскажем ниже.

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

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

Отмена операций

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

Повторение действий

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

Стандартное перетаскивание

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

• Выделите фрагмент ячеек, которые нужно переместить.

• Поместите указатель мыши над одну из границ фрагмента.

• Когда указатель мыши станет крестиком с 4-мя стрелками, можете перетаскивать фрагмент в другое место.

Копирование путем перетаскивания

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

• Выделите диапазон ячеек, которые нужно скопировать.

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

• Он станет похожим на крестик +. Это говорит о том, что будет выполняться копирование, а не перетаскивание.

• Перетащите фрагмент в нужное место и отпустите мышку. Excel задаст вопрос — хотите вы заменить содержимое ячеек. Выберите «Отмена» или ОК.

Особенности вставки при перетаскивании

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

Автозаполнение формулами

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

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

Как в формуле указать постоянную ячейку

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

• Кликните на клетку, где находится формула.

• Наведите курсор в нужную вам ячейку и нажмите F4.

• В формуле аргумент с номером ячейки станет выглядеть так: $A$1 (абсолютная ссылка).

• Когда вы протяните формулу, ссылка на ячейку $A$1 останется фиксированной и не будет меняться.

Как поставить «плюс», «равно» без формулы

Когда нужно указать отрицательное значение, поставить = или написать температуру воздуха, например, +22 °С, делайте так:

• Кликаете правой кнопкой по ячейке и выбираете «Формат ячеек».

• Отмечаете «Текстовый».

Теперь можно ставить = или +, а затем нужное число.

Самые распространенные ошибки при составлении формул в редакторе Excel

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

• Слишком много вложений в выражении. Лимит 64 штуки.

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

• Неверно расставленные скобочки. В редакторе они обозначены разными цветами для удобства.

• Указывая имена книг и листов, пользователи забывают брать их в кавычки.

• Числа в неверном формате. Например, символ $ в Эксель — это не знак доллара, а формат абсолютных ссылок.

• Неправильно введенные диапазоны ячеек. Не забывайте ставить «:».

Коды ошибок при работе с формулами

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

Отличие в версиях MS Excel

Всё, что написано в этом гайде, касается более современных версий программы 2007, 2010, 2013 и 2016 года. Устаревший Эксель заметно уступает в функционале и количестве доступных инструментов. Например, функция СЦЕП появилась только в 2016 году.

Во всем остальном старые и новые версии Excel не отличаются — операции и расчеты проводятся по одинаковым алгоритмам.

Заключение

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

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