Формулы Excel могут сделать почти все. В этой статье вы узнаете, насколько мощными могут быть формулы Microsoft Excel и условное форматирование, на трех полезных примерах.
Копаем в Microsoft Excel
Мы рассмотрели ряд различных способов более эффективного использования Excel, таких как использование его для создания собственного шаблона календаря или использование его в качестве инструмента управления проектами .
Большая часть возможностей лежит в основе формул и правил Excel, которые вы можете написать, чтобы автоматически манипулировать данными и информацией, независимо от того, какие данные вы вставляете в электронную таблицу.
Давайте разберемся, как вы можете использовать формулы и другие инструменты для более эффективного использования Microsoft Excel.
Условное форматирование с формулами Excel
Одним из инструментов, который люди не используют достаточно часто, является условное форматирование. Если вам нужна более подробная информация об условном форматировании в Microsoft Excel, обязательно ознакомьтесь со статьей Сэнди о форматировании данных в Microsoft Excel с условным форматированием .
С помощью формул, правил или нескольких простых настроек Excel вы можете преобразовать электронную таблицу в автоматизированную панель мониторинга.
Чтобы перейти к условному форматированию, просто нажмите на вкладку « Главная » и щелкните значок на панели инструментов условного форматирования .
Под условным форматированием есть много вариантов. Большинство из них выходят за рамки этой конкретной статьи, но большинство из них о выделении, окрашивании или затенении ячеек на основе данных в этой ячейке.
Это, вероятно, наиболее распространенное использование условного форматирования — например, окрашивание красной ячейки с использованием формул меньше или больше, чем.
Одним из наименее используемых инструментов условного форматирования является параметр « Наборы значков» , который предлагает большой набор значков, которые можно использовать, чтобы превратить ячейку данных Excel в значок отображения на приборной панели.
Когда вы нажмете кнопку « Управление правилами» , вы попадете в диспетчер правил условного форматирования .
В зависимости от данных, выбранных до выбора набора значков, вы увидите ячейку, указанную в окне диспетчера, с выбранным набором значков.
Когда вы нажмете на Edit Rule , вы увидите диалог, в котором происходит волшебство.
Здесь вы можете создать логическую формулу и уравнения, которые будут отображать нужный значок на панели инструментов.
В этом примере панель инструментов будет отображать время, потраченное на различные задачи, в сравнении с запланированным временем. Если вы превысите половину бюджета, появится желтый индикатор. Если вы полностью превысили бюджет, он станет красным.
Как вы можете видеть, эта панель показывает, что бюджетирование времени не является успешным.
Почти половина времени уходит намного больше запланированных сумм.
Время перефокусироваться и лучше управлять своим временем !
1. Использование функции VLookup
Если вы хотите использовать более продвинутые функции Microsoft Excel, то вот еще одна для вас.
Вы, вероятно, знакомы с функцией VLookup, которая позволяет искать в списке определенный элемент в одном столбце и возвращать данные из другого столбца в той же строке, что и этот элемент.
К сожалению, функция требует, чтобы элемент, который вы ищете в списке, находился в левом столбце, а данные, которые вы ищете, были справа, но что, если они переключаются?
В приведенном ниже примере, что, если я хочу найти задачу, которую я выполнил 25.06.2008, по следующим данным?
В этом случае вы просматриваете значения справа и хотите вернуть соответствующее значение слева — в противоположность тому, как обычно работает VLookup .
Если вы читаете форумы для пользователей 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(A2,FIND(";",A2,1)-1)"
Вот как работает эта логика:
- Ищет текстовую строку из A2
- Находит символ разделителя «;»
- Вычитает один для правильного расположения конца этого раздела строки
- Захватывает самый левый текст к этой точке
В этом случае самый левый текст — «Райан». Миссия выполнена.
3. Вложенная формула в Excel
Но как насчет других разделов?
Возможно, есть более простые способы сделать это, но, поскольку мы хотим попробовать создать самую сумасшедшую формулу вложенного Excel (которая действительно работает), мы будем использовать уникальный подход.
Чтобы извлечь части справа, вам нужно вложить несколько правых функций, чтобы захватить фрагмент текста до первого символа «;», и снова выполнить функцию «ВЛЕВО» над ним. Вот как это выглядит для извлечения части адреса из номера улицы.
"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"
Это выглядит сумасшедшим, но это не сложно собрать воедино. Все, что я сделал, взял эту функцию:
RIGHT(A2,LEN(A2)-FIND(";",A2))
И вставил его в каждое место в левой функции выше, где есть «A2».
Это правильно извлекает второй раздел строки.
Каждому последующему разделу строки необходимо создать другое гнездо. Итак, теперь вы просто берете сумасшедшее «ПРАВИЛЬНОЕ» уравнение, которое вы создали для последнего раздела, а затем передаете его в новую ПРАВИЛЬНУЮ формулу с предыдущей ПРАВИЛЬНОЙ формулой, вставленной в себя, где бы вы ни увидели «А2». Вот как это выглядит.
(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))
Затем вы берете формулу ТА и помещаете ее в оригинальную ЛЕВУЮ формулу, где есть «А2».
Окончательная формула для сгибания ума выглядит следующим образом:
"=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 это так же верно, как и с остальными. Вы даже можете использовать встроенные функции, такие как Power Query . Начните с чтения нашего руководства, чтобы создать свой первый скрипт Microsoft Power Query .
Если вы действительно хотите серьезно относиться к использованию Excel, вам нужно прочитать наше руководство для начинающих по использованию Microsoft Excel В нем есть все, что нужно для повышения производительности с помощью Excel. После этого обязательно ознакомьтесь с нашими основными таблицами функций Excel листы листы