3 безумных формулы Microsoft Excel, которые чрезвычайно полезны
Формулы Microsoft Excel могут делать практически все. Из этой статьи вы узнаете, насколько мощными могут быть формулы Microsoft Excel и условное форматирование, с тремя полезными примерами.
Знакомство с Microsoft Excel
Мы рассмотрели несколько различных способов более эффективного использования Excel, например, где найти отличные загружаемые шаблоны Excel и как использовать Excel в качестве инструмента управления проектами .
Большая часть возможностей Excel заключается в формулах и правилах Excel, которые помогают автоматически управлять данными и информацией, независимо от того, какие данные вы вставляете в электронную таблицу.
Давайте разберемся, как можно использовать формулы и другие инструменты, чтобы лучше использовать Microsoft Excel.
Условное форматирование с помощью формул Excel
Одним из инструментов, который люди используют недостаточно часто, является условное форматирование. Используя формулы, правила Excel или несколько действительно простых настроек, вы можете превратить электронную таблицу в автоматизированную панель управления.
Чтобы перейти к условному форматированию, просто щелкните вкладку « Главная » и щелкните значок панели инструментов « Условное форматирование» .
В разделе «Условное форматирование» есть много параметров. Большинство из них выходит за рамки данной статьи, но большинство из них посвящено выделению, окраске или затенению ячеек на основе данных в этой ячейке.
Это, вероятно, наиболее распространенное использование условного форматирования – например, окрашивание ячейки в красный цвет с использованием формул «меньше или больше». Узнайте больше о том, как использовать операторы IF в Excel .
Одним из менее используемых инструментов условного форматирования является параметр « Наборы значков» , который предлагает отличный набор значков, которые можно использовать для превращения ячейки данных Excel в значок отображения панели мониторинга.
Когда вы нажмете « Управление правилами» , вы попадете в Диспетчер правил условного форматирования .
В зависимости от данных, которые вы выбрали перед выбором набора значков, вы увидите ячейку, указанную в окне «Диспетчер», с только что выбранным набором значков.
Когда вы нажмете на Edit Rule , вы увидите диалоговое окно, в котором происходит волшебство.
Здесь вы можете создать логическую формулу и уравнения, которые будут отображать нужный значок на панели инструментов.
В этом примере приборной панели будет отображаться время, потраченное на разные задачи, в сравнении с запланированным временем. Если вы превысите половину бюджета, загорится желтый свет. Если вы полностью превысили бюджет, он станет красным.
Как видите, эта панель управления показывает, что временное бюджетирование не удается.
Почти половина времени уходит больше, чем предусмотрено в бюджете.
Пора переориентировать и лучше управлять своим временем!
1. Использование функции VLookup
Если вы хотите использовать более продвинутые функции Microsoft Excel, вот пара, которую вы можете попробовать.
Вы, вероятно, знакомы с функцией VLookup, которая позволяет вам искать в списке определенный элемент в одном столбце и возвращать данные из другого столбца в той же строке, что и этот элемент.
К сожалению, функция требует, чтобы элемент, который вы ищете в списке, находился в левом столбце, а данные, которые вы ищете, – справа, но что, если они будут переключены?
В приведенном ниже примере, что, если я хочу найти Задачу, которую я выполнил 25.06.2018, по следующим данным?
В этом случае вы просматриваете значения справа и хотите вернуть соответствующее значение слева.
Если вы читаете форумы профессиональных пользователей Microsoft Excel, вы обнаружите, что многие люди говорят, что это невозможно с VLookup. Для этого вы должны использовать комбинацию функций Index и Match. Это не совсем так.
Вы можете заставить VLookup работать таким образом, вложив в него функцию CHOOSE. В этом случае формула Excel будет выглядеть так:
"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"Эта функция означает, что вы хотите найти дату 25.06.2013 в списке поиска, а затем вернуть соответствующее значение из индекса столбца.
В этом случае вы заметите, что индекс столбца равен «2», но, как вы можете видеть, столбец в приведенной выше таблице на самом деле равен 1, верно?
Это правда, но то, что вы делаете с функцией « ВЫБРАТЬ », – это манипулирование двумя полями.
Вы назначаете ссылочные "индексные" номера диапазонам данных – назначаете даты индексному номеру 1, а задачам – индексному номеру 2.
Итак, когда вы набираете «2» в функции VLookup, вы фактически имеете в виду индекс номер 2 в функции CHOOSE. Круто, правда?
VLookup теперь использует столбец Date и возвращает данные из столбца Task , даже если Task находится слева.
Теперь, когда вы знаете этот лакомый кусочек, просто представьте, что еще вы можете сделать!
Если вы пытаетесь выполнить другие задачи расширенного поиска данных, ознакомьтесь с этой статьей о поиске данных в Excel с помощью функций поиска .
2. Вложенная формула для синтаксического анализа строк
Вот вам еще одна безумная формула Excel! Могут быть случаи, когда вы импортируете данные в Microsoft Excel из внешнего источника, состоящего из строки данных с разделителями.
После того, как вы введете данные, вы захотите проанализировать их на отдельные компоненты. Вот пример информации об имени, адресе и номере телефона, разделенных знаком ";" персонаж.
Вот как вы можете проанализировать эту информацию с помощью формулы Excel (посмотрите, сможете ли вы мысленно проследить за этим безумием):
Для первого поля, чтобы извлечь крайний левый элемент (имя человека), вы должны просто использовать функцию LEFT в формуле.
"=LEFT(A2,FIND(";",A2,1)-1)"Вот как работает эта логика:
- Ищет текстовую строку из A2
- Находит ";" символ-разделитель
- Вычитает единицу для правильного расположения конца этой строковой секции
- Захватывает крайний левый текст до этой точки
В этом случае крайний левый текст – «Райан». Миссия выполнена.
3. Вложенная формула в Excel
А как насчет других разделов?
Могут быть более простые способы сделать это, но поскольку мы хотим попытаться создать самую безумную из возможных вложенных формул Excel (которая действительно работает), мы собираемся использовать уникальный подход.
Чтобы извлечь части справа, вам нужно вложить несколько функций RIGHT, чтобы захватить часть текста до этого первого символа ";" символ и снова выполните для него функцию ВЛЕВО. Вот как это выглядит при извлечении части адреса из числа улиц.
"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"Выглядит безумно, но собрать воедино несложно. Все, что я сделал, это взял эту функцию:
RIGHT(A2,LEN(A2)-FIND(";",A2))И вставил его во все места функции LEFT выше, где есть "A2".
Это правильно извлекает вторую часть строки.
Для каждого последующего участка строки необходимо создать новое гнездо. Теперь все, что вам нужно сделать, это взять уравнение « ПРАВИЛЬНО », которое вы создали в предыдущем разделе, и вставить его в новую формулу ПРАВИЛЬНО с предыдущей формулой ПРАВО, вставленной в нее, где вы видите «A2». Вот как это выглядит.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))Затем вам нужно взять ЭТУ формулу и поместить ее в исходную ЛЕВУЮ формулу везде, где есть "A2".
Итоговая формула сногсшибательной выглядит так:
"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"Эта формула правильно извлекает «Portland, ME 04076» из исходной строки.
Чтобы извлечь следующий раздел, повторите описанный выше процесс снова.
Формулы Excel могут быть очень запутанными, но все, что вы делаете, – это вырезаете и вставляете длинные формулы в самих себя, создавая длинные гнезда, которые все еще работают.
Да, это соответствует требованию «сумасшедший». Но давайте будем честными, есть гораздо более простой способ сделать то же самое с помощью одной функции.
Просто выберите столбец с данными с разделителями, а затем в пункте меню « Данные» выберите « Текст в столбцы» .
Это вызовет окно, в котором вы можете разделить строку любым разделителем, который вам нужен. Просто введите ' ; 'и вы увидите, что предварительный просмотр выбранных данных изменится соответствующим образом.
За пару кликов вы можете сделать то же самое, что и эта безумная формула выше … но где в этом веселье?
С ума сойти с формулами Microsoft Excel
Вот и все. Вышеупомянутые формулы доказывают, насколько безупречным может оказаться человек при создании формул Microsoft Excel для выполнения определенных задач.
Иногда эти формулы Excel на самом деле не самый простой (или лучший) способ добиться чего-то. Большинство программистов посоветуют вам сохранять простоту, и это верно как для формул Excel, так и для всего остального.
Если вы действительно хотите серьезно подойти к использованию Excel, вам стоит прочитать наше руководство для начинающих по использованию Microsoft Excel . В нем есть все необходимое, чтобы начать повышать вашу продуктивность с помощью Excel. После этого обязательно ознакомьтесь с нашей памяткой по основным функциям Excel для получения дополнительных рекомендаций.
Кредит изображения: kues / Depositphotos