Microsoft Excel — один из самых мощных инструментов работы с электронными таблицами, с впечатляющим набором встроенных инструментов и функций. В этой статье вы узнаете, насколько мощными могут быть формулы Excel и условное форматирование, на трех полезных примерах.
Разблокируйте шпаргалку «Основные ярлыки Microsoft Office» прямо сейчас!
Это подпишет вас на нашу рассылку
Введите адрес электронной почты
отпереть
Прочитайте нашу политику конфиденциальности
Копаем в Microsoft Excel
Мы рассмотрели ряд различных способов более эффективного использования Excel, таких как использование его для создания собственного шаблона календаря или использование его в качестве инструмента управления проектами.
Большая часть возможностей лежит в основе формул и правил Excel, которые вы можете написать, чтобы автоматически манипулировать данными и информацией, независимо от того, какие данные вы вставляете в электронную таблицу.
Давайте углубимся в то, как вы можете использовать формулы и другие инструменты для более эффективного использования Microsoft Excel.
Условное форматирование с формулами
Одним из инструментов, который люди не используют достаточно часто, является условное форматирование. Если вам нужна более подробная информация об условном форматировании в Microsoft Excel, обязательно ознакомьтесь со статьей Сэнди о форматировании данных в Microsoft Excel с условным форматированием.
С помощью формул, правил или нескольких простых настроек Excel вы можете преобразовать электронную таблицу в автоматизированную панель мониторинга.
Чтобы перейти к условному форматированию, просто нажмите на Главная вкладку и нажмите на Условное форматирование значок панели инструментов.
Под условным форматированием есть много вариантов. Большинство из них выходят за рамки этой конкретной статьи, но большинство из них о выделении, окрашивании или затенении ячеек на основе данных в этой ячейке.
Вероятно, это наиболее распространенное использование условного форматирования — например, окрашивание красной ячейки с использованием формул меньше или больше, чем. Узнайте больше о том, как использовать операторы IF в Excel.
Одним из менее используемых инструментов условного форматирования является Наборы иконок опция, которая предлагает большой набор значков, которые вы можете использовать, чтобы превратить ячейку данных Excel в значок панели инструментов.
Когда вы нажимаете на Управлять правилами, это приведет вас к Менеджер правил условного форматирования.
В зависимости от данных, выбранных до выбора набора значков, вы увидите ячейку, указанную в окне диспетчера, с выбранным набором значков.
Когда вы нажимаете на Изменить правило, вы увидите диалог, в котором происходит волшебство.
Здесь вы можете создать логическую формулу и уравнения, которые будут отображать нужный значок на панели инструментов.
В этом примере панель инструментов будет показывать время, потраченное на различные задачи, в сравнении с запланированным временем. Если вы превысите половину бюджета, появится желтый индикатор. Если у вас полностью превышен бюджет, он станет красным.
Как вы видите, эта панель показывает, что бюджетирование времени не является успешным.
Почти половина времени уходит намного больше запланированных сумм.
Время перефокусироваться и лучше управлять своим временем!
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 использует Дата столбец и возвращает данные из задача столбец, хотя задание слева.
Теперь, когда вы знаете этот маленький кусочек, просто представьте, что еще вы можете сделать!
Если вы пытаетесь выполнить другие расширенные задачи поиска данных, обязательно ознакомьтесь с полной статьей Данна о поиске данных в 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, как и для всего остального.
Если вы действительно хотите серьезно относиться к использованию Excel, вам нужно прочитать наше руководство для начинающих по использованию Microsoft Excel
, В нем есть все, что нужно для повышения производительности с помощью Excel.
