25 формул Excel и сочетаний клавиш для экономии времени и усилий

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

Почему Excel стал необходимостью каждого бизнеса?

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

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

Запись данных, контроль финансов и создание отчетов — отличные платформы для создания графиков и диаграмм.

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

Кроме того, в нем есть сводные таблицы, графические инструменты и язык программирования макросов Visual Basic for Applications. Это электронная таблица, широко используемая в операционных системах Microsoft Windows, Mac OS X, iOS и Android.

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

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

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

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

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

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

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

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

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

Давайте рассмотрим несколько наиболее часто используемых формул Microsoft Excel.

МНОЖЕСТВО

Формула Excel под названием МАССИВ используется для выполнения сложных вычислений, которые часто невозможно выполнить вручную.

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

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

Иллюстрация массива с одной ячейкой

Как показано ниже, формула массива умножает акции из ячеек C3 в G3 и их цены из ячеек C4 в G4. Наконец, складывает эти результаты, чтобы получить общее значение 14400.

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

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

Пользователи могут использовать функции МАССИВА рабочей книги Excel для выполнения различных вычислений. Более подробную информацию об использовании массивов можно получить в Microsoft.

СРЕДНИЙ

Функция СРЗНАЧ (среднее арифметическое) в Excel используется для вычисления среднего значения данных, как следует из ее названия. Среднее значение этих конкретных данных предоставляется в строке или столбце путем сложения всех числовых значений и деления результата на количество числовых значений.

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

Давайте посмотрим, как найти среднюю цену акции на листе Excel ниже.

Поместите курсор туда, где вы хотите вставить среднее значение, перейдите в меню Автосумма в верхнем левом углу Excel и выберите СРЗНАЧ , как показано на листе Excel ниже.

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

Видео Microsoft также может научить вас понимать функцию среднего.

СЧИТАТЬ

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

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

Например, вы можете ввести формулу =COUNT, чтобы подсчитать числа между D4 и D8 (D4:D8). Функция COUNT возвращает четыре, так как этот диапазон содержит четыре ячейки с числами, как показано в примере ниже:

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

СВИДАНИЕ

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

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

Синтаксис формулы по умолчанию: =ДАТА (год, месяц, день). На изображении ниже показано, как работает формула ДАТА в Microsoft Excel.

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

РАЗДЕЛЕНИЕ

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

Давайте рассмотрим пример, чтобы понять, как работает функция ДЕЛЕНИЕ в Excel.

Формула =D/10 будет использоваться для деления значений из столбца D на 10, как показано ниже. Эту же формулу можно применить ко всем ячейкам, если перетащить угол ячейки E4 вниз ко всем остальным ячейкам.

Формула деления будет =C4/D4, как показано ниже, чтобы разделить числа в ячейке C4 на числа в ячейке D4.

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

ЕСЛИ

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

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

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

Функция ЕСЛИ в Excel выполняет логический тест и возвращает значение или текст как для результата ИСТИНА, так и для результата ЛОЖЬ.

Его синтаксис по умолчанию — ЕСЛИ (логический тест, значение, если оно истинно, значение, если оно ложно).

Давайте рассмотрим лист Excel pic1 ниже, чтобы понять функцию ЕСЛИ:

Задача состоит в том, чтобы выявить учащихся, набравших более 50 баллов, и отметить их как «Удачно», а учеников, набравших менее 50 баллов, следует отметить как «Неудовлетворительно».

Оператор IF, как показано ниже на Рисунке 1, проверяет баллы учащегося в столбце D с числом 50, и если балл выше 50, он помечается как Pass; если ниже 50, он будет вставлен как Fail. Pass и fail вставляются в столбец результатов.

Вот еще одна иллюстрация того, как оператор ЕСЛИ может быть применен к управлению проектами или продажам:

Задача состоит в том, чтобы найти проекты, которые укладываются в бюджет и превышают бюджет, в файле Excel ниже.

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

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

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

Чтобы извлечь определенный символ, слово или число из ячейки Excel, используйте команды ВЛЕВО, СРЕДНЯЯ и ВПРАВО.

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

Упражнение. Извлеките Имя из Полного имени, используя функцию LEFT.

Синтаксис функции LEFT: =LEFT (text , num)

Как видно из приведенной ниже электронной таблицы Excel, в нашем примере используется =ЛЕВО(B4,3), а имя Сэм извлекается и вводится в ячейку D4. Первое имя с последней позиции слева представлено цифрой 3.

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

Поскольку имя Рикки состоит из пяти букв, ваша формула будет =ЛЕВО(B5,5), если вы хотите извлечь имя из B5.

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

Синтаксис функции MID: =MID(текст, номер начала, номер конца)

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

=СРЕДНЕЕ(B4,5,11).

D Дерлинга начинается на счете пять, а G Дерлинга заканчивается на счете 11. В формуле используются числа 5 и 11. Дерлинг будет извлечен как второе имя и введен в ячейку E4.

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

Наконец, мы извлечем ' ext.' число из столбца C с использованием функции RIGHT

Синтаксис функции RIGHT: =RIGHT (текст, конечная цифра)

Посмотрите на лист Excel ниже, чтобы увидеть, как работает функция ПРАВИЛЬНО.

Формула будет =RIGHT(C4,2) .  

Цифра 2 — это конечное место доп. справа. Как видно на изображении ниже, расширение 88 будет удалено и помещено в ячейку F4.

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

Чтобы быстро извлечь информацию из обширной базы данных, такую ​​как контакты, адреса, списки и т. д., используйте функции ВЛЕВО, СРЕДНЯЯ и ВПРАВО.

УМНОЖЕНИЕ

Еще одна простая операция Excel — умножение чисел с использованием символа звездочки (*).

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

Например, если вы введете формулу = 11*5 и нажмете Enter, ячейка покажет ответ как 55.

Возьмем еще одну иллюстрацию.

Если вы хотите умножить число в столбце C на число в столбце D, тогда формула будет =C5*D5 , как показано ниже на листе Excel.

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

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

PRODUCT также может использоваться для обозначения последовательных ячеек, разделенных двоеточием (:), как показано на рисунке 1.

Есть несколько способов использовать функцию умножения Excel.

Формула для умножения одного постоянного целого числа на весь столбец: =C5$D$5 , как показано на листе Excel ниже.

В приведенном ниже примере все числа в столбце C умножаются на постоянное число 8 из столбца D.

Символы доллара ($) сообщают Excel, что ссылка на D5 является «абсолютной», что означает, что ссылка всегда будет D5, когда вы копируете формулу в другую ячейку.

ПРОЦЕНТ

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

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

Excel вычисляет проценты, разделяя данные и превращая их в проценты с помощью сочетания клавиш Ctrl+Shift+% или знака процента (%) , найденного в группе «Числа» на вкладке «Главная», как показано на изображении ниже.

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

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

Как показано ниже, столбец I содержит оценки, полученные учащимися, а общие баллы (100 баллов по каждому предмету x 4) представлены в столбце J.

Теперь давайте рассмотрим процесс расчета процентов.

Процент рассчитывается по следующей формуле.

= I5/J5 ( полученные баллы / общее количество баллов)

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

Перейдите на вкладку «Главная», выберите группу «Число», как показано на изображении ниже, и щелкните знак процента (%) или нажмите CTRL+SHIFT+% , чтобы преобразовать эти десятичные дроби в проценты. На изображении Excel выше в столбце L отображаются результаты расчета.

Нужно быть знакомым с форматированием ячеек, чтобы понимать десятичные дроби в функции ПРОЦЕНТ. Посетите страницу Microsoft, чтобы увидеть предварительный просмотр.

РАНДОМИЗАЦИЯ

В Microsoft Excel нет точной функции для РАНДОМИЗАЦИИ элементов списка.

Функция RAND создает случайные числа в качестве основы для рандомизации вашего списка в порядке возрастания и убывания, если вы хотите RANDOMIZE что-либо.

Поместите функцию RAND в столбец рядом со списком для RANDOMIZE. Затем отсортируйте эти случайные числа в порядке возрастания или убывания, что приведет к смене позиций элементов списка.

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

Чтобы лучше понять, как работает функция RAND, рассмотрим пример ниже.

Установите курсор рядом со списком столбцов, затем введите =RAND() .

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

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

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

ВЫЧИТАНИЕ

Простейшими задачами в Microsoft Excel являются сложение и вычитание чисел.

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

Формула начинается со знака равенства (=), выбирает ячейку для вычитания, вводит знак минус (-) и выбирает следующую ячейку.

Формула вычитания будет продублирована во всех ячейках, если вы перетащите угол ячейки E4.

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

Давайте посмотрим на приведенный ниже пример, чтобы понять

Формула будет =C5-$D$5 , как показано на листе Excel ниже. Вы можете ввести эту формулу в соседний столбец. Перемещая угол ячейки E5, вы можете скопировать эту формулу в другие ячейки.

СУММА

В Excel функция СУММ объединяет несколько чисел, добавляет одно число к диапазону чисел, складывает числа из одного столбца в другой и т. д.

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

Формула суммы начинается со знака равенства (=), как показано на листе Excel ниже.

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

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

Давайте воспользуемся тем же примером, что и ранее, и сложим все числа одновременно.

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

СУММЕСЛИ

В Excel функция СУММЕСЛИ складывает числа, соответствующие определенным условиям.

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

Теперь давайте посмотрим, как осуществить этот расчет.

Задайте диапазон ячеек и условие добавления; в этом случае мы хотим добавить только значения больше 11.

Как видно из приведенного ниже примера, формула для выполнения этой операции =СУММЕСЛИ (C4:C10, «>11») .

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

ПОДРЕЗАТЬ

Дополнительные начальные или конечные пробелы удаляются с помощью TRIM .

Формула TRIM обычно начинается со знака равенства (=), за которым следует информация о ячейке и Enter.

=ОТРЕЗАТЬ (C7)

Как видно на изображении ниже, функция TRIM удаляет начальные, лишние и конечные пробелы из ячейки C7.

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

ВПР

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

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

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

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

Задача состоит в том, чтобы найти статус результата Маргарет Адлеман, чей номер броска равен 3.

Уравнение для выполнения этого действия:

=ВПР(G5, B4:E9,4, ЛОЖЬ)

Давайте разберемся в деталях синтаксиса

• G5 : эта ячейка содержит условие поиска или число, которое вы хотите найти в большой таблице или рабочей книге.
• B4:E9 : это диапазон таблиц, в котором выполняется поиск с помощью функции ВПР.
• 4: дает указание функции ВПР вернуть значение из четвертого столбца таблицы в той же строке.
• FALSE: возвращает ошибку, если совпадение не найдено.

Как видно из рабочего листа ниже, функция ВПР идентифицировала статус результата Маргарет Адлеман как Неудачный в ячейке G6.

Используйте XLOOKUP, обновленную версию VLOOKUP, если у вас Excel 365 или Excel 2021. Дополнительные преимущества функции XLOOKUP включают возможность поиска в любом направлении и предоставление точных совпадений по умолчанию. Кроме того, он проще и удобнее в использовании.

Что такое ярлыки Excel?

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

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

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

Давайте посмотрим на некоторые популярные сочетания клавиш Excel.

Быстро выбрать строки или столбцы

Щелкните алфавит в верхней части столбца, чтобы быстро выбрать весь столбец, или выберите любую ячейку и нажмите Ctrl + Space , чтобы выбрать весь столбец.

Нажмите на серийные номера в дальнем правом углу листа, чтобы быстро выбрать всю строку, выберите любую ячейку и нажмите Shift + Space, чтобы выбрать всю строку.

Клавиша управления (Ctrl) при щелчке по нужным строкам или столбцам будет выбирать случайные.

Формат чисел

Ctrl+Shift+1 [!] — это сочетание клавиш для применения стандартного числового формата.

Мгновенная работа над рабочей тетрадью

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

Ctrl + N , чтобы начать новый рабочий лист

Ctrl + O откроет существующий рабочий лист.

Ctrl + S Команда сохранения книги

Ctrl + W закроет текущий рабочий лист.

Ctrl+PageDown перейдет к следующему листу.

Ctrl+PageUp перенесет вас на предыдущий лист.

Alt + A перенесет вас на вкладку «Данные».

Alt + W перенесет вас на вкладку «Вид».

Alt + M откроет вкладку Формула.

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

Нажмите Ctrl+; (точка с запятой), чтобы добавить текущую дату.

Нажмите Ctrl+Shift+; (точка с запятой), чтобы вставить текущее время.

Копировать в соседние ячейки

Ctrl+R скопирует значения или формулы в соседнюю ячейку столбца.

Ctrl+D скопирует значения или формулы в ячейку следующей строки.

Игнорирование пустых ячеек при редактировании

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

1. Используйте функцию ЕСЛИ, чтобы игнорировать пустые ячейки в диапазоне.

2. Использование функции ЕПУСТО в Excel для игнорирования пустых ячеек в диапазоне.

3. Функция IНОМЕР.

4. Использование функции COUNT Excel для игнорирования пустых ячеек в диапазоне.

Копировать и вставить несколько вариантов

Нажмите Shift + клавишу со стрелкой вниз: можно выбрать несколько ячеек.

Ctrl+C скопирует несколько ячеек.

Ctrl+V используется для вставки множества ячеек.

Вернуться к предыдущим местоположениям

Диалоговое окно Go-To, из которого вы можете перейти в предыдущее местоположение, появится при использовании клавиши F5 или Ctrl + G.

Изменить формулу на значение

Преобразование формулы в значение выполняется в три этапа:

Шаг 1. Выделите ячейки, которые хотите преобразовать.

Шаг 2: Нажмите Ctrl + C

Шаг 3: Shift + F10 и V вместе.

Вход в ячейку и удаление данных

Шаг 1: Ввод данных в ячейку

Шаг 2: Введите необходимый текст или числа для ввода данных и нажмите ENTER или TAB .

Шаг 3: Потяните маркер заполнения (угол ячейки), чтобы автоматически заполнить данные в других ячейках листа.

Шаг 4: Введите разрыв строки, нажав ALT+ENTER , чтобы начать новую строку данных в ячейке.

Шаг 5: Чтобы полностью очистить данные и информацию листа Excel, перейдите на вкладку «Главная», в группу «Редактирование», затем нажмите кнопку «Очистить» и выберите « Очистить все ».

Заключительные слова

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

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

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