Выбрать формулу или данные excel

Применение функции ВЫБОР в Microsoft Excel

​Смотрите также​ аргументами функции СЧЕТЗ​ 2000 руб., необходимо​В ячейку Е3 введем​ чисел она попадает,​ вспомогательной таблице и​ Вашим форумом. многими​. В ячейке В7​категории​ трехмерных ссылках при​ ссылкой​​ того, можно задавать​​.​ аргументов.​ будет выводиться результат​ третьем нам предстоит​ колонке таблицы.​При работе в Excel​

​ ставим «-1». В​ ежемесячно переводить на​

Использование оператора ВЫБОР

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

​Выделяем первую пустую ячейку​

​ пользователи иногда сталкиваются​​ качестве диапазона можно​​ накопительную часть пенсии​ * Е2 –​ нужную ячейку, нужного​ наименованием (​ смогла​ Excel выделяет ячейки,​в диалоговом окне​ и удалении листов,​​Абсолютные ссылки​​ разных листов одной​​ ячейку, и она​​«Значение2»​ После этого щелкаем​ВЫБОР​ столбца​ с задачей произвести​ указывать весь столбец​ по 1000 рублей​ 20 / Е2.​ прайса.​но не понимаю как​)​ которые суммируются.​​Параметры​​ на которые такие​   . Абсолютная ссылка на ячейку​​ книги либо на​​ отобразится в строке​добавляем координаты столбца​ по уже знакомому​

​дать данной оценке​​«Наименование месяца»​​ выбор из списка​ А – Excel​​ с 41 года.​​А в ячейку Е2​Вопрос второй:​​), тогда получится что​​но сейчас вообще​Чтобы отобразить результат (95,94)​. Чтобы открыть это​ ссылки указывают. В​ в формуле, например​ ячейки из других​ формул.​​«2 торговая точка»​​ нам значку​​ соответствующую характеристику (​​. Кликаем по значку​ определенного элемента и​ быстро определяет пустые​​Функция «Подбор параметра» работает​​ поставим любое число,​

​какую формулу применить​ я выбираю наименование​ в ступоре…​

• ​ в ячейке В7,​
• ​ окно, перейдите на​
• ​ примерах используется формула​
• ​ $A$1, всегда ссылается​
• ​ книг. Ссылки на​

​Выделите пустую ячейку.​, в поле​«Вставить функцию»​

Пример 1: последовательный порядок расположения элементов

​«очень плохо»​«Вставить функцию»​ на основании его​ ячейки. В примере​ правильно, если:​​ которое находится в​​ для того что​​ — и соответственно​​мне необходимо сделать​ нажмите клавишу ВВОД.​ вкладку​​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​​ на ячейку, расположенную​ ячейки других книг​Введите знак равенства «=»,​

1. ​«Значение3»​.​​,​​около строки формул.​​ индекса присвоить ему​​ мы поставили лишь​

   

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

   

3. ​—​​Активируется окошко​​«плохо»​​Производится запуск​​ указанное значение. С​ первые 20 ячеек.​ формулой;​ Пусть это будет​ пункта из выпадающего​ расчета, остается ввести​строка №1 из​ в строке формул​.​ с A2 по​ При изменении позиции​ внешними ссылками.​ Например, чтобы получить​«3 торговая точка»​Мастера функций​

   ​,​Мастера функций​ данной задачей прекрасно​​Создаем именованный диапазон для​​все формулы написаны полностью​ 2.​ меню (№карты клиента),​ фактические ширину и​ выпадающего списка я​​ вверху окна Excel.​​К началу страницы​​ A5 на листах​​ ячейки, содержащей формулу,​​Стиль ссылок A1​​ общий объем продаж,​​, а в поле​​. На этот раз​

   ​«удовлетворительно»​. Переходим в категорию​​ справляется функция, которая​​ второго столбца. По​

   

4. ​ и без ошибок.​Запускам инструмент и заполняем​ происходило умножение на​ высоту.​ выбираю одно из​Примечания:​​Можно создать простую формулу​​ со второго по​ абсолютная ссылка не​

   

5. ​По умолчанию Excel использует​ нужно ввести «=СУММ».​«Значение4»​ перемещаемся в категорию​​,​​«Ссылки и массивы»​ так и называется​ такому же принципу.​Диаграммы позволяют нам комфортно​ поля:​ %скидки по этой​_Boroda_​ наименований (напр. монтаж​ ​ для сложения, вычитания,​ шестой.​

   

6. ​ изменяется. При копировании​ стиль ссылок A1,​Введите открывающую круглую скобку​—​«Математические»​«хорошо»​. Выбираем из перечня​«ВЫБОР»​

​Теперь поменяем ссылки на​​ воспринимать информацию. Excel​

Пример 2: произвольный порядок расположения элементов

​«Установить в ячейке» -​ карте.​​: А, все, я​​ в кв.м.),​Чтобы сложить числа в​ умножения и деления​Вставка или копирование.​ или заполнении формулы​ в котором столбцы​ «(«.​«4 торговая точка»​. Находим и выделяем​,​ наименование​. Давайте узнаем подробно,​ ряд данных в​ обладает широкими возможностями​​ Е3 (ячейка с​​Вопрос третий:​​ не заметил формулу​​строка №2 из​ столбце, выберите ячейку​ числовых значений на​​    Если вставить листы между​​ по строкам и​ обозначаются буквами (от​​Выделите диапазон ячеек, а​​.​​ наименование​​«отлично»​​«ВЫБОР»​​ как работать с​​ графике именами динамических​​ для создания диаграмм​​ формулой);​​можно ли сделать​

1. ​ для отделки.​ выпадающего списка я​​ под последним числом​​ листе. Простые формулы​ листами 2 и​ столбцам абсолютная ссылка​ A до XFD,​ затем введите закрывающую​После выполнения данных действий​​«СУММ»​​).​

   ​и щелкаем по​​ данным оператором, и​​ диапазонов. Вызываем диалоговое​ и графиков. А​​«Значение» — 25 (результат​​ так что при​тогда делаете таблицу,​

   ​ выбираю одно из​​ в столбце. Чтобы​​ всегда начинаются со​

   • ​ 6, Microsoft Excel​​ не корректируется. По​​ не более 16 384 столбцов),​​ круглую скобку «)».​
   • ​ жмем на кнопку​​. После этого щелкаем​​Выделяем первую ячейку в​​ кнопке​
   • ​ с какими поставленными​​ окно «Выбор источника​​ если добавить к​​ уравнения);​
   • ​ выборе пункта из​​ как у меня​​ наименований (напр. подоконник​​ сложить числа в​
   • ​ знака равенства (​​ прибавит к сумме​​ умолчанию в новых​​ а строки —​

   ​Нажмите клавишу ВВОД, чтобы​«OK»​ по кнопке​​ колонке​​«OK»​

   

2. ​ проблемами он может​ данных». Выделяем элемент​ диаграммам формулы, то​

   
3. ​«Изменяя значение ячейки» -​ выпадающего меню автоматически​ во вложении и​ в п.м.)​ строке, выберите первую​=​ содержимое ячеек с​ формулах используются относительные​​ номерами (от 1​​ получить результат.​.​«OK»​«Описание»​.​ справиться.​

Пример 3: использование в комбинации с другими операторами

​ легенды и нажимаем​​ тогда появляется дополнительная​​ $Е$2 (ячейка, назначенная​ заполнялись строки рядом​ пишете формулу​каждое наименованию соответствует​ ячейку справа.​​), за которым следуют​​ A2 по A5​​ ссылки, а для​​ до 1 048 576). Эти​

​Мы подготовили для вас​Но, как видим, формула​.​и переходим при​Запускается окно аргументов оператора​Скачать последнюю версию​ «Изменить». Меняем ссылки​ возможность для создания​ для аргумента х).​ информацией соответствующей данному​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВЫБОР(ВПР(A4;$BU$3:$BV$19;2;);формула_1;формула_2;формула_3;формула_4)​ своя формула расчета​Создав формулу один раз,​ константы, т. е.​ на новых листах.​ использования абсолютных ссылок​ буквы и номера​ книгу Начало работы​ выводит ошибочное значение.​​Происходит запуск окошка аргументов​​ помощи того способа,​​ВЫБОР​​ Excel​

1. ​ в поле «Значения»​ динамических отчетов и​Результат функции:​ пункту из меню.​можно засунуть все​ стоимости. затем я​​ ее можно копировать​​ числовые значения, и​

   

2. ​Удаление​​ надо активировать соответствующий​​ называются заголовками строк​ с формулами, которая​​ Это связано с​​ функции​ о котором уже​​. В поле​​Функция​ на имя диапазона.​​ презентаций.​​Найденный аргумент отобразится в​

   

3. ​еслиу нужно могу​ в имена, но,​​ ввожу исходные данные​​ в другие ячейки,​ операторы вычисления, такие​   .  Если удалить листы​ параметр. Например, при​ и столбцов. Чтобы​ доступна для скачивания.​

   ​ тем, что мы​

   ​СУММ​ шел разговор выше,​«Номер индекса»​ВЫБОР​Далее жмем «Изменить подписи​Рассмотрим, как применять формулы​ зарезервированной для него​ скинуть файл и​ думаю, что не​ ширину, высоту, цену​ а не вводить​ как плюс (​ между листами 2​​ копировании или заполнении​​ добавить ссылку на​

   ​ Если вы впервые​​ ещё не ввели​​. Данный оператор используется​ в окно аргументов​следует указать адрес​относится к категории​ горизонтальной оси». Задаем​ и условное форматирование​ ячейке.​​ подробно рассказать что​​ стоит морочить Вам​ и кол-во.​ снова и снова.​+​ и 6, Microsoft​​ абсолютной ссылки из​​ ячейку, введите букву​ пользуетесь Excel или​ номер торговой точки​ для подсчета суммы​ оператора​ первой ячейки диапазона​ операторов​ для диапазона назначенной​ в диаграммах Excel.​Решение уравнения: х =​ к чему​ голову.​как сделать так​ Например, при копировании​​), минус (​​ Excel не будет​

   

4. ​ ячейки B2 в​​ столбца, а затем —​​ даже имеете некоторый​ в соответствующую ячейку.​​ чисел в ячейках​​ВЫБОР​ нумерации месяцев. Эту​​«Ссылки и массивы»​​ имя.​Построим на основе ряда​​ 1,80.​​Заранее спасибо​

   

5. ​NyuraMalceva​​ чтобы использовалась именно​​ формулы из ячейки​​-​​ использовать их значения​ ячейку B3 она​ номер строки. Например,​ опыт работы с​Вводим номер торговой точки​ листа. Его синтаксис​.​

   ​ процедуру можно совершить,​​. Её целью является​​График остается прежним. Но​ данных простой график​​Функция «Подбор параметра» возвращает​​Читаем Правила форума,​: _Boroda_, сейчас еще​ та формула, которая​ B7 в ячейку​), звездочка (​ в вычислениях.​ остается прежней в​​ ссылка B2 указывает​​ этой программой, данный​ в предназначенной для​ довольно прост и​

   ​В поле​​ вбив координаты вручную.​​ выведение определенного значения​​ если мы добавим​​ с маркерами:​​ в качестве результата​​ исправляем все нарушения​​ раз попробую….​​ нужна. у меня​​ C7 формула в​​*​​Перемещение​​ обеих ячейках: =$A$1.​

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

   

6. ​«Номер индекса»​ Но мы поступим​ в указанную ячейку,​ в имеющуюся таблицу​Если щелкнуть по любой​ поиска первое найденное​ в новой теме!​

   
7. ​NyuraMalceva​ в расчетах используются​ ячейке C7 автоматически​) и косая черта​   . Если листы, находящиеся между​Скопированная формула с абсолютной​ на пересечении столбца B​ ознакомиться с самыми​

​ Сумма выручки по​=СУММ(число1;число2;…)​указываем ссылку на​ более удобно. Устанавливаем​ которому соответствует номер​ новые данные, они​ точке графика, то​ значение. Вне зависимости​Эта тема закрыта.​

​: _Boroda_, а как​​ 3 формулы​ настроится под новое​

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

​ тут же попадут​

lumpics.ru

Полные сведения о формулах в Excel

​ в строке формул​​ от того, сколько​«Подбор параметра» — ограниченный​ быть с выпадающим​прикрепила рабочий файл,​ расположение и подсчитает​/​ листом 6, переместить​   ​Ячейка или диапазон​ наглядным примерам вы​ же отобразится в​ оператора обычно являются​«Оценка»​ и кликаем левой​ элементе на листе.​ на диаграмму.​ появится функция РЯД.​ уравнение имеет решений.​ по функционалу вариант​ списком?​

​ но эта форма​ числа в ячейках​).​ таким образом, чтобы​

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

Создание формулы, ссылающейся на значения в других ячейках

1. ​NyuraMalceva​

2. ​ не очень удобная​

   ​ C3:C6.​​В качестве примера рассмотрим​ они оказались перед​

3. ​   . Смешанная ссылка содержит либо​Ячейка на пересечении столбца​ количество, среднее значение​

   

4. ​ котором установлена формула.​ еще чаще, ссылки​ балл.​

5. ​ соответствующей ячейке на​ следующий:​ массивом данных иногда​

   

6. ​ помощью генерируются ряды​ Е2 мы поставим​ Это часть блока​

Просмотр формулы

1. ​: _Boroda_, _Boroda_, вот​ — приходится добавлять​Кроме того, вы можете​​ простую формулу.​​ листом 2 или​

   

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

   

Ввод формулы, содержащей встроенную функцию

1. ​ на ячейки, где​

2. ​Группу полей​ листе. Как видим,​=ВЫБОР(номер_индекса;значение1;значение2;…)​ нужно создать диаграмму​ данных всех диаграмм.​

3. ​ начальное число -2,​ задач инструмента «Анализ​

4. ​ идеальный вид рабочей​ строки при возникновении​ использовать функцию «Автосумма»​

   

5. ​Выделите на листе ячейку,​ после листа 6,​

Скачивание книги «Учебник по формулам»

​ относительную строку, либо​ 10​ не хуже профессионалов.​ можно только числа​ содержаться числа, которые​«Значение»​ координаты автоматически отобразились​Аргумент​ только на основе​ Эта функция применяется​ то решение будет​ «Что-Если»».​ таблички. например заказчик​ дополнительных материалов и​ сразу для нескольких​ в которую необходимо​ Microsoft Excel вычтет​

Подробные сведения о формулах

​ абсолютную строку и​A10​Чтобы узнать больше об​ от 1 до​

Части формулы Excel

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

​ ввести формулу.​​ из суммы содержимое​

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

​«Значение1»​​ окна аргументов.​​содержит ссылку на​ значений в ряду.​

​ значений точек на​​Функция «Подбор параметра» в​​ назначение можно сформулировать​ окно без монтажа​ редактировать формулу…….​ выделить ячейки B7​

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

Использование констант в формулах Excel

​—​После этого нам предстоит​ ячейку, где находится​ Чтобы формула выбирала​ графиках. Просто использовать​ Excel применяется тогда,​ так: найти значения,​ но с подоконником​китин​ и C7, нажать​=​ листов.​ вид $A1, $B1​A10:A20​ ниже.​ точки. Если вы​ аргумента выступит не​«Очень плохо»​ вручную вбить в​ порядковый номер элемента,​ только их, при​ ее на рабочем​ когда известен результат​ которые нужно ввести​ (2 строки +​: нету файла.сбежал​

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

​ кнопку​(знак равенства), а​Перемещение конечного листа​ и т.д. Абсолютная​Диапазон ячеек: строка 15,​Формула также может содержать​ введете любое другое​ число и не​;​ группу полей​ которому следующей группой​ формировании динамического именованного​ листе невозможно.​ формулы, но начальный​ в одиночную формулу,​ итого)​Rioran​Автосумма​ затем константы и​   . Если переместить лист 2​ ссылка строки приобретает​ столбцы B-E​ один или несколько​

• ​ число, то формула​

  ​ ссылка, а содержимое​«Значение2»​«Значение»​ операторов присваивается определенное​ диапазона прописываем следующее:​Аргументы функции РЯД:​ параметр для получения​ чтобы получить желаемый​то есть открыла​:​и суммировать два​ операторы (не более​ или 6 в​ вид A$1, B$1​B15:E15​ таких элементов, как​ опять выдаст ошибку.​ функции​—​наименование месяцев. Причем​

  ​ значение. Этот порядковый​	​ =СМЕЩ(Лист1!$A$1;СЧЁТЗ(Лист1!$A$1:$A$1000)-40;0;40;1). По такому​
  ​Имя (название ряда данных,​ результата неизвестен. Чтобы​ (известный) результат.​	​ рабочий файл, выбрала​
  ​NyuraMalceva​ столбца одновременно.​	​ 8192 знаков), которые​
  ​ другое место книги,​ и т.д. При​	​Все ячейки в строке​
  ​функции​Урок:​	​ВЫБОР​
  ​«Плохо»​ каждому полю должен​ номер может варьироваться​	​ же принципу –​
  ​ отображается в легенде;​ не подбирать входные​	​Известен результат некой формулы.​
  ​ два наименования -​, здравствуйте.​Скопируйте данные из таблицы​	​ нужно использовать при​
  ​ Microsoft Excel скорректирует​ изменении позиции ячейки,​	​ 5​

• ​,​Как посчитать сумму в​.​;​ соответствовать отдельный месяц,​

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

  

  ​Вы можете использовать​ ниже и вставьте​

  ​ вычислении.​ сумму с учетом​ содержащей формулу, относительная​

  ​5:5​ссылки​ Экселе​Устанавливаем курсор в поле​

  ​«Значение3»​​ то есть, в​1​Сколько бы данных мы​Подписи категорий (метки, появляющиеся​ команда.​ данные. Кроме одного.​сама таблица пустая,​

• ​ могущество функции ЕСЛИ().​ их в ячейку​

  1. ​В нашем примере введите​​ изменения диапазона листов.​ ссылка изменяется, а​Все ячейки в строках​,​Как видим, функция​«Число1»​—​ поле​до​ ни добавляли в​ на оси категорий;​Пример 1. Метод подбора​ Неизвестное входное значение​ а формулы то,​ В данном случае​ A1 нового листа​=1+1​Удаление конечного листа​ абсолютная ссылка не​ с 5 по​операторы​ВЫБОР​. Затем кликаем по​

     ​«Удовлетворительно»​«Значение1»​​254​

     

  2. ​ исходную таблицу, на​​ не обязательный аргумент);​ начальной суммы инвестиций​ мы и будем​ откуда то должны​ нужно будет вложить​ Excel. При необходимости​.​   . Если удалить лист 2​ изменяется. При копировании​ 10​и​при правильном её​ пиктограмме, которая изображена​;​записываем​. Если задать индекс,​ графике будет показано​Значения (которые применяются для​ (вклада).​ искать. Рассмотрим функцию​ браться…. я нашла​ одно ЕСЛИ() в​ измените ширину столбцов,​Примечания:​ или 6, Microsoft​

     ​ или заполнении формулы​5:10​​константы​

     

  3. ​ применении, может стать​​ в виде перевернутого​«Значение4»​«Январь»​ превышающий данное число,​ только последние 40​ построения графика; обязательный​Известные параметры:​ «Подбора параметров» в​ вот такой файлик​ другое, например:​ чтобы видеть все​ ​ Excel скорректирует сумму​ вдоль строк и​Все ячейки в столбце​.​ очень хорошим помощником​ треугольника. Этот значок​—​, в поле​ то оператор выведет​ значений.​ параметр);​срок – 10 лет;​ Excel на примере.​ (цифры прописью) тут​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(«Условия 1″;»Формула 1»;ЕСЛИ(«Условия 2»;»Формула​ данные.​

     ​Вместо ввода констант в​ с учетом изменения​​ вдоль столбцов относительная​

     

• ​ H​

  ​Части формулы​ для выполнения поставленных​​ находится в том​«Хорошо»​«Значение2»​ в ячейку ошибку.​Данный инструмент достаточно просто​Порядок (порядок значений в​доходность – 10%;​Необходимо подобрать процентную ставку​ куча вспомогательных таблиц,​ 2″;»Формула 3″))​Примечание:​ формуле можно выбрать​ диапазона листов.​ ссылка автоматически корректируется,​H:H​   ​ задач. При использовании​ же горизонтальном ряду,​;​—​ Если в качестве​ используется для обычных​

  • ​ ряду данных; обязательный​коэффициент наращения – расчетная​ по займу, если​ (понятное дело мне​NyuraMalceva​ Чтобы эти формулы выводили​ ячейки с нужными​Стиль ссылок R1C1​ а абсолютная ссылка​Все ячейки в столбцах​1.​ её в комбинации​ где расположены кнопка​

  • ​«Значение5»​«Февраль»​

  • ​ данного аргумента ввести​ данных. Для диаграмм​ параметр).​ величина;​ известна сумма и​ не разобраться как​

  ​: новый файл​ результат, выделите их​ значениями и ввести​​Можно использовать такой стиль​ не корректируется. Например,​ с H по​Функции​ с другими операторами​«Вставить функцию»​—​и т. д.​ дробное значение, то​ в Excel применить​Аргументы функции РЯД можно​сумма выплат в конце​ срок. Заполняем таблицу​ это получается), может​

  • ​NyuraMalceva​​ и нажмите клавишу​ операторы между ними.​ ссылок, при котором​ при копировании или​ J​. Функция ПИ() возвращает​ возможности существенно увеличиваются.​

  • ​и строка формул,​​«Отлично»​После выполнения указанной задачи​ функция воспримет его,​ встроенное условное форматирование​ найти и изменить​ срока – желаемая​

  • ​ входными данными.​​ принцип работы моей​: значит мне надо​ F2, а затем —​В соответствии со стандартным​ нумеруются и строки,​ заполнении смешанной ссылки​H:J​ значение числа пи:​Автор: Максим Тютюшев​ но слева от​.​

  • ​ щелкаем по кнопке​​ как ближайшее к​ невозможно. Нужно идти​ в диалоговом окне​ цифра (500 000​Процентная ставка неизвестна, поэтому​ таблицы должен быть​

  • ​ составить следующие вспомогательные​​ВВОД​ порядком математических операций,​ и столбцы. Стиль​ из ячейки A2​Диапазон ячеек: столбцы А-E,​

• ​ 3,142…​

  ​Примечание:​ них. Открывается список​После того, как введение​«OK»​ данному числу меньшее​ другим путем.​ «Выбрать данные»:​ рублей).​ ячейка пустая. Для​ приблизительно таким же​ таблицы:​(Windows) или​ умножение и деление​ ссылок R1C1 удобен​ в ячейку B3​ строки 10-20​

  ​2.​	​ Мы стараемся как можно​
  ​ недавно используемых функций.​	​ вышеуказанных данных произведено,​внизу окна.​ целое значение. Если​Зачем это? Для улучшения​
  ​Выделим элемент легенды «y»​	​Внесем входные данные в​ расчета ежемесячных платежей​NyuraMalceva​для выпл. списка​Return​
  ​ выполняются до сложения​	​ для вычисления положения​ она изменяется с​A10:E20​
  ​Ссылки​	​ оперативнее обеспечивать вас​ Так как формула​ щелкаем по кнопке​
  ​Как видим, сразу в​	​ задать​ восприятия информации. При​

  ​ и щелкнем по​ таблицу:​ используем функцию ПЛТ.​: _Boroda_, я попробовала​ наименование работ​(Mac).​​ и вычитания.​​ столбцов и строк​ =A$1 на =B$1.​Создание ссылки на ячейку​. A2 возвращает значение​ актуальными справочными материалами​ВЫБОР​«OK»​

  ​ той ячейке, которую​«Номер индекса»​ изменении значений в​ кнопке изменить. В​​Начальные инвестиции – искомая​​Когда условия задачи записаны,​​ вставить формулы …но​​для условия -​​Данные​​Нажмите клавишу​​ в макросах. При​​Скопированная формула со смешанной​ или диапазон ячеек​ ячейки A2.​​ на вашем языке.​​недавно применялась нами​

  ​.​

support.office.com

Создание простой формулы в Excel

​ мы отметили ещё​, для которого нет​ исходных ячейках автоматически​ поле «Имя ряда»​ величина. В ячейке​ переходим на вкладку​ наверно неправильно…​​ величины(высота ширина кол-во​​2​ВВОД​ использовании стиля R1C1​ ссылкой​ с другого листа​​3.​​ Эта страница переведена​​ в предыдущем способе,​​Значение балла для первого​​ в первом действии,​​ соответствующего аргумента​ будет меняться цветовое​​ содержится аргумент функции​​ В4 (коэффициент наращения)​

​ «Данные». «Работа с​NyuraMalceva​

1. ​ цена) и формулы……..​5​(Windows) или​

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

   ​«Значение»​​ исполнение диаграммы.​​ «Имя»:​

   ​ – формула =(1+B3)^B2.​​ данными» — «Анализ​

   • ​: _Boroda_, проверьте пожалуйста!!!!!!​я не понимаю​Формула​Return​ положение ячейки обозначается​

   • ​Стиль трехмерных ссылок​ книге​. Числа или текстовые​ текст может содержать​ в этом списке.​

3. ​ ячейку.​​ именно наименование​​, то оператор будет​​Выполнить условное форматирование в​​Название ряда данных –​

​Вызываем окно команды «Подбор​ «Что-Если»» — «Подбор​​ у меня получилось​​ как их связать​Описание​​(Mac).​​ буквой R, за​​Удобный способ для ссылки​​В приведенном ниже примере​ значения, введенные непосредственно​ неточности и грамматические​ Поэтому достаточно кликнуть​

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

​Для того, чтобы произвести​«Январь»​ возвращать в ячейку​ диаграммах можно с​ «y». Его можно​ параметра». Заполняем поля:​ параметра».​​ или нет??​​ со старой таблицей……​​Результат​​Рассмотрим другой вариант простой​ которой следует номер​​ на несколько листов​​ функция СРЗНАЧ вычисляет​​ в формулу, например​​ ошибки. Для нас​

​ по данному наименованию,​​ подобную процедуру и​​, соответствующее первому номеру​ ошибку.​ помощью макросов и​ менять.​

​После выполнения команды Excel​​В поле «Установить в​NyuraMalceva​и как в​​=A2+A3​ формулы. Введите​ строки, и буквой​

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

​: я сломала себе​ этом случае РОДИТЬ​Сумма значений в ячейках​=5+2*3​ C, за которой​ анализа данных из​

​ диапазоне B1:B10 на​​4.​

• ​ статья была вам​ окно аргументов. Но​ столбца, копируем данные​Теперь, чтобы не вводить​«Значение»​ способ.​ аргумент значений ряда​

• ​Чтобы через 10 лет​ на ячейку с​ голову!!! но кажется​ формулу…..​ A1 и A2​в другой ячейке​ следует номер столбца.​ одной и той​ листе «Маркетинг» в​Операторы​ полезна. Просим вас​ более вероятно, что​ в его ячейки​ вручную формулу для​

• ​. Она может достигать​На основании тех же​ данных.​ получить 500 000​ расчетной формулой (B4).​ все получилось!!!!!!​ArkaIIIa​​=A2+A3​​ и нажмите клавишу​Ссылка​

Примеры

​ же ячейки или​ той же книге.​. Оператор ^ (крышка)​ уделить пару секунд​ у вас в​ при помощи маркера​ всех остальных ячеек​ количества​

​ исходных данных составим​​Подписи горизонтальной оси –​ рублей при 10%​ Поле «Значение» предназначено​_Boroda_, ОГРОМНОЕ СПАСИБО​​:​​=A2-A3​​ВВОД​​Значение​

support.office.com

выбор формулы в зависимости от значения выпадающего списка (Формулы/Formulas)

​ содержит ссылку на​​ ячеек от B1​
​ для умножения.​ внизу страницы. Для​ кликнуть по позиции​. Как видим, и​​ скопировать. Для этого​
​«Значение1»​ следующим образом: отдельные​
​ построен на основе​ прибавку к пенсии​
​ сумма ежемесячных платежей.​ радовалась! в openoffice​ значения для этих​=A2/A3​ последних числа и​
​ строки выше в​ ячейку или диапазон,​ до B10​Константа представляет собой готовое​ удобства также приводим​
​«Другие функции…»​ на этот раз​ производим установку курсора​. В данной группе​ столбики должны закрашиваться​ одного ряда данных,​
​ по старости за​ Допустим, -5 000​ работает не корректно!​ выпадающих списков и​Частное от деления значений​ добавит первое число​
​ том же столбце​ перед которой указываются​3. Восклицательный знак (!)​ (не вычисляемое) значение,​ ссылку на оригинал​.​ функция отработала корректно​ в правый нижний​

​ аргументов указываются те​​ в определенный цвет​

​ то порядок равняется​​ счет участия в​​ (чтобы формула работала​​Продолжение следует…​
​ таблицы?​ в ячейках A1​ к результату умножения.​R[2]C[2]​ имена листов. В​ отделяет ссылку на​
​ которое всегда остается​ (на английском языке).​

​Производится запуск​​ и вывела все​

​ угол ячейки, содержащей​​ значения, которым будет​ в зависимости от​ единице. Данный аргумент​
​ государственной программе софинансирования.​ правильно, ставим знак​
​malish606​NyuraMalceva​ и A2​
​Для быстрого суммирования чисел​Относительная ссылка на ячейку,​ Microsoft Excel используются​​ лист от ссылки​ неизменным. Например, дата​Начните создавать формулы и​

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

​: Доброго дня!​​: добавила, снизу старой​=A2/A3​
​ в столбце или​ расположенную на две​ все листы, указанные​ на диапазон ячеек.​ 09.10.2008, число 210​ использовать встроенные функции,​, в котором в​ с заданным алгоритмом.​ заполнения. Зажимаем левую​ предыдущего аргумента. То​

​Для условного форматирования требуется​​ «Элементы легенды».​ежемесячные отчисления – 1000​​ деньги будут отдаваться).​
​Помогите разобраться с​
​ таблицы.​
​=A2*A3​
​ строке можно использовать​
​ строки ниже и​
​ между начальным и​

​Примечание:​​ и текст «Прибыль​ чтобы выполнять расчеты​
​ разделе​Но гораздо продуктивнее оператор​ кнопку мыши и​
​ есть, если в​ формула, которая определяет​Аргументы функции РЯД допускают​
​ руб.;​ В поле «Изменяя​
​ написанием формулы с​если ранее я​Произведение значений в ячейках​ кнопку «Автосумма». Выберите​ на два столбца​ конечным именами в​​ Если название упоминаемого листа​​ за квартал» являются​ и решать задачи.​«Ссылки и массивы»​ВЫБОР​ тянем маркер заполнения​ качестве аргумента​ отформатированные ячейки.​

​ применение именованных диапазонов.​​период уплаты дополнительных страховых​ значение ячейки» -​ двумя выпадающими окнами​
​ сама редактировала содержимое​ A1 и A2​ ячейку рядом с​ правее​
​ ссылке. Например, формула​
​ содержит пробелы или​ константами. Выражение или​Важно:​мы должны отыскать​можно использовать в​

​ вниз до конца​​«Номер индекса»​Для каждого условия создадим​

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

​ ячейки (наименование), то​​=A2*A3​ числами, которые необходимо​R2C2​ =СУММ(Лист2:Лист13!B5) суммирует все​ цифры, его нужно​ его значение константами​ Вычисляемые результаты формул и​ наименование​
​ комбинации с другими​ колонки.​выступает число​ отдельный ряд данных.​
​ возможностью, то можно​ величина (пенсионный возраст​ ячейку с искомым​т.е. если я​ сейчас я могу​=A2^A3​ сложить, нажмите кнопку​Абсолютная ссылка на ячейку,​ значения, содержащиеся в​ заключить в апострофы​ не являются. Если​ некоторые функции листа​«ВЫБОР»​

​ функциями. Посмотрим, как​​Как видим, формула скопировалась​«3»​ Значения в исходной​

​ создать динамическую диаграмму,​​ (в примере –​ параметром ($B$3).​ выбираю из выпадающего​

​ его выбрать из​​Значение в ячейке A1​Автосумма​ расположенную во второй​
​ ячейке B5 на​ (‘), например так:​

​ формула в ячейке​​ Excel могут несколько​и выделить его.​ это делается на​
​ в нужный нам​

​, то ему будет​​ таблице находятся в​
​ быстро переключаться между​ для мужчины) минус​После нажатия ОК на​ списка первое значение​
​ списка, но мне​ в степени, указанной​на вкладке​ строке второго столбца​ всех листах в​ ‘123’!A1 или =’Прибыль​ содержит константы, а​ отличаться на компьютерах​ Щелкаем по кнопке​ примере применения операторов​ диапазон. При этом​ соответствовать значение, которое​ диапазоне от 0,06​ данными одного ряда.​ возраст участника программы​ экране появится окно​ то оно ведет​ необходимо чтобы формула​ в ячейке A2​Главная​R[-1]​ диапазоне от Лист2​ за январь’!A1.​ не ссылки на​ под управлением Windows​«OK»​ВЫБОР​ все наименования месяцев,​ внесено, как аргумент​ до 5,7. Создадим​Присмотримся поближе к применению​ на момент вступления);​ результата.​ на первый лист​ в ячейке «сумма»​
​=A2^A3​
​, а затем нажмите​Относительная ссылка на строку,​ до Лист13 включительно.​Различия между абсолютными, относительными​ другие ячейки (например,​ с архитектурой x86​.​и​
​ которые отобразились в​
​«Значение3»​ ряд для периодов​ именованных динамических диапазонов​пенсионные накопления – расчетная​Чтобы сохранить, нажимаем ОК​ (прайс лист1), если​ менялась в соответствии​
​Формула​ клавишу​ расположенную выше текущей​При помощи трехмерных ссылок​
​ и смешанными ссылками​
​ имеет вид =30+70+110),​ или x86-64 и​Активируется окошко аргументов оператора​
​СУММ​

excelworld.ru

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

​ ячейках, соответствуют их​.​ 0-0,6; 0,6-1,6; 1,6-3;​ при построении диаграмм.​ величина (накопленная за​ или ВВОД.​

​ на второе значение,​ с выбранным наименованием…​Описание​ВВОД​ ячейки​ можно создавать ссылки​Относительные ссылки​

Где находится «Подбор параметра» в Excel

​ значение в такой​ компьютерах под управлением​ВЫБОР​.​ порядковому номеру из​В качестве значений могут​ 3-4,6; 4,6-6.​​

​ период участником сумма,​Функция «Подбор параметра» изменяет​ то формулу ведет​_Boroda_​Результат​

​(Windows) или​R​ на ячейки на​   . Относительная ссылка в формуле,​

​ ячейке изменяется только​ Windows RT с​. В поле​Имеется таблица реализации продукции​ столбца слева.​ выступать разнообразные виды​

​Сформируем данные для гистограммы​Для имеющейся исходной таблицы​ увеличенная государством в​ значение в ячейке​ на второй лист​: Во, теперь понятно.​=5+2​Return​Абсолютная ссылка на текущую​ других листах, определять​ например A1, основана​ после редактирования формулы.​ архитектурой ARM. Подробнее​«Номер индекса»​ по торговым точкам.​Урок:​ данных:​ с условным форматированием.​ с данными создадим​

​ 2 раза);​ В3 до тех​ (прайс2), затем во​

​ Попробуйте так​Сумма чисел 5 и​

​(Mac).​ строку​ имена и создавать​ на относительной позиции​ Обычно лучше помещать​ об этих различиях.​указываем ссылку на​ Она разбита на​Мастер функций в Экселе​

​Ссылки;​

Решение уравнений методом «Подбора параметров» в Excel

​ Диапазон условий внесем​ именованные диапазоны: для​ожидаемый период выплаты трудовой​ пор, пока не​ втором выпадающем окне​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=AC4*AE4*ЕСЛИ(U4;U4/1000;1)*ЕСЛИ(Y4;Y4/1000;1)​ 2​Когда вы нажимаете кнопку​При записи макроса в​ формулы с использованием​ ячейки, содержащей формулу,​ такие константы в​Выделите ячейку.​ ту ячейку листа,​

​ четыре столбца, каждый​В предыдущем случае мы​Числа;​ в строки 1​

​ первого столбца –​ пенсии – 228​ получит заданный пользователем​ если выбираю первый​или просто тупо​=5+2​

​Автосумма​ Microsoft Excel для​

​ следующих функций: СУММ,​ и ячейки, на​ отдельные ячейки, где​

​Введите знак равенства «=».​ в которую будем​

​ из которых соответствует​ применили формулу​Текст;​

​ и 2. Заголовки​

​ категорий – «х»;​ мес.;​ результат формулы, записанной​

​ пункт, то формулу​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВЫБОР(ПОИСКПОЗ(Q4;{«кв.м.»:»п.м.»:»шт.»};);U4*Y4*(AC4*AE4)/1000000;Y4*AC4*AE4/1000;AC4*AE4)​

​=5-2​, Excel автоматически вводит​ некоторых команд используется​ СРЗНАЧ, СРЗНАЧА, СЧЁТ,​ которую указывает ссылка.​ их можно будет​

​Примечание:​ вводить номер торговой​ определенной торговой точке.​ВЫБОР​Формулы;​

Примеры подбора параметра в Excel

​ – в строку​ для второго –​желаемая прибавка к пенсии​ в ячейке В4.​ ведет на первую​или​Разность чисел 5 и​ формулу для суммирования​ стиль ссылок R1C1.​

​ СЧЁТЗ, МАКС, МАКСА,​ При изменении позиции​ легко изменить при​

​ Формулы в Excel начинаются​

• ​ точки для последующего​
• ​ Выручка указана отдельно​
• ​, когда все значения​Функции и т. д.​
• ​ 3. Формулы для​ точек данных –​ – 2000 руб.​ Команда выдает только​

​ строку (певрого или​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ВЫБОР(ПОИСКПОЗ(Q4;{«кв.м.»:»п.м.»:»шт.»};);U4*Y4/1000000;Y4/1000;1)*AC4*AE4​

​ 2​ чисел (в которой​ Например, если записывается​ МИН, МИНА, ПРОИЗВЕД,​

​ ячейки, содержащей формулу,​ необходимости, а в​

​ со знака равенства.​ отображения общей суммы​

​ за определенную дату​ номеров индекса были​Теперь давайте рассмотрим конкретные​ заголовков:​ «у».​

​С какого возраста необходимо​ одно решение задачи.​ второго прайса, в​или​=5-2​

​ используется функция СУММ).​

• ​ команда щелчка элемента​ СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА,​
• ​ изменяется и ссылка.​ формулах использовать ссылки​Выберите ячейку или введите​ выручки по ней.​ построчно. Наша задача​ расставлены по порядку.​ примеры применения данного​
• ​Заполним колонки для диаграммы​Открываем вкладку «Формулы» -​ уплачивать по 1000​​ зависимости от выбранного​
• ​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(Q4=»кв.м.»;U4*Y4/1000000;ЕСЛИ(Q4=»п.м.»;Y4/1000;1))*AC4*AE4​=5/2​Примечание:​
• ​Автосумма​ СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,​

​ При копировании или​ на эти ячейки.​ ее адрес в​В поле​ — сделать так,​ Но как работает​ оператора.​

1. ​ с условным форматированием.​ нажимаем кнопку «Диспетчер​ рублей в качестве​Функция «Подбор параметра» идеально​ условия в первом​NyuraMalceva​
2. ​Частное от деления 5​ Также в ячейке можно​

​для вставки формулы,​ ДИСПА и ДИСППА.​ заполнении формулы вдоль​Ссылка указывает на ячейку​ выделенной.​«Значение1»​

​ чтобы после ввода​ данный оператор в​

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

exceltable.com

Как использовать формулы в диаграммах Excel: примеры

​ подходит для решения​ выпадающем окне), если​: каждое наименование считается​ на 2​ ввести ALT+= (Windows)​ суммирующей диапазон ячеек,​Трехмерные ссылки нельзя использовать​ строк и вдоль​ или диапазон ячеек​Введите оператор. Например, для​нужно вписать координаты​

​ номера торговой точки​ случае, если указанные​ данная функция на​

Примеры формул в диаграммах

​ будет отображать значения,​В диалоговом окне жмем​ чтобы получить прибавку​

​ уравнений с одним​ выбираю второй пункт,​ по отдельной формуле​=5/2​ или ALT+​ в Microsoft Excel​ в формулах массива.​ столбцов ссылка автоматически​ листа и сообщает​ вычитания введите знак​ столбца​ в определенную ячейку​ значения перемешаны и​

​ простейшем примере. У​

1. ​ находящиеся в диапазонах​ «Создать». Откроется окно​ к пенсии в​
2. ​ неизвестным. Возьмем для​ то формулу ведет​монтаж: ширина *​
3. ​=5*2​+= (Mac), и​ при записи формулы​
4. ​Трехмерные ссылки нельзя использовать​ корректируется. По умолчанию​ Microsoft Excel, где​

​ «минус».​«1 торговая точка»​ листа отображалась сумма​ повторяются? Давайте рассмотрим​

​ нас имеется таблица​ заголовков.​ «Создание имени». В​ 2000 рублей:​ примера выражение: 20​ на вторую строку​

​ высота * кол-во​Произведение чисел 5 и​ Excel автоматически вставит​

​ будет использован стиль​ вместе с оператор​ в новых формулах​

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

​ выручки за все​ это на примере​ с нумерацией от​Источник данных для гистограммы​ поле «Имя» вводим​Ячейка с формулой расчета​ * х –​

​ (первого или второго​ * цена​ 2​ функцию СУММ.​ ссылок R1C1, а​ пересечения (один пробел),​ используются относительные ссылки.​

​ значения или данные.​ введите ее адрес​ просто. Устанавливаем курсор​

​ дни работы указанного​

Создание динамических диаграмм

​ таблицы с успеваемостью​1​ – столбцы А​ имя диапазона. В​ прибавки к пенсии​ 20 / х​ прайса, в зависимости​отделка: ширина +​

​=5*2​Пример: чтобы сложить числа​ не A1.​

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

​ поле «Диапазон» -​ активна – вызываем​ = 25. Аргумент​ от выбранного условия​ (высота*2) * кол-во​=5^2​ за январь в​Чтобы включить или отключить​ формулах с неявное​ или заполнении относительной​ можно использовать в​Нажмите клавишу ВВОД. В​

​ Затем, зажав левую​ мы и будем​ столбце таблицы указана​

​12​ исключить колонку В​ формулу для ссылки​ команду «Подбор параметра».​ х – искомый​ в первом выпадающем​ * цена​Число 5 во второй​ бюджете «Развлечения», выберите​ использование стиля ссылок​

​ пересечение.​ ссылки из ячейки​ одной формуле данные,​ ячейке с формулой​

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

​ Заполняем поля в​ параметр. Пусть функция​ окне), затем в​подоконник: длина *​ степени​ ячейку B7, которая​ R1C1, установите или​Что происходит при перемещении,​ B2 в ячейку​ находящиеся в разных​ отобразится результат вычисления.​ весь диапазон ячеек​СУММ​

​ втором оценка (от​ порядковым номерам с​ созданный диапазон С:F.​ первом столбце (=СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A$1:$A$20)-1;1)).​ открывшемся меню.​ поможет решить уравнение​

Условное форматирование в диаграмме

​ поле площади ввожу​ кол-во * цена​=5^2​ находится прямо под​ снимите флажок​ копировании, вставке или​ B3 она автоматически​

​ частях листа, а​При вводе в ячейку​ столбца​и​1​ помощью функции​

​Теперь столбики диаграммы окрашены​Чтобы заголовок ряда данных​Нажимаем ОК – получаем​ подбором параметра и​ цифру и в​

​я так понимаю​NyuraMalceva​ столбцом с числами.​

​Стиль ссылок R1C1​ удалении листов​ изменяется с =A1​ также использовать значение​ формула также отображается​«1 торговая точка»​ВЫБОР​

​до​ВЫБОР​ в разные цвета​

​ не включался в​ результат подбора.​ отобразит найденное значение​ зависимости от того​ мне надо прописать​: Добрый день!!!​ Затем нажмите кнопку​в разделе​   . Нижеследующие примеры поясняют, какие​

​ на =A2.​ одной ячейки в​ в​. Адрес тут же​.​5​указать наименование соответствующего​ в зависимости от​

​ именованный диапазон, за​Чтобы получить прибавку в​ в ячейке Е2.​ в какой диапазон​ эти формулы во​Давно наблюдаю за​

​Автосумма​Работа с формулами​ изменения происходят в​Скопированная формула с относительной​ нескольких формулах. Кроме​строке формул​

​ отобразится в окне​Выделяем ячейку, в которой​баллов), а в​ месяца во второй​

exceltable.com

​ значения.​

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

1. Не добавляйте последние скобки в функции

Давайте начнем с чего-то по-настоящему простого! Когда вы пишите формулу, содержащую только одну функцию (СУММ, СРЗНАЧ и т.д.), вам не обязательно вводить заключительные закрывающие скобки. Например, вы можете просто ввести:

= СУММ(A1:A10

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

Примечание: это не сработает, если ваша формула содержит более одного набора скобок.

2. При перемещении формулы не допускайте изменения ссылок

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

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

Способ 1: Если вы просто перемещаете формулу в соседнее место, попробуйте перетаскивание. Перетаскивание сохранит все адреса в целости и сохранности.

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

Горячие клавиши:

Windows: Ctrl + X, Ctrl + V

Mac: Cmd + X, Cmd + V

3. Не допускайте изменения ссылок при копировании формулы

Примечание: можно сделать все ссылки на ячейки абсолютными. Предположим, что этот способ вам не подходит.

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

Способ 2: Чтобы скопировать группу формул в новое место, не затрагивая ссылки, вы можете использовать поиск и замену.

1. Выберите формулы, которые вы хотите скопировать
2. Затем найдите и замените знак равенства (=) в формулах символом хеша (#). Это преобразует формулы в текст.
3. Теперь скопируйте и вставьте формулы в новое место.
4. После этого проделайте обратную операцию. Найдите хэш (#) и замените на знак равенства (=). Это восстановит формулы в рабочее состояние.

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

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

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

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

Вы также можете заполнить формулы на листе, используя сочетание клавиш (Ctrl + D). При обработке большой группы ячеек, быстрее будет воспользоваться маркером заполнения.

5. Используйте таблицу для автоматического ввода формул

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

Как только вы преобразуете свои данные в таблицу (Ctrl + T), все формулы, введенные в первой строке, будут автоматически скопированы по всей длине таблицы. Это экономит много времени, а также помогает предотвратить ошибки.

Когда вы обновляете формулу в таблице, Excel снова обновляет все аналогичные формулы в том же столбце.

Примечание: формулы в таблице будут автоматически использовать структурированные ссылки (т.е. в приведенном выше примере = [@ Кол-во] * [@ Цена]

6. Используйте автозаполнение при вводе функций

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

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

7. Используйте Ctrl + клик для ввода аргументов

Не любите вводить точку с запятой между аргументами? Excel может сделать это за вас. Когда вы вводите аргументы в функцию, просто удерживайте нажатой клавишу «Ctrl» (Mac: Command) при нажатии на каждую ссылку, и Excel автоматически введет для вас разделители.

Например, вы можете ввести формулу: = СУММ(A1; B10; C5:C10), введя «= СУММ(», затем щелкнув по каждой ссылке, удерживая нажатой клавишу «Ctrl». Это работает с любой функцией, в которой можно использовать ссылки в качестве аргументов.

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

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

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

9. Вставьте заполнители аргументов функции с помощью горячих клавиш

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

Когда вы вводите функцию, после того, как Excel распознал имя функции, введите Ctrl + Shift + A (обе платформы).

Например, если вы наберете «=ДАТА (» и затем используете Ctrl + Shift + A, Excel выдаст вам «= DATE (год, месяц, день)». Затем вы можете дважды щелкнуть каждый аргумент (или воспользоваться подсказкой функции окно для выбора каждого аргумента) и измените его на значение, которое вы хотите.

10. Уберите мешающую подсказку с обзора

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

11. Включить отображение всех формул одновременно

Каждый раз, когда вы редактируете ячейку, содержащую формулу, Excel автоматически отображает формулу вместо ее результата. Но иногда нужно увидеть все формулы на рабочем листе одновременно. Для этого просто используйте сочетание клавиш для отображения формул: Ctrl + ~ (это тильда). С помощью этого трюка вы можете быстро включать и выключать отображение всех формул на листе. Это хороший способ проверить формулы на согласованность, увидев все формулы одновременно.

12. Выберите все формулы на рабочем листе одновременно

Еще один способ увидеть все формулы на рабочем листе — выбрать их. Вы можете сделать это, используя более мощные (и скрытые) функции в Excel: это окно Переход (Ctrl + G). С помощью этой команды вы можете выбрать все виды аргументов в Excel, включая пустые ячейки, ячейки с числами и многое другое. Один из вариантов — это ячейки, содержащие формулы.

Чтобы выбрать все ячейки, содержащие формулы на листе, просто нажмите Ctrl + G, чтобы открыть диалоговое окно «Переход», затем нажмите кнопку «Выделить» и выберите «Формулы». Когда вы нажмете ОК, будут выбраны все ячейки, которые содержат формулы.

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

13. Используйте специальную вставку для преобразования формул в значения.

Распространенной проблемой в Excel является необходимость остановить обновление рассчитанных значений. Например, вы хотите упростить рабочую таблицу, удалив вспомогательные столбцы, которые вы использовали для генерации определенных значений. Но если вы удалите эти столбцы с формулами, все еще ссылающимися на них, вы получите массу ошибок #ЗНАЧ. Решение состоит в том, чтобы сначала преобразовать формулы в значения, а затем удалить дополнительные столбцы. Самый простой способ сделать это — использовать Специальную вставку. Сначала выберите формулы, которые вы хотите преобразовать, и скопируйте в буфер обмена. Затем, когда формулы еще выбраны, откройте диалоговое окно «Специальная вставка» (Win: Ctrl + Alt + V, Mac: Ctrl + Cmd + V) и используйте параметр «Значения». Это заменит все формулы, которые вы выбрали, на значения, которые они рассчитали.

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

Другой распространенной проблемой в Excel является необходимость изменения большого количества значений одновременно. Например, возможно, у вас есть список из 500 цен на товары, и вам нужно увеличить все цены на 5%. Или, может быть, у вас есть список из 100 дат, которые нужно перенести на одну неделю? В таких случаях вы можете добавить столбец «Помощник» в таблицу, выполнить необходимые вычисления, преобразовать результаты в значения, а затем скопировать их в исходный столбец. Но если вам нужен только простой расчет, Специальная вставка поможет намного проще и быстрее, потому что вы можете изменить значение напрямую без каких-либо дополнительных формул.

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

• добавьте число 7 к любой ячейке на рабочем листе
• скопируйте его в буфер обмена
• выберите все даты, которые вы хотите изменить
• используйте Специальная вставка> Операция> Сложить
• нажмите ОК

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

Чтобы увеличить цены на 5%, используйте тот же подход. Введите «1.05» в ячейку и скопируйте в буфер обмена. Затем выберите цены, которые вы хотите изменить, и используйте Специальная вставка> Операция> Умножить, чтобы преобразовать все цены сразу. Как только вы освоите этот совет, вы найдете много способов его применения.

Примечание: этот способ работает только со значениями. Не пытайтесь делать это с формулами!

15. Используйте именованные диапазоны, чтобы сделать формулы более читабельными

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

= B3 * $A$1
= B4 * $A$1
= B5 * $A$1

Но если вы назовете ячейку A1 «почасовая_ставка», ваши формулы будут выглядеть так:

= B3 * почасовая_ставка
= B4 * почасовая_ставка
= B5 * почасовая_ставка

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

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

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

16. Применять имена к существующим формулам автоматически

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

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

17. Сохраните формулу, которая еще не дописана

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

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

18. Будьте в курсе функций, которые предлагает Excel

Функции существуют для решения конкретных проблем. Вы можете думать о функции как о готовой формуле с определенным названием, целью и возвращаемым значением. Например, функция ПРОПНАЧ (PROPER) имеет только одну цель: она преобразует первую букву каждого слова в прописную. Дайте ей текст типа «ПОЕЗД чита — ЧЕЛябинск», и она вернет вам «Поезд Чита — Челябинск». Функции невероятно удобны, когда они решают вашу проблему, поэтому имеет смысл ознакомиться с функциями, доступными в Excel.

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

19. Используйте клавишу F4 для переключения относительных и абсолютных ссылок

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

Однако преобразование между относительными и абсолютными ссылками может создавать неудобства — ввод всех этих знаков доллара ($) утомителен и подвержен ошибкам. К счастью, есть отличная горячая клавиша, которая позволяет быстро переключаться между 4 вариантами, доступными для каждой ссылки: (Windows: F4, Mac: Command + T).

Просто поместите курсор в ссылку, используя клавишу. Каждый раз, когда вы нажимаете ее, Excel будет «переходить» к следующему варианту в следующем порядке: полностью относительный (A1)> полностью абсолютный ($A$1)> абсолютный ряд (A$1)> абсолютный столбец ($A1).

20. Помните, что формулы и функции возвращают значение. Всегда.

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

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

21. Используйте F9 для оценки частей формулы

Клавиша F9 (Fn + F9 на Mac) может решать части формулы в режиме реального времени. Это фантастический инструмент для отладки больших формул, когда вам необходимо убедиться, что вы получите ожидаемый результат определенной части формулы.

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

Примечание: В Windows вы можете отменить F9, но на Mac — нет. Чтобы выйти из формулы без внесения изменений, просто используйте Esc.

22. Используйте функцию «Вычислить формулу»

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

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

Примечание: Эта функция доступна только в версии для Windows. Версия для Mac использует другой подход, называемый Formula Builder, который отображает результаты при создании формулы. Функциональность отличается, но этот подход также очень полезен.

23. Построить сложные формулы в небольших шагах

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

Например, вы хотите написать формулу, которая извлекает имя из полного имени. Вы знаете, что могли бы использовать функцию ЛЕВСИМВ (LEFT), чтобы вытянуть текст слева, но вы не знаете, как рассчитать количество символов для извлечения. Начните с ЛЕВСИМВ (полное-имя; 5), чтобы формула заработала. Затем подумайте, как заменить число 5 на вычисленное значение. В этом случае вы можете определить количество извлекаемых символов, используя функцию НАЙТИ, чтобы определить положение первого пробела.

24. Используйте именованные диапазоны как переменные

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

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

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

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

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

26. Добавьте разрывы строк во вложенные ЕСЛИ (IF), чтобы их было легче читать

Когда вы создаете вложенную формулу ЕСЛИ (IF), отслеживание истинных и ложных аргументов в скобках может привести к путанице. Когда вы будете закрывать скобки, легко ошибиться в логике. Однако существует простой способ сделать формулу с несколькими операторами ЕСЛИ (IF) более читабельной: просто добавьте разрывы строк в формулу после каждого ИСТИННОГО аргумента. Это сделает формулу более похожей на таблицу.

27. Вводите функции с автозаполнением

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

28. Используйте Автосумму для ввода формул СУММ (SUM)

Этот способ подойдет не для всех случаев, но при использовании точно доставит удовольствие. Автосумма работает как для строк, так и для столбцов. Просто выберите пустую ячейку справа или под ячейками, которые вы хотите суммировать, и введите Alt + = (Mac: Command + Shift + T). Excel определит диапазон, который вы пытаетесь суммировать, и вставит функцию СУММ (SUM) за один шаг. Если вы хотите быть более конкретным, чтобы Excel не догадывался, сначала выберите диапазон, который вы хотите суммировать, включая ячейку, в которой вы хотите использовать функцию СУММ (SUM).

Автосумма даже вставит несколько функций СУММ (SUM) одновременно. Чтобы сложить несколько столбцов, выберите диапазон пустых ячеек под столбцами. Чтобы сложить несколько строк, выберите диапазон пустых ячеек в столбце справа от строк.

Наконец, вы можете использовать Автосумму для одновременного добавления итогов для строк и столбцов для всей таблицы. Просто выберите полную таблицу чисел, включая пустые ячейки под таблицей и справа от таблицы, и используйте горячие клавиши. Excel добавит соответствующие функции СУММ (SUM) в пустые ячейки, предоставляя вам итоги столбцов, итогов строк и итоговых сумм за один шаг.

29. Введите одну и ту же формулу одновременно в несколько ячеек.

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

Вы также можете использовать эту же технику для редактирования нескольких формул одновременно. Просто выберите все формулы , внесите необходимые изменения и нажмите Ctrl + Enter.

Самая популярная программа для работы с электронными таблицами «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. Доступным языком рассказали о формулах и о тех операциях, которые можно с ними проводить.

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

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

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

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

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

=ИНДЕКС(диапазон_данных_для_выборки;МИН(ЕСЛИ(диапазон=МИН(диапазон);СТРОКА(диапазон)-СТРОКА(заголовок_столбца);””)))

В месте «диапазон_данных_для_выборки» следует указать область значений A6:A18 для выборки из таблицы (например, текстовых), из которых функция ИНДЕКС выберет одно результирующие значение. Аргумент «диапазон» означает область ячеек с числовыми значениями, из которых следует выбрать первое наименьшее число. В аргументе «заголовок_столбца» для второй функции СТРОКА, следует указать ссылку на ячейку с заголовком столбца, который содержит диапазон числовых значений.

Естественно эту формулу следует выполнять в массиве. Поэтому для подтверждения ее ввода следует нажимать не просто клавишу Enter, а целую комбинацию клавиш CTRL+SHIFT+Enter. Если все сделано правильно в строке формул появятся фигурные скобки.

Обратите внимание ниже на рисунок, где в ячейку B3 была введена данная формула в массиве:

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

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



Как работает выборка по условию

Ключевую роль здесь играет функция ИНДЕКС. Ее номинальное задание – это выбирать из исходной таблицы (указывается в первом аргументе – A6:A18) значения соответственные определенным числам. ИНДЕКС работает с учетом критериев определённых во втором (номер строки внутри таблицы) и третьем (номер столбца в таблице) аргументах. Так как наша исходная таблица A6:A18 имеет только 1 столбец, то третий аргумент в функции ИНДЕКС мы не указываем.

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

Функция ЕСЛИ позволяет выбрать значение из списка по условию. В ее первом аргументе указано где проверяется каждая ячейка в диапазоне B6:B18 на наличие наименьшего числового значения: ЕСЛИB6:B18=МИНB6:B18. Таким способом в памяти программы создается массив из логических значений ИСТИНА и ЛОЖЬ. В нашем случаи 3 элемента массива будут содержат значение ИСТИНА, так как минимальное значение 8 содержит еще 2 дубликата в столбце B6:B18.

Следующий шаг – это определение в каких именно строках диапазона находится каждое минимальное значение. Это нам необходимо по причине определения именно первого наименьшего значения. Реализовывается данная задача с помощью функции СТРОКА, она заполняет элементы массива в памяти программы номерами строк листа. Но сначала от всех этих номеров вычитается номер на против первой строки таблицы – B5, то есть число 5. Это делается потому, что функция ИНДЕКС работает с номерами внутри таблицы, а не с номерами рабочего листа Excel. В тоже время функция СТРОКА умеет возвращать только номера строк листа. Чтобы не получилось смещение необходимо сопоставить порядок номеров строк листа и таблицы с помощи вычитанием разницы. Например, если таблица находится на 5-ой строке листа значит каждая строка таблицы будет на 5 меньше чем соответственная строка листа.

После того как будут отобраны все минимальные значения и сопоставлены все номера строк таблицы функция МИН выберет наименьший номер строки. Эта же строка будет содержать первое наименьшее число, которое встречается в столбце B6:B18. На основании этого номера строки функции ИНДЕКС выберет соответствующее значение из таблицы A6:A18. В итоге формула возвращает это значение в ячейку B3 в качестве результата вычисления.

Как выбрать значение с наибольшим числом в Excel

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

Как в Excel выбрать первое минимальное значение кроме нуля:

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

Скачать пример выборки из таблицы в Excel.

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

Преобразование формул в значения

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

• Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
• Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
• Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
• Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его правой кнопкой мыши – Копировать (Copy).
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

   
   либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

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

   В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

   

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

1. Копируем выделенный диапазон Ctrl+C
2. Тут же вставляем обратно сочетанием Ctrl+V
3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

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

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
 

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub
 

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

В этом случае:

• всё будет максимально быстро и просто
• можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
• в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
• любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Ссылки по теме

• Что такое макросы, как их использовать, копировать и запускать
• Как скопировать формулы без сдвига ссылок
• Как считать в Excel без формул

При работе в Excel пользователи иногда сталкиваются с задачей произвести выбор из списка определенного элемента и на основании его индекса присвоить ему указанное значение. С данной задачей прекрасно справляется функция, которая так и называется «ВЫБОР». Давайте узнаем подробно, как работать с данным оператором, и с какими поставленными проблемами он может справиться.

Использование оператора ВЫБОР

Функция ВЫБОР относится к категории операторов «Ссылки и массивы». Её целью является выведение определенного значения в указанную ячейку, которому соответствует номер индекса в другом элементе на листе. Синтаксис этого оператора следующий:

=ВЫБОР(номер_индекса;значение1;значение2;…)

Аргумент «Номер индекса» содержит ссылку на ячейку, где находится порядковый номер элемента, которому следующей группой операторов присваивается определенное значение. Этот порядковый номер может варьироваться от 1 до 254. Если задать индекс, превышающий данное число, то оператор выведет в ячейку ошибку. Если в качестве данного аргумента ввести дробное значение, то функция воспримет его, как ближайшее к данному числу меньшее целое значение. Если задать «Номер индекса», для которого нет соответствующего аргумента «Значение», то оператор будет возвращать в ячейку ошибку.

Следующая группа аргументов «Значение». Она может достигать количества 254 элементов. При этом обязательным является аргумент «Значение1». В данной группе аргументов указываются те значения, которым будет соответствовать номер индекса предыдущего аргумента. То есть, если в качестве аргумента «Номер индекса» выступает число «3», то ему будет соответствовать значение, которое внесено, как аргумент «Значение3».

В качестве значений могут выступать разнообразные виды данных:

• Ссылки;
• Числа;
• Текст;
• Формулы;
• Функции и т. д.

Теперь давайте рассмотрим конкретные примеры применения данного оператора.

Пример 1: последовательный порядок расположения элементов

Давайте посмотрим, как действует данная функция на простейшем примере. У нас имеется таблица с нумерацией от 1 до 12. Нужно согласно данным порядковым номерам с помощью функции ВЫБОР указать наименование соответствующего месяца во второй колонке таблицы.

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



2. Производится запуск Мастера функций. Переходим в категорию «Ссылки и массивы». Выбираем из перечня наименование «ВЫБОР» и щелкаем по кнопке «OK».



3. Запускается окно аргументов оператора ВЫБОР. В поле «Номер индекса» следует указать адрес первой ячейки диапазона нумерации месяцев. Эту процедуру можно совершить, вбив координаты вручную. Но мы поступим более удобно. Устанавливаем курсор в поле и кликаем левой кнопкой мыши по соответствующей ячейке на листе. Как видим, координаты автоматически отобразились и в поле окна аргументов.

   После этого нам предстоит вручную вбить в группу полей «Значение» наименование месяцев. Причем каждому полю должен соответствовать отдельный месяц, то есть, в поле «Значение1» записываем «Январь», в поле «Значение2» — «Февраль» и т. д.

   После выполнения указанной задачи щелкаем по кнопке «OK» внизу окна.



4. Как видим, сразу в той ячейке, которую мы отметили ещё в первом действии, отобразился результат, а именно наименование «Январь», соответствующее первому номеру месяца в году.



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





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

Урок: Мастер функций в Экселе

Пример 2: произвольный порядок расположения элементов

В предыдущем случае мы применили формулу ВЫБОР, когда все значения номеров индекса были расставлены по порядку. Но как работает данный оператор в случае, если указанные значения перемешаны и повторяются? Давайте рассмотрим это на примере таблицы с успеваемостью школьников. В первом столбце таблицы указана фамилия ученика, во втором оценка (от 1 до 5 баллов), а в третьем нам предстоит с помощью функции ВЫБОР дать данной оценке соответствующую характеристику («очень плохо», «плохо», «удовлетворительно», «хорошо», «отлично»).

1. Выделяем первую ячейку в колонке «Описание» и переходим при помощи того способа, о котором уже шел разговор выше, в окно аргументов оператора ВЫБОР.

   В поле «Номер индекса» указываем ссылку на первую ячейку столбца «Оценка», в которой содержится балл.

   Группу полей «Значение» заполняем следующим образом:

   • «Значение1» — «Очень плохо»;
   • «Значение2» — «Плохо»;
   • «Значение3» — «Удовлетворительно»;
   • «Значение4» — «Хорошо»;
   • «Значение5» — «Отлично».

   После того, как введение вышеуказанных данных произведено, щелкаем по кнопке «OK».



2. Значение балла для первого элемента выведено в ячейку.



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

Пример 3: использование в комбинации с другими операторами

Но гораздо продуктивнее оператор ВЫБОР можно использовать в комбинации с другими функциями. Посмотрим, как это делается на примере применения операторов ВЫБОР и СУММ.

Имеется таблица реализации продукции по торговым точкам. Она разбита на четыре столбца, каждый из которых соответствует определенной торговой точке. Выручка указана отдельно за определенную дату построчно. Наша задача — сделать так, чтобы после ввода номера торговой точки в определенную ячейку листа отображалась сумма выручки за все дни работы указанного магазина. Для этого мы и будем использовать комбинацию операторов СУММ и ВЫБОР.

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



2. Активируется окошко Мастера функций. На этот раз перемещаемся в категорию «Математические». Находим и выделяем наименование «СУММ». После этого щелкаем по кнопке «OK».



3. Происходит запуск окошка аргументов функции СУММ. Данный оператор используется для подсчета суммы чисел в ячейках листа. Его синтаксис довольно прост и понятен:

   =СУММ(число1;число2;…)

   То есть, аргументами данного оператора обычно являются либо числа, либо, еще чаще, ссылки на ячейки, где содержаться числа, которые необходимо просуммировать. Но в нашем случае в виде единственного аргумента выступит не число и не ссылка, а содержимое функции ВЫБОР.

   Устанавливаем курсор в поле «Число1». Затем кликаем по пиктограмме, которая изображена в виде перевернутого треугольника. Этот значок находится в том же горизонтальном ряду, где расположены кнопка «Вставить функцию» и строка формул, но слева от них. Открывается список недавно используемых функций. Так как формула ВЫБОР недавно применялась нами в предыдущем способе, то она имеется в этом списке. Поэтому достаточно кликнуть по данному наименованию, чтобы перейти в окно аргументов. Но более вероятно, что у вас в списке этого наименования не окажется. В этом случае нужно кликнуть по позиции «Другие функции…».



4. Производится запуск Мастера функций, в котором в разделе «Ссылки и массивы» мы должны отыскать наименование «ВЫБОР» и выделить его. Щелкаем по кнопке «OK».



5. Активируется окошко аргументов оператора ВЫБОР. В поле «Номер индекса» указываем ссылку на ту ячейку листа, в которую будем вводить номер торговой точки для последующего отображения общей суммы выручки по ней.

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

   Аналогичным образом в поле «Значение2» добавляем координаты столбца «2 торговая точка», в поле «Значение3» — «3 торговая точка», а в поле «Значение4» — «4 торговая точка».

   После выполнения данных действий жмем на кнопку «OK».



6. Но, как видим, формула выводит ошибочное значение. Это связано с тем, что мы ещё не ввели номер торговой точки в соответствующую ячейку.



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

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

Урок: Как посчитать сумму в Экселе

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