Home-theater-designers
Формулы Microsoft Excel могут делать практически все. Из этой статьи вы узнаете, насколько мощными могут быть формулы Microsoft Excel и условное форматирование, с тремя полезными примерами.
Знакомство с Microsoft Excel
Мы рассмотрели несколько различных способов более эффективного использования Excel, например где найти отличные загружаемые шаблоны Excel , а также как использовать Excel как инструмент управления проектами .
Большая часть возможностей Excel заключается в формулах и правилах Excel, которые помогают автоматически управлять данными и информацией, независимо от того, какие данные вы вставляете в электронную таблицу.
Давайте разберемся, как можно использовать формулы и другие инструменты, чтобы лучше использовать Microsoft Excel.
Условное форматирование с помощью формул Excel
Одним из инструментов, который люди используют недостаточно часто, является условное форматирование. Используя формулы, правила Excel или несколько действительно простых настроек, вы можете превратить электронную таблицу в автоматизированную панель управления.
Чтобы перейти к условному форматированию, просто щелкните значок Дом вкладку и нажмите на Условное форматирование значок на панели инструментов.
В разделе «Условное форматирование» есть много параметров. Большинство из них выходит за рамки данной статьи, но большинство из них посвящено выделению, окраске или затенению ячеек на основе данных в этой ячейке.
Это, вероятно, наиболее распространенное использование условного форматирования - например, окрашивание ячейки в красный цвет с использованием формул «меньше или больше». Узнайте больше о том, как использовать операторы ЕСЛИ в Excel.
Одним из менее используемых инструментов условного форматирования является Наборы иконок вариант, который предлагает отличный набор значков, которые вы можете использовать, чтобы превратить ячейку данных Excel в значок отображения панели мониторинга.
Когда вы нажимаете на Управление правилами , это приведет вас к Диспетчер правил условного форматирования .
как выделить больше видеопамяти
В зависимости от данных, которые вы выбрали перед выбором набора значков, вы увидите ячейку, указанную в окне «Диспетчер», с только что выбранным набором значков.
Когда вы нажимаете на Изменить правило , вы увидите диалог, в котором происходит волшебство.
Здесь вы можете создать логическую формулу и уравнения, которые будут отображать нужный значок панели инструментов.
В этом примере приборной панели будет отображаться время, потраченное на разные задачи, в сравнении с запланированным временем. Если вы превысите половину бюджета, загорится желтый свет. Если вы полностью превысили бюджет, он станет красным.
Как видите, эта панель управления показывает, что временное бюджетирование не удается.
Почти половина времени уходит больше, чем предусмотрено в бюджете.
Пора переориентировать и лучше управлять своим временем!
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 теперь использует Дата столбец и возвращает данные из Задача столбец, даже если задача находится слева.
приложение gps для android без интернета
Теперь, когда вы знаете этот лакомый кусочек, просто представьте, что еще вы можете сделать!
Если вы пытаетесь выполнить другие расширенные задачи поиска данных, ознакомьтесь с этой статьей о поиск данных в 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))И вставил его во все места в ЛЕВЫЙ функция выше, где есть 'A2'.
Это правильно извлекает вторую часть строки.
Можете ли вы вернуть деньги за игры на ps4
Для каждого последующего участка строки необходимо создать новое гнездо. Теперь все, что вам нужно сделать, это взять ' ПРАВИЛЬНО 'уравнение, которое вы создали в последнем разделе, и вставьте его в новую формулу ПРАВА с предыдущей формулой ПРАВО, вставленной в нее там, где вы видите «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
Делиться Делиться Твитнуть Эл. адрес 7 основных финансовых функций в ExcelНезависимо от того, являетесь ли вы бухгалтером или специалистом в области финансов, вы должны знать эти формулы Excel.
Читать далее Похожие темы- Продуктивность
- Советы по работе с таблицами
- Майкрософт Эксель
- Microsoft Office 2016
- Советы по Microsoft Office
- Microsoft Office 2019
Райан имеет степень бакалавра электротехники. Он проработал 13 лет в области автоматизации, 5 лет в ИТ, а теперь работает инженером по приложениям. Бывший управляющий редактор MakeUseOf, он выступал на национальных конференциях по визуализации данных и выступал на национальном телевидении и радио.
Ещё от Ryan DubeПодписывайтесь на нашу новостную рассылку
Подпишитесь на нашу рассылку технических советов, обзоров, бесплатных электронных книг и эксклюзивных предложений!
Нажмите здесь, чтобы подписаться