Формула енд в excel: Функция ЕНД как скрыть ошибку #Н/Д для ВПР и ПРОСМОТР в Excel

Функция Excel ISNA (Содержание)

• ISNA в Excel
• ISNA Formula в Excel
• Как использовать функцию ISNA в Excel?

ISNA в Excel

• Функция ISNA — это предварительно созданная функция в Excel, которая относится к категории ИНФОРМАЦИОННАЯ функция и представляет собой группу или семейство функций IS.
• Обычно # N / A! появляется ошибка, когда значение недоступно в диапазоне данных.
• Функция ISNA вместе с другой функцией позволяет нам заменить другое значение вместо ошибки, то есть модифицировать текстовую строку на другую.
• Чаще всего используется функция для анализа данных в финансовой сфере.
• Функция ISNA проста в использовании и гибкая функция, где нам нужно ввести только один аргумент, функция ISNA в Excel используется как лист и функция VBA в Excel.
• С помощью функции ISNA мы можем сообщить Excel оставить пустую ячейку или отобразить любой указанный или желаемый текст, например «Значение не найдено», вместо отображения # N / A! ошибка.

Определение

Функция ISNA в Excel вернет TRUE, если значением является # N / A error, и вернет FALSE, если его нет.

ISNA Formula в Excel

Ниже приведена формула ISNA в Excel:

Формула ISNA в Excel имеет один обязательный параметр: значение.

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

Как использовать функцию ISNA в Excel?

Функция ISNA в Excel очень проста и удобна в использовании. Позвольте понять работу функции ISNA в Excel на примере формулы ISNA в Excel.

Пример № 1 — функция ISNA

В приведенной ниже таблице. У меня есть различные типы значений ошибок в столбце ERROR_VALUES (столбец D). Здесь С помощью функции ISNA в Excel мне нужно выяснить # N / A ошибку в столбце D.

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

Нажмите кнопку функции вставки (fx) под панелью инструментов формул, появится диалоговое окно, введите ключевое слово «ISNA» в поле поиска функции и в раскрывающемся списке выберите категорию «ИНФОРМАЦИЯ». Так что функция ISNA появится в окне выбора функции. Дважды щелкните по функции ISNA.

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

Значение: Здесь я должен проверить, содержит ли значение в ячейке D8 ошибку # Н / Д или нет. Я должен упомянуть ссылку на ячейку «D8», т.е. = ISNA (D8).

Здесь = ISNA (D8) вернет TRUE, если значение является ошибкой # N / A, или вернет FALSE, если не содержит ошибку # N / A.

Ячейка D8 не содержит ошибки # N / A, она вернет FALSE в качестве выхода в ячейке «E8».

Чтобы получить выходные данные для полного набора данных, щелкните внутри ячейки E8, и вы увидите выбранную ячейку, затем выберите ячейки до E14. Таким образом, этот диапазон столбцов будет выбран, как только он будет выбран, нажмите Ctrl + D, чтобы формула ISNA была применена ко всему диапазону.

Ячейка E13 возвращает TRUE, значение, потому что ячейка D13 содержит # N / A Error.

Пример # 2 — функция ISNA & IF с функцией VLOOKUP для предотвращения ошибки # N / A

В приведенной ниже таблице1. У меня есть список учеников в колонке учеников (колонка J) и их оценки в колонке баллов (колонка K).

Таблица 2 содержит список имен учеников в колонке учеников (колонка M), наша цель здесь в этой таблице — найти оценку ученика на основе его имени, то есть имени ученика (см. Таблицу 1), с помощью функции VLOOKUP.

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

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

Нажмите кнопку функции вставки (fx) под панелью инструментов формул, появится диалоговое окно, введите ключевое слово «VLOOKUP» в поле поиска функции, функция VLOOKUP появится в поле выбора функции. Дважды щелкните по функции VLOOKUP.

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

Синтаксис для функции VLOOKUP:

VLOOKUP (lookup_value, table_array, col_index_num, (range_lookup))

• lookup_value: значение, которое вы хотите найти, т.е. «M7» или «CINDER».
• table_array: диапазон, в котором находится искомое значение, т.е. выберите диапазон table1 J6: K17.
• col_index_num: номер столбца в массиве таблицы, из которого должно быть возвращено соответствующее значение. Здесь оценка студента в таблице 1 находится во втором столбце, т.е. 2.
• range_lookup: FALSE для точного соответствия или TRUE для приблизительного соответствия. Выберите 0 или false.

= VLOOKUP (M7, J6: K17, 2, 0) возвращает оценку шлака, т.е. 77.

Чтобы получить окончательные данные для других учащихся, щелкните внутри ячейки N7, и вы увидите выбранную ячейку, затем выберите ячейки до N12. Таким образом, этот диапазон столбцов будет выделен, как только он будет выделен, нажмите Ctrl + D, чтобы формула VLOOKUP была применена ко всему диапазону.

Здесь ошибка # N / A возвращается в ячейки N10 и N12. потому что функция Vlookup не может найти соответствие предоставленному lookup_value в диапазоне таблицы ссылок.

# Н / Д ошибка выглядит странно. Для лучшего прогноза окончательного набора данных без ошибки N / A. Функция ISNA используется с функцией IF в функции VLOOKUP, чтобы избежать ошибки # N / A. Где # Н / Д Значение ошибки заменяется настраиваемой текстовой строкой.

Предположим, я хочу персонализированное сообщение, например «Не найдено», а не # N / A error.

Давайте поработаем над этим в таблице 3. Ниже упомянутая функция ISNA с IF используется с функцией VLOOKUP, чтобы избежать ошибки # N / A.

= ЕСЛИ (ISNA (VLOOKUP (P7, J6: K17, 2, 0)), «Не найдено», VLOOKUP (P7, J6: K17, 2, 0))

Три части формулы

• = ЕСЛИ (ЕНД (ВПР (Р7, J6: K17, 2, 0)),

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

Выведите «Not found» вместо # N / A.

• ВПР (P7, J6: K17, 2, 0))

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

Давайте применим эту формулу в ячейке «Q7».

Результатом будет:

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

Он вернет настроенную текстовую строку, указанную в формуле, т.е. «Не найдено», вместо ошибки # N / A в ячейке N10 и N12.

• Значение аргумента ISNA может быть пустым (пустая ячейка), текстом, числом, ошибкой, логическим или ссылочным значением или именем, относящимся к любому из них, которое вы хотите проверить.
• Функции IS важны в формулах для проверки результатов расчета. Если он сочетается с логическими условиями IF, это поможет найти значение ошибки.

Рекомендуемые статьи

Это было руководство по ISNA в Excel. Здесь мы обсуждаем формулу ISNA в Excel и Как использовать функцию ISNA в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

1. Руководство по полосе прокрутки в Excel
2. Примеры функции LN в Excel
3. Функция Excel COMBIN
4. Функция SUMIF в Excel

Excel: волшебные формулы (Excel2)

На этом курсе мы разовьём свои знания, умения и навыки работы с функциями ВПР, СУММЕСЛИМН, а также научимся использовать функции ПОИСКПОЗ, ИНДЕКС, ДВССЫЛ, СМЕЩ, познакомимся с языком оформления чисел, порешаем множество задач из моей практики.

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

Вот примеры некоторых задач:

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

На этом примере мы разберем как используются функции ВПР или ПОИСКПОЗ с ИНДЕКС для сложной выборки из «прямоугольного диапазона. В основном курсе м выбирали данные из «одномерного» потока. Как быть, когда поток двумерный — будем разбирать.

А потом задача еще усложнится и окажется, что информация может лежать на нескольких листах.

Собрать заказы с нескольких листов:

Вот такие бланки поступают к заказчику на разных листах.

Приглядитесь, внизу есть корешки с номерами заказов: 001, 002 и т.д. Нам надо собрать все сведения из зеленых ячеек с разных листов в итоговую таблицу:

Мы это сделаем при помощи функции ДВССЫЛ. Страшное название, но очень впечатляющее действие.

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

На отдельный лист:

И тут нам пригодится функция СМЕЩ.

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

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

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

Функция ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами

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

В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:h21». Знаки «$» можно поставить вручную, а можно выделить «G2:h21» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

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

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

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

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

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

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

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

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

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

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

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

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

• Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

• Функцию можно вписать вручную или в специальном окне (Shift + F3).

• Искомое значение – относительная ссылка, а таблица – абсолютная. 

• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

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

• Порядок работы с функцией подходит для Гугл-таблиц.

Заполните формулу до соседних ячеек

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

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

2. Перетащите маркер заполнения по ячейкам, которые нужно заполнить.

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

   1. Щелкните Файл > Параметры

   2. Щелкните Advanced .

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

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

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

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

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

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

2. Щелкните Home > Fill и выберите Down , Right , Up или Left .

   Сочетание клавиш: Вы также можете нажать Ctrl + D, чтобы заполнить формулу вниз по столбцу, или Ctrl + R, чтобы заполнить формулу справа в строке.

Поверните расчет рабочей книги на

Формулы не пересчитываются при заполнении ячеек, если автоматический расчет книги не включен.

Вот как это можно включить:

1. Щелкните Файл > Параметры .

2. Щелкните Формулы .

3. В разделе Расчет рабочей книги выберите Автоматически .

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

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

Сочетание клавиш: Вы также можете нажать Ctrl + D, чтобы заполнить формулу вниз по ячейке в столбце, или Ctrl + R, чтобы заполнить формулу справа в строке.

Скопируйте формулу, перетащив маркер заполнения в Excel для Mac

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

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

Скопируйте формулу, перетащив маркер заполнения

Выполните следующие действия:

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

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

3. Перетащите маркер заполнения вниз, вверх или через ячейки, которые вы хотите заполнить.В этом примере на рисунке показано перетаскивание маркера заполнения вниз:

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

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

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

Советы:

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

• Вы также можете нажать Ctrl + R, чтобы заполнить формулу справа в строке.Сначала выберите ячейку с формулой, которую вы хотите заполнить, затем выберите ячейки справа от нее и нажмите Ctrl + R.

При неработающем наполнении

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

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

= СУММ (A1, B1)

A1 и B1 — это относительные ссылки . Это просто означает, что когда вы заполняете формулу, ссылки будут постепенно изменяться с A1, B1 на A2, B2 и так далее, например:

= СУММ (A1, B1)

= СУММ (A2; B2)

= СУММ (A3, B3)

В других случаях вы можете не захотеть изменять ссылки на другие ячейки.Например, предположим, что вы хотите, чтобы первая ссылка, A1, оставалась фиксированной, а B1 изменялась при перетаскивании маркера заполнения. В этом случае вы должны ввести знак доллара ($) в первой ссылке: = СУММ ($ A $ 1, B1) . Знак доллара заставляет Excel продолжать указывать на A1 при заполнении других ячеек. Вот как это будет выглядеть:

= СУММ ($ A $ 1, B1)

= СУММ ($ 1, B2)

= СУММ ($ 3, B3)

Ссылки со знаками доллара ($) называются абсолютными ссылками .При заполнении ссылка на A1 остается неизменной, но Excel меняет ссылку на B1 на B2 и B3.

Не видите дескриптор файла?

Если маркер заполнения не отображается, возможно, он скрыт. Для отображения:

1. В меню Excel щелкните Параметры .

2. Щелкните Изменить .

3. В разделе «Параметры редактирования » установите флажок « Разрешить дескриптор заполнения и перетаскивание ячейки» .

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

Вот как его включить:

1. В меню Excel щелкните Параметры .

2. Щелкните Расчет .

3. Убедитесь, что в параметрах расчета выбрано Автоматически .

Как применить формулу ко всему столбцу в Excel (5 простых способов)

Формулы — неотъемлемая часть электронных таблиц Excel. И в большинстве случаев вам не нужна формула в одной или нескольких ячейках.

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

И Excel дает вам несколько различных способов сделать это с помощью нескольких щелчков мышью (или сочетания клавиш).

Давайте посмотрим на эти методы.

Двойным щелчком по маркеру автозаполнения

Один из самых простых способов применить формулу ко всему столбцу — это использовать этот простой трюк с двойным щелчком мыши.

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

Формула для этого будет:

 = B2 * 15% 

Ниже приведен способ применения этой формулы ко всему столбцу C:

1. В ячейке A2 введите формулу: = B2 * 15%
2. Выбрав ячейку, вы увидите небольшой зеленый квадрат в правом нижнем углу выделения.
3. Поместите курсор на маленький зеленый квадрат. Вы заметите, что курсор изменится на знак плюса (это называется дескриптором автозаполнения)
4. Дважды щелкните левой кнопкой мыши

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

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

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

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

Путем перетаскивания маркера автозаполнения

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

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

Ниже приведены шаги для этого:

1. В ячейке A2 введите формулу: = B2 * 15%
2. Если ячейка выбрана, вы увидите маленький зеленый квадрат в правом нижнем углу окна. selection
3. Поместите курсор на маленький зеленый квадрат. Вы заметите, что курсор изменится на знак «плюс»
4. Удерживая левую клавишу мыши, перетащите ее в ячейку, где вы хотите применить формулу
5. Оставьте клавишу мыши, когда закончите

Использование опции «Заполнить вниз» (это на ленте)

Другой способ применить формулу ко всему столбцу — использовать параметр заполнения на ленте.

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

Ниже приведены шаги по использованию метода заполнения:

1. В ячейке A2 введите формулу: = B2 * 15%
2. Выберите все ячейки, в которых вы хотите применить формулу (включая ячейку C2)
3. Перейдите на вкладку «Главная»
4. В группе редактирования щелкните значок «Заливка».
5. Щелкните «Заполнить»

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

Добавление заливки Вниз на панели быстрого доступа

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

T0 добавьте его на панель быстрого доступа (QAT), перейдите к опции «Заполнить вниз», щелкните его правой кнопкой мыши, а затем нажмите «Добавить на панель быстрого доступа»

Теперь вы увидите значок «Заполнить вниз» появится в QAT.

Использование сочетания клавиш

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

 CONTROL + D (удерживайте клавишу управления, а затем нажмите клавишу D) 

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

1. В ячейке A2 введите формулу: = B2 * 15%
2. Выберите все ячейки, в которых вы хотите применить формулу (включая ячейку C2 )
3. Удерживая клавишу Control, нажмите клавишу D

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

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

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

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

 = B2: B15 * 15% 

Это массив формула, которая вернет 14 значений в ячейке (по одному для B2: B15). Но поскольку у нас есть динамические массивы, результат не будет ограничиваться отдельной ячейкой и будет распространяться на весь столбец.

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

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

Путем копирования-вставки ячейки

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

Ниже приведены шаги для этого:

1. В ячейке A2 введите формулу: = B2 * 15%
2. Скопируйте ячейку (используйте сочетание клавиш Control + C в Windows или Command + C в Mac)
3. Выделите все ячейки, в которых вы хотите применить ту же формулу (за исключением ячейки C2)
4. Вставьте скопированную ячейку (Control + V в Windows и Command + V в Mac)

Одно различие между этим методом копирования и вставки и всеми другими методы convert ниже выше, это то, что с помощью этого метода вы можете выбрать только вставку формулы (но не вставку какого-либо форматирования).

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

Если вы хотите применить только формулу, а не форматирование, выполните следующие действия:

1. В ячейке A2 введите формулу: = B2 * 15%
2. Скопируйте ячейка (используйте сочетание клавиш Control + C в Windows или Command + C в Mac)
3. Выберите все ячейки, в которых вы хотите применить ту же формулу
4. Щелкните правой кнопкой мыши выделение
5. В появившихся параметрах нажмите «Специальная вставка»
6. В диалоговом окне «Специальная вставка» выберите параметр «Формулы».
7. Нажмите «ОК»

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

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

Надеюсь, вы нашли этот урок полезным!

Другие руководства по Excel, которые могут вам также понравиться:

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

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

Изображение: iStock / dima_sidelnikov

Получение последнего значения в столбце — простая задача в базе данных, но Excel не является базой данных (хотя многие люди используют его как единое целое). В Excel выполнить эту задачу немного сложнее, но ненамного. В этой статье я покажу вам формулу, которая работает с объектом Table и обычным диапазоном данных. Я также покажу вам эквиваленты VBA.

Я использую Excel 2016 в 64-разрядной системе Windows 10.Для вашего удобства вы можете скачать демонстрационные файлы .xlsx и CLS. Формула набора данных не работает в более ранних версиях меню, а макросы не работают в версии браузера. Версия для браузера не распознает объект Table, но формула по-прежнему работает — Excel достаточно умен, чтобы использовать структурные ссылки объекта Table, если вы открываете файл в браузере.

ПОСМОТРЕТЬ: Развивайте свои навыки работы с Excel с помощью этих 10 полезных советов (TechRepublic PDF)

Используйте таблицу

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

На рисунке A показана простая таблица данных счета-фактуры и формула (E2), которая возвращает последнюю дату в столбце C «Дата счета-фактуры». Как видите, формула и таблица содержат итоговую строку таблицы и смежные данные.

Рисунок A

Эта формула возвращает последнюю дату в столбце C.

В формуле используются структурированные ссылки на таблицу и столбец «Дата счета-фактуры»:

 = ИНДЕКС (Счета-фактуры [Дата счета-фактуры], COUNTA (Счета-фактуры [Дата счета-фактуры])) 

Чтобы понять, как работает эта формула, вам необходимо знать, как работают функции ИНДЕКС () и СЧЁТ ():

• ИНДЕКС () возвращает значение или ссылку на ячейку на пересечении определенной строки и столбца в заданном диапазон с использованием синтаксиса ИНДЕКС ( массив , строка , [ столбец ]).
• COUNTA () подсчитывает количество непустых ячеек в определенном диапазоне, используя синтаксис COUNTA ( значение1 , [ значение2 ]).

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

Функция COUNTA () возвращает 30, поскольку в столбце «Дата счета-фактуры» 30 строк, исключая строки заголовка и итогов. (Вы можете включить эти строки в подсчет, включив идентификатор [#All].) Поскольку массив имеет только один столбец, аргумент столбца не требуется. Поскольку COUNTA () возвращает 30, функция INDEX () возвращает значение в данных счета-фактуры в строке 30, 07.08.2017. Формула продолжает работать, если вы переместите таблицу, если вы добавите строку над таблицей и если вы добавите данные выше или ниже данных счета.

СМ.: 10 советов по экономии времени для ускорения работы в Excel (TechRepublic)

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

Вы можете использовать VBA для достижения того же результата, используя код из Листинг A . Свойство End (xlDown) относится к последней ячейке в столбце «Дата счета». Как и раньше, ссылки на таблицу знают, где находится последняя строка, поэтому текст под таблицей не является проблемой.Однако эта ссылка включает строку итогов, которую поддерживает свойство Offset.

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

Листинг A

 Функция ReturnLastValueTable ()
'Возвращает последнее значение в указанном столбце.
ReturnLastValueTable = Диапазон («Счета-фактуры [Дата счета-фактуры]»). _
Конец (xlDown) .Offset (-1, 0) .Value
Отлаживать.Распечатать ReturnLastValueTable
Конечная функция 

Использовать диапазон данных

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

 = ИНДЕКС (C: C, COUNTA (C: C)) 

На этот раз функция COUNTA () возвращает 31, поскольку она считает ячейку заголовка в строке 1. Таким образом, формула возвращает значение в строке 31 в столбце C, 07.08.2017. Если у вас есть ряд итогов, вы также должны учесть его.

Рисунок B

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

Формула работает с обычным диапазоном данных, но менее гибкая. Вы не можете переместить набор данных, потому что ссылки на столбцы не обновляются. Вставка строк над набором данных может прервать вычисление, поскольку COUNTA () не будет оценивать пустые строки над набором данных — COUNTA () возвращает количество ячеек, содержащих данные. Однако, если ячейки над набором данных в столбце C содержат данные, формула по-прежнему работает.Я не рекомендую такую ​​компоновку, потому что вы должны помнить эту деталь. Вы не можете ввести данные под набором данных, потому что ссылка на столбец в COUNTA () оценивает весь столбец; ввод данных под набором данных сбросит счет.

Вы можете сделать то же самое, используя код из Листинг B . Свойство End (xlDown) останавливается на первой пустой ячейке. Это означает, что вы можете ввести данные под набором данных в столбце C, если между последней строкой и новыми данными есть хотя бы одна пустая ячейка.

Листинг B

 Функция ReturnLastValueDataSet ()
'Возвращает последнее значение в указанном столбце.
ReturnLastValueDataSet = Range ("C1"). End (xlDown) .Value
Debug.Print ReturnLastValueDataSet
Конечная функция 

Последний звонок

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

Еженедельный бюллетень Microsoft

Будьте инсайдером Microsoft в своей компании, прочитав эти советы, рекомендации и шпаргалки по Windows и Office. Доставка по понедельникам и средам.

Пришлите мне свой вопрос об офисе

Я отвечаю на вопросы читателей, когда могу, но нет никаких гарантий.Не отправляйте файлы без запроса; первоначальные запросы о помощи, поступающие с прикрепленными файлами, будут удалены непрочитанными. Вы можете отправить снимки экрана с вашими данными, чтобы прояснить ваш вопрос. Обращаясь ко мне, будьте как можно более конкретными. Например, запрос «Устраните неполадки в моей книге и исправьте, что не так», вероятно, не получит ответа, но «Вы можете сказать мне, почему эта формула не возвращает ожидаемых результатов?» мог бы. Пожалуйста, укажите приложение и версию, которую вы используете. TechRepublic не возмещает мне затраты времени или опыта при оказании помощи читателям, и я не прошу плату у читателей, которым помогаю.Вы можете связаться со мной по адресу [email protected].

Также прочтите …

Оцените себя в конце квартала

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

ВОПРОС

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

ОТВЕТ

Вы можете взломать функцию EOMONTH для выполнения расчета на конец квартала.Решение включает редко используемую, но универсальную функцию в Excel под названием MOD. На рисунке 1 в столбце A указаны случайные даты в 2017 году. В столбце B функция EOMONTH находит последний день месяца для даты в столбце A.

Формула в B2: = EOMONTH (A2,0)

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

Прежде чем я расскажу о формуле в C2, которая определяет дату конца квартала, давайте посмотрим, что нам нужно делать.Вторая часть функции EOMONTH определяет, сколько месяцев добавить к дате в A2. В приведенной выше формуле введен ноль, поскольку она вычисляет дату окончания текущего месяца. Нам нужен гибкий расчет, который берет дату в A2 и определяет, сколько месяцев нужно добавить к этой дате, чтобы прийти к месяцу конца квартала для этой даты. Функция EOMONTH сделает все остальное и вычислит дату конца месяца для этого месяца конца квартала.

Формула решения в ячейке C2:
= EOMONTH (A2; MOD (3-МЕСЯЦ (A2); 3))

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

Функция МЕСЯЦ в приведенной выше формуле проста; он возвращает число от 1 до 12, представляющее номер календарного месяца соответствующей даты.

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

Повышение квалификации: Основные формулы Excel для профессионалов в области финансов — веб-семинар: узнайте, как создавать ключевые формулы Excel, чтобы сократить время, затрачиваемое на вычисление чисел, улучшить отчеты и анализ.

MOD функция

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

Функция МОД возвращает остаток от деления одного числа на другое. Этот остаток на удивление полезен в определенных ситуациях. Остальное работает нелогично для отрицательных чисел. (Формула, которую Excel использует для вычисления функции MOD, показана в конце статьи на рисунке 4.)

Синтаксис:
MOD (число, делитель)
Число — любое значение, обычно ссылка на ячейку или вычисление.
Делитель — значение, которое нужно разделить на Число.

Примеры:
Предположим, ячейка A1 содержит 7:
= MOD (A1,3) возвращает 1. Три переходит в 7 дважды, а остаток равен 1.
= MOD (A1,12) возвращает 7, так как 12 вообще не входит в 7, поэтому 7 — это остаток.

Отрицательные числа работают немного иначе. Предположим, ячейка A1 содержит -7:

= MOD (A1,3) возвращает 2. Три переходят в -9 три раза. Разница между -9 и -7 равна 2, поэтому остаток равен 2. Чтобы получить остаток, кратное (-9) должно быть меньше числа (-7).Вы не используете -6, так как это значение больше числа.

= MOD (A1,12) возвращает 5, так как 12 сразу превращается в -12. Разница между -12 и -7 равна 5. Опять же, кратное (-12) должно быть меньше числа. Нельзя использовать ноль, так как он больше числа.

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

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

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

Формула в ячейке B2 скопирована, это: = MOD (A2,3)

В этой формуле делитель равен 3, потому что в квартале три месяца.

Как видите, в столбце B есть шаблон. Здесь всего три числа, и их последовательность повторяется. Это остатки от номера месяца после деления на три. Это стандартный расчет MOD. Это сообщает нам, через сколько месяцев после предыдущего квартала находится текущий номер месяца. Например, ячейка B12 сообщает нам, что 11 (ноябрь) — это два месяца после 9 (сентябрь). Это может быть полезно для других вычислений, но столбец B не предоставляет нам нужных значений.

Однако мы можем изменить функцию MOD, чтобы обеспечить правильные значения. Этот метод использует то, как MOD обрабатывает негативы. В столбце C есть расчет, в котором номер месяца в столбце A вычитается из трех (делитель).

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

Формула в ячейке D2:
= МОД (C2,3)

Это вычисляет, сколько месяцев нам нужно добавить к номеру месяца в столбце A, чтобы получить номер месяца на конец квартала.В столбце E показаны числа в конце четверти путем добавления числа в столбце A к результату MOD из столбца D.

Вернемся к нашему решению в ячейке C2 на рисунке 1:
= EOMONTH (A2; MOD (3-МЕСЯЦ (A2); 3))

Здесь используется функция EOMONTH с датой из столбца A. Функция MOD вычитает номер месяца даты в столбце A из 3 и находит остаток после деления на 3. Это количество месяцев, которое нам нужно добавить к дате. в столбце A, чтобы получить данные на конец месяца.MOD в этой формуле объединяет столбцы A, C и D с рисунка 3 в единую формулу.

Последовательности

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

Функцию MOD можно использовать в бюджетах и ​​финансовых моделях для обработки периодических платежей: двухмесячных, квартальных или полугодовых.Обратите внимание на рис. 2, что в столбце B каждый раз, когда достигается кратное значение, MOD возвращает 0. Это может быть простой тест для функции ЕСЛИ для определения месяцев, которые являются двухмесячными, квартальными или полугодовыми.

На рисунке 4 показана формула, используемая для расчета результата MOD. Ссылка INT в формуле округляет число до ближайшего целого числа. Таким образом, (n / d) округляется до наименьшего целого числа. Для отрицательных чисел это означает округление от нуля, поэтому и -0,33, и -0.66 будет округлено до -1.

Сопутствующее видео и файл Excel могут помочь вам в понимании.

Нил Блэквуд CPA управляет бухгалтерским учетом формата A4, обеспечивая обучение по Excel, вебинары и консультационные услуги организациям по всей Австралии. Вопросы можно отправлять на [адрес электронной почты]

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

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

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

Теперь предположим, что у вас есть одна формула, которую вы хотите легко применить ко всему столбцу, не тратя много времени? Один из наиболее распространенных способов сделать это — перетащить формулу из ячейки 1 в последнюю ячейку. Хотя это может дать вам ответ, в конце концов, это отнимет у вас много времени и энергии. Перетаскивание также является очень утомительным способом выполнения вашей работы.

Если вам нужно применить ту же формулу, что и в этом случае, предположите, что C1 = A1 * 2, C2 = A2 * 2, C3 = A3 * 3 …… Cn = An * 2, есть более простой способ сделать это.Есть несколько хитрых способов сделать это, помимо перетаскивания формулы все время. Теперь давайте посмотрим три простых способа сделать это, которые сэкономят вам время и силы.

Используйте ключевые слова

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

Сначала выберите весь столбец и введите формулу в первую ячейку, т.е.С1 = (А1 * Х + N) / Т. После этого нажмите CTRL + введите ключей вместе. Это в конечном итоге даст вам результат.

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

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

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

Введите формулу в первую ячейку C1 = (A1 * X + N) / T. Просто убедитесь, что формула размещена первой в ячейке столбца или строки, которую вы хотите использовать.

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

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

Kutools для Excel

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

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

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

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

Позже нажмите Kutools> подробнее> операция .

Четкое изображение нарисовано на скриншоте ниже.

Оттуда переходите ко второму шагу. После щелчка по диалоговому окну инструментов управления переходите к выбору настраиваемого окна операции. Теперь введите аббревиатуры вашей формулы или даже введите формулу полностью.В этом случае мы добавим * X + N) / T в пустое поле в настраиваемом разделе. Наконец, нажмите кнопку ОК и подождите, как показано на снимке экрана ниже.

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

Полезные советы

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

1. Результаты будут в форме формул для каждой ячейки, если вы отметите опцию создания формул, как показано выше.
2. Теперь, если вы установите флажок Пропустить ячейки формулы, операция будет пропускать и намеренно игнорировать ячейки формулы в выбранном вами диапазоне.

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

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

Нравится:

Нравится Загрузка …

15 формул Excel, сочетания клавиш и приемы, которые сэкономят много времени

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

По правде говоря, есть — вы просто еще этого не знаете.

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

Что такое формулы Excel?

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

Мы рассмотрим все это и многое другое в этом сообщении в блоге.

Как вставлять формулы в Excel

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

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

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

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

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

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

ПРИМЕЧАНИЕ. Следующие формулы применимы к Excel 2017 . Если вы используете более старую версию Excel, расположение каждой функции, упомянутой ниже, может немного отличаться.

1. СУММА

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

Формула СУММ в Excel — одна из самых простых формул, которые вы можете ввести в электронную таблицу, позволяющую найти сумму (или итог) двух или более значений. Чтобы выполнить формулу СУММ, введите значения, которые вы хотите сложить, используя формат = СУММ (значение 1, значение 2 и т. Д.) .

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

• Чтобы найти СУММ 30 и 80, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (30, 80) .Нажмите «Enter», и в ячейке появится сумма обоих чисел: 110.
• Чтобы найти СУММУ значений в ячейках B2 и B11, например, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2, B11) . Нажмите «Enter», и в ячейке появится сумма чисел, которые в настоящее время заполнены в ячейках B2 и B11. Если ни в одной из ячеек нет чисел, формула вернет 0.

Имейте в виду, что вы также можете найти общее значение списка чисел в Excel.Чтобы найти СУММУ значений в ячейках B2 с по B11, введите следующую формулу в ячейку своей электронной таблицы: = СУММ (B2: B11) . Обратите внимание на двоеточие между обеими ячейками, а не на запятую. Посмотрите, как это может выглядеть в таблице Excel для контент-маркетолога, ниже:

2. IF

Формула ЕСЛИ в Excel обозначается = ЕСЛИ (логический_тест, значение_если_ истинно, значение_если_ ложь) . Это позволяет вам ввести текстовое значение в ячейку «если» что-то еще в вашей электронной таблице истинно или ложно.Например, = IF (D2 = «Гриффиндор», «10», «0») присудит 10 баллов ячейке D2, если эта ячейка содержит слово «Гриффиндор».

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

Мы вернемся к примеру Спранга для этого. Если мы хотим присудить 10 баллов всем, кто принадлежит к Гриффиндорскому дому, вместо того, чтобы вручную вводить десятки рядом с именем каждого гриффиндорского ученика, мы воспользуемся формулой ЕСЛИ-ТО, чтобы сказать: Если ученик находится в Гриффиндоре, , затем он или она должны получить десять баллов.

• Формула: IF (логический_тест, значение_если_ истинно, значение_если_ ложь)
  • Logical_Test: Логический тест — это часть оператора «ЕСЛИ». В данном случае логика D2 = «Гриффиндор». Убедитесь, что ваше значение Logical_Test заключено в кавычки.
  • Value_if_True: Если значение истинно, т. Е. Если студент живет в Гриффиндоре, то это значение мы хотим отобразить. В данном случае мы хотим, чтобы это было число 10, чтобы указать, что ученику были присвоены 10 баллов.Примечание. Используйте кавычки только в том случае, если вы хотите, чтобы результат был текстом, а не числом.
  • Value_if_False: Если значение false — а студент , а не , живет в Гриффиндоре — мы хотим, чтобы в ячейке отображалось «0» за 0 баллов.
• Формула в примере ниже: = IF (D2 = «Гриффиндор», «10», «0»)

3. Процент

Чтобы вычислить процентную формулу в Excel, введите ячейки, для которых вы найдете процентное значение, в формате = A1 / B1 .Чтобы преобразовать полученное десятичное значение в процент, выделите ячейку, щелкните вкладку «Главная» и выберите «Процент» в раскрывающемся списке чисел.

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

Базовая настройка для преобразования значения ячейки в процент находится на вкладке «Главная страница» в Excel . Выберите эту вкладку, выделите ячейки, которые вы хотите преобразовать в процент, и щелкните раскрывающееся меню рядом с условным форматированием (эта кнопка меню может сначала сказать «Общие»).Затем выберите «Процент» из появившегося списка параметров. Это преобразует значение каждой выделенной ячейки в процент. См. Эту функцию ниже.

Имейте в виду, что если вы используете другие формулы, такие как формула деления (обозначенная = A1 / B1 ), для возврата новых значений, ваши значения могут отображаться как десятичные по умолчанию. Просто выделите ячейки до или после выполнения этой формулы и установите для формата этих ячеек значение «Процент» на вкладке «Главная», как показано выше.

4. Вычитание

Чтобы выполнить формулу вычитания в Excel, введите вычитаемые ячейки в формате = СУММ (A1, -B1) . Это приведет к вычитанию ячейки по формуле СУММ, добавив отрицательный знак перед ячейкой, которую вы вычитаете. Например, если A1 было 10, а B1 было 6, = SUM (A1, -B1) выполнит 10 + -6, вернув значение 4.

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

• Использование формулы = СУММ. Чтобы вычесть несколько значений друг из друга, введите ячейки, которые вы хотите вычесть, в формате = СУММ (A1, -B1) со знаком минус (обозначается дефисом) перед ячейкой, значение которой вы вычитаете. Нажмите клавишу ВВОД, чтобы вернуть разницу между обеими ячейками, заключенную в круглые скобки. Посмотрите, как это выглядит на скриншоте выше.
• Использование формата = A1-B1 .Чтобы вычесть несколько значений друг из друга, просто введите знак равенства, за которым следует первое значение или ячейка, дефис и значение или ячейка, которые вы вычитаете. Нажмите Enter, чтобы вернуть разницу между обоими значениями.

5. Умножение

Чтобы выполнить формулу умножения в Excel, введите умножаемые ячейки в формате = A1 * B1 . В этой формуле звездочка используется для умножения ячейки A1 на ячейку B1. Например, если A1 было 10, а B1 было 6, = A1 * B1 вернет значение 60.

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

Чтобы умножить два или более значений в электронной таблице Excel, выделите пустую ячейку. Затем введите значения или ячейки, которые вы хотите умножить, в формате = A1 * B1 * C1 … и т. Д. Звездочка эффективно умножает каждое значение, включенное в формулу.

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

6. Отдел

Чтобы выполнить формулу деления в Excel, введите разделяемые ячейки в формате = A1 / B1 . В этой формуле используется косая черта «/» для разделения ячейки A1 на ячейку B1. Например, если A1 было 5, а B1 было 10, = A1 / B1 вернет десятичное значение 0,5.

Деление в Excel — одна из самых простых функций, которые вы можете выполнить. Для этого выделите пустую ячейку, введите знак равенства «=» и введите два (или более) значения, которые вы хотите разделить, с помощью косой черты «/» между ними.Результат должен быть в следующем формате: = B2 / A2 , как показано на скриншоте ниже.

Нажмите Enter, и желаемое частное должно появиться в первоначально выделенной ячейке.

7. ДАТА

Формула Excel ДАТА обозначается = ДАТА (год, месяц, день) . Эта формула вернет дату, соответствующую значениям, указанным в скобках, — даже значениям, полученным из других ячеек. Например, если A1 было 2018, B1 было 7, а C1 было 11, = DATE (A1, B1, C1) вернет 7/11/2018.

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

• Создание дат из ряда значений ячеек. Для этого выделите пустую ячейку, введите «= ДАТА» и в круглых скобках введите ячейки, значения которых создают желаемую дату — начиная с года, затем с номера месяца, затем с дня. Окончательный формат должен выглядеть так: = ДАТА (год, месяц, день).Посмотрите, как это выглядит на скриншоте ниже.
• Автоматически устанавливает сегодняшнюю дату. Для этого выделите пустую ячейку и введите следующую строку текста: = ДАТА (ГОД (СЕГОДНЯ ()), МЕСЯЦ (СЕГОДНЯ ()), ДЕНЬ (СЕГОДНЯ ())). Нажатие клавиши ВВОД вернет текущую дату, когда вы работаете в электронной таблице Excel.

При любом использовании формулы даты Excel возвращаемая дата должна быть в виде «мм / дд / гг» — если ваша программа Excel не отформатирована по-другому.

8. Массив

Формула массива в Excel окружает простую формулу в фигурных скобках в формате {= (Начальное значение 1: Конечное значение 1) * (Начальное значение 2: Конечное значение 2)} . При нажатии ctrl + shift + center будет вычислено и возвращено значение из нескольких диапазонов, а не только отдельные ячейки, добавленные или умноженные друг на друга.

Вычислить сумму, произведение или частное отдельных ячеек легко — просто используйте формулу = СУММ и введите ячейки, значения или диапазон ячеек, над которыми вы хотите выполнить эту арифметику.Но как насчет нескольких диапазонов? Как найти совокупное значение большой группы ячеек?

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

1. Чтобы начать использовать формулу массива, введите «= СУММ» и в скобках введите первые из двух (или трех, или четырех) диапазонов ячеек, которые вы хотите умножить вместе.Вот как может выглядеть ваш прогресс: = СУММ (C2: C5
2. Затем добавьте звездочку после последней ячейки первого диапазона, который вы включили в формулу. Это означает умножение. После этой звездочки введите второй диапазон ячеек. Вы умножите этот второй диапазон ячеек на первый. Ваш прогресс в этой формуле теперь должен выглядеть так: = СУММ (C2: C5 * D2: D5)
3. Готовы нажать Enter? Не так быстро … Поскольку эта формула настолько сложна, Excel резервирует другую команду клавиатуры для массивов.После того, как вы закроете круглые скобки в формуле массива, нажмите C trl + Shift + Enter . Это распознает вашу формулу как массив, заключит формулу в фигурные скобки и успешно вернет ваш продукт обоих диапазонов вместе.

При расчете доходов это может значительно сократить ваше время и усилия. См. Окончательную формулу на скриншоте выше.

9. СЧЕТ

Формула COUNT в Excel обозначается = COUNT (начальная ячейка: конечная ячейка) .Эта формула вернет значение, равное количеству записей, найденных в желаемом диапазоне ячеек. Например, если есть восемь ячеек с введенными значениями от A1 до A10, = COUNT (A1: A10) вернет значение 8.

Формула СЧЁТ в Excel особенно полезна для больших электронных таблиц , в которых вы хотите увидеть, сколько ячеек содержит фактические записи. Не дайте себя обмануть: Эта формула не выполняет никаких вычислений со значениями самих ячеек. Эта формула предназначена просто для того, чтобы узнать, сколько ячеек в выбранном диапазоне чем-то занято.

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

10. СРЕДНЕЕ

Чтобы выполнить формулу среднего в Excel, введите значения, ячейки или диапазон ячеек, для которых вы вычисляете среднее значение, в формате = СРЕДНЕЕ (число1, число2 и т. Д.), или = СРЕДНЕЕ (начальное значение: Конечное значение). Это вычислит среднее значение всех значений или диапазона ячеек, включенных в круглые скобки.

Нахождение среднего значения диапазона ячеек в Excel избавляет вас от необходимости находить отдельные суммы и затем выполнять отдельное уравнение деления для общей суммы. Используя = СРЕДНЕЕ в качестве начального ввода текста, вы можете позволить Excel сделать всю работу за вас.

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

11. СУММЕСЛИ

Формула СУММЕСЛИ в Excel обозначается = СУММЕСЛИ (диапазон, критерий, [диапазон суммы]) .Это вернет сумму значений в желаемом диапазоне ячеек, которые все соответствуют одному критерию. Например, = СУММЕСЛИ (C3: C12, «> 70 000») вернет сумму значений между ячейками C3 и C12 только из ячеек, размер которых превышает 70 000.

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

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

• Формула : = СУММЕСЛИ (диапазон, критерии, [диапазон_суммы])
  
  • Диапазон: Диапазон, который проверяется с использованием ваших критериев.
  • Критерии: Критерии, определяющие, какие ячейки в Criteria_range1 будут добавлены вместе
  • [Sum_range]: Необязательный диапазон ячеек, который вы собираетесь сложить в дополнение к первому введенному диапазону Range .Это поле можно не указывать.

В приведенном ниже примере мы хотели вычислить сумму зарплат, превышающих 70 000 долларов. Функция СУММЕСЛИ суммирует суммы в долларах, которые превышают это число в ячейках с C3 по C12, по формуле = СУММЕСЛИ (C3: C12, «> 70 000») .

12. ОТДЕЛКА

Формула TRIM в Excel обозначается = TRIM (текст) . Эта формула удалит все пробелы, введенные до и после текста, введенного в ячейку.Например, если A2 включает имя «Стив Петерсон» с нежелательными пробелами перед именем, = TRIM (A2) вернет «Стив Петерсон» без пробелов в новой ячейке.

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

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

• Формула : = ОБРЕЗАТЬ (текст).
  • Текст: Текст или ячейка, из которых вы хотите удалить пробелы.

Вот пример того, как мы использовали функцию TRIM для удаления лишних пробелов перед списком имен. Для этого мы ввели = TRIM («A2») в панель формул и скопировали это для каждого имени под ним в новом столбце рядом со столбцом с нежелательными пробелами.

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

13. ЛЕВЫЙ, СРЕДНИЙ и ПРАВЫЙ

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

ЛЕВЫЙ

• Цель : используется для извлечения первых X чисел или символов в ячейке.
• Формула : = LEFT (текст, число_символов)
  
  • Текст : строка, из которой вы хотите извлечь.
  • Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого левого символа.

В приведенном ниже примере мы ввели = LEFT (A2,4) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь первые 4 символа кода.

MID

• Назначение : Используется для извлечения символов или цифр в середине в зависимости от позиции.
• Формула : = MID (текст, начальная_позиция, число_символов)
  
  • Текст : строка, из которой вы хотите извлечь.
  • Start_position : позиция в строке, с которой вы хотите начать извлечение. Например, первая позиция в строке — 1.
  • Number_of_characters : количество символов, которые вы хотите извлечь.

В этом примере мы ввели = MID (A2,5,2) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь два числа, начиная с пятой позиции кода.

ПРАВЫЙ

• Назначение: Используется для извлечения последних X чисел или символов в ячейке.
• Формула: = ПРАВО (текст, число_символов)
  
  • Текст : строка, из которой вы хотите извлечь.
  • Number_of_characters : количество символов, которые вы хотите извлечь, начиная с самого правого символа.

Для этого примера мы ввели = ПРАВО (A2,2) в ячейку B2 и скопировали его в B3: B6. Это позволило нам извлечь два последних числа кода.

14. ВПР

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

Моя коллега Рэйчел Спранг, чей учебник «Как использовать Excel» является обязательным к прочтению для всех, кто хочет учиться, использует в качестве примера список имен, адресов электронной почты и компаний.Если у вас есть список имен людей рядом с их адресами электронной почты в одной электронной таблице и список адресов электронной почты тех же людей рядом с названиями их компаний в другой, но вы хотите, чтобы имена, адреса электронной почты и названия компаний этих людей появиться в одном месте — вот тут и пригодится ВПР.

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

• Формула: ВПР (значение поиска, массив таблицы, номер столбца, [поиск диапазона])
  • Значение поиска: Идентичное значение в обеих таблицах. Выберите первое значение в своей первой таблице. В следующем примере Спрунга это означает первый адрес электронной почты в списке или ячейку 2 (C2).
  • Table Array: Диапазон столбцов на листе 2, из которого вы собираетесь извлекать данные, включая столбец данных, идентичный вашему значению поиска (в нашем примере, адреса электронной почты) на листе 1, а также столбец данные, которые вы пытаетесь скопировать на лист 1.В нашем примере это «Sheet2! A: B». «A» означает столбец A на листе 2, который является столбцом на листе 2, где перечислены данные, идентичные нашему поисковому значению (электронная почта) в листе 1. «B» означает столбец B, содержащий информацию, доступную только на листе 2, которую вы хотите перевести на лист 1.
  • Номер столбца: Массив таблицы сообщает Excel, где (какой столбец) находятся новые данные, которые вы хотите скопировать на лист 1. В нашем примере это будет столбец «Дом», второй в нашем массиве таблиц, что делает его столбцом номер 2.
  • Поиск диапазона: Используйте FALSE, чтобы обеспечить получение только точных совпадений значений.
• Формула с переменными из примера Спранга ниже: = ВПР (C2, Sheet2! A: B, 2, FALSE)

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

15. СЛУЧАЙНО ИЗМЕНИТЬ

Есть отличная статья, в которой формула RANDOMIZE в Excel сравнивается с перетасовкой колоды карт. Вся колода представляет собой столбец, а каждая карта — 52 в колоде — представляет собой ряд. «Чтобы перемешать колоду, — пишет Стив МакДоннелл, — вы можете вычислить новый столбец данных, заполнить каждую ячейку столбца случайным числом и отсортировать книгу на основе поля случайных чисел».

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

• Формула: RAND ()
  • Начните с одного столбца контактов. Затем в соседнем столбце введите «RAND ()» без кавычек, начиная со строки верхнего контакта.
• Для примера ниже: RANDBETWEEN (внизу, вверху)
  • RANDBETWEEN позволяет вам указать диапазон номеров, которые вы хотите назначить.В случае этого примера я хотел использовать от 10 до 10.
  • внизу: Наименьшее число в диапазоне.
  • верх: Наибольшее число в диапазоне,
• Формула в примере ниже: = СЛУЧМЕЖДУ (1,10)

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

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

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

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

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

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

Сочетания клавиш Excel

1.Быстро выбирайте строки, столбцы или всю таблицу.

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

Просто хотите выделить все в определенном столбце строки? Это так же просто с этими ярлыками:

Для Mac:

• Выбрать столбец = Command + Shift + Down / Up
• Выбрать строку = Command + Shift + Right / Left

Для ПК:

• Выбрать столбец = Control + Shift + Down / Up
• Выбрать строку = Control + Shift + Right / Left

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

2. Быстро откройте, закройте или создайте книгу.

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

Для Mac:

• Открыть = Команда + O
• Закрыть = Command + W
• Создать = Command + N

Для ПК:

• Открыть = Контроль + O
• Закрыть = Control + F4
• Создать = Control + N

3.Отформатируйте числа в валюту.

Есть необработанные данные, которые вы хотите превратить в валюту? Будь то зарплаты, маркетинговые бюджеты или продажа билетов на мероприятие, решение простое. Просто выделите ячейки, которые вы хотите переформатировать, и выберите Control + Shift + $ .

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

Примечание. Этот ярлык также работает с процентами. Если вы хотите пометить столбец числовых значений как «проценты», замените «$» на «%».

4. Вставьте текущую дату и время в ячейку.

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

Затем, в зависимости от того, что вы хотите вставить, выполните одно из следующих действий:

• Вставить текущую дату = Control +; (точка с запятой)
• Вставить текущее время = Control + Shift +; (точка с запятой)
• Вставить текущую дату и время = Control +; (точка с запятой) , ПРОБЕЛ, а затем Control + Shift +; (точка с запятой) .

Другие уловки Excel

1. Настройте цвет вкладок.

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

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

2. Добавьте комментарий к ячейке.

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

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

3. Скопируйте и продублируйте форматирование.

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

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

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

4.Определите повторяющиеся значения.

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

Какой бы ни была ситуация, легко обнаружить любые существующие повторяющиеся значения в вашем рабочем листе всего за несколько быстрых шагов. Для этого щелкните опцию Conditional Formatting и выберите Highlight Cell Rules> Duplicate Values ​​

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

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

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

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