Check-moscow.ru

Финансы и учет
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Exceltip

Excel имеет различные функции, включая вычисление косинуса дуги заданного значения, перемножение двух матриц и оценку внутренней нормы прибыли. Но большинство из нас (в том числе и я) используют для работы не более 5-6 формул. И формула ЕСЛИ входит в этот список. Поэтому не помешает узнать еще несколько интересных вещей, которые можно сделать с помощью всего одной функции ЕСЛИ.

1. Сумма альтернативных столбцов/строк

Это происходит, когда нам нужно просуммировать каждую вторую строку в столбце (не спрашивайте меня, почему, это не редкость). Конечно, мы могли бы суммировать каждую ячейку по отдельности, но это не наш вариант. Наша стратегия заключается в том, чтобы сделать себе перерыв на кофе, пока Excel будет выполнять грязную работу. Для этого нам понадобится альтернативный столбец и функция СУММЕСЛИ, которая суммирует ячейки с учетом определенного критерия. В нашем случае критерием будет «если элемент находится в нечетных строках».

1-sum-alternative-rows-columns-excel - копия

Все, что нам нужно сделать, это добавить дополнительный столбец и заполнить его единицами и нулями (введите 1 и 0 в двух строках, выделите обе ячейки и перетащите в конец таблицы). Теперь наш столбец может определять условие с помощью =SUMMESLY(диапазон условий; 1; диапазон сумм).

2. Подсчет количества повторений в листе А элементов из листа Б

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

2-using-countif-ms-excel

В приведенном выше примере я использовал функцию CALCULATE для подсчета количества клиентов в определенном городе (где список клиентов находится на листе 2, а список городов — на листе 1). Формула выглядит следующим образом: =CALCULATE(интервал условия; условие). Например, формула CURRENTLY($E$6:$E$16; «Волгоград») скажет мне, сколько клиентов находится в Волгограде.

3. Быстрый подсчет данных с помощью СЧЁТЕСЛИ и СУММЕСЛИ

Теперь, когда мы научились пользоваться формулами RATESLY и SUMMESLY, мы можем использовать их вместе.

3-summarize-data-with-countif-sumif

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

4. Поиск второго, третьего … n -ного вхождения элемента в списке EXCEL

Часто мы работаем со списком данных, в котором много дубликатов. Например, в список телефонов клиента со временем могут добавляться новые данные. Получить второе, третье… десятое появление может быть непросто. Мы можем использовать комбинации SCRATESLY и VRP, чтобы найти любое вхождение в списке.

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

Читайте так же:
Как вставить текущую дату в текстовое поле?

4-find-second-occurance-using-vlookup

После этого мы будем использовать измененные данные клиента при поиске информации о Кристине Агилере. С помощью формулы FPR(«Christina Aguilera3», search range, 2, FALSE) мы получим третий номер телефона Кристины Агилеры. Из-за того, что наш список не упорядочен по алфавиту, последний аргумент будет FALSE, так как нам нужно точное совпадение поискового значения.

5. Сокращаем количество вложений функции ЕСЛИ

В ранних версиях Excel допускалось только 7 уровней функции IF. Это ограничение было снято с Excel 2007. К счастью, большинство из нас не опускались ниже уровня 3 или 4. Вместо того чтобы писать эти многоуровневые формулы, почему бы не использовать функцию SELECT, которая позволяет выбрать значение или действие из списка значений? Синтаксис функции — SELECT(индекс_номер; значение_по_индексу_равно_1; значение_по_индексу_равно_2; значение_по_индексу_равно_2). Когда вы введете следующую функцию, SELECT(3; «Отлично»; «Хорошо»; «Плохо»), она вернет значение «Плохо». Есть одно ограничение: значение индекса должно быть указано в числовом формате, поэтому, когда значение индекса не может быть указано явно, потребуется некоторое творчество (как в следующем примере):

5-using-excel-choose-function

Как я перевел буквенное значение в число в SELECT(), оставьте для домашнего задания).

Использование индекса и MATCH вместо ВРОТ

Существует ряд ограничений, которые относятся только к использованию функции RIGHT. Это означает, что столбец, содержащий и поиск, всегда должен находиться слева от столбца, содержащего возвращаемое значение. Если ваша таблица построена не таким образом, не используйте INSERT. Вместо этого используйте комбинацию функций INDEX и MATCH.

В примере ниже показан небольшой список, в котором искомое значение (Воронеж) не является крайним левым. Поэтому мы не можем использовать функцию БПФ. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использована функция ПОИСКПОЗ. Оно находится в строке 4. INDEX использует это значение в качестве аргумента поиска и находит население Воронежа в четвертом столбце (столбец D). Формула отображается в ячейке A14.

Использование функций ИНДЕКС и ПОИСКПОЗ для поиска значения

Другие примеры использования индексов и MATCH вместо PROCESS смотрите у Билла Джелена, Microsoft MVP, Bill Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/.

Нужно выделить повторяющиеся значения в столбце? Надо выбрать первые 5 максимальных ячеек? Необходимо сделать термальную шкалу для наглядности (цвет меняется в зависимости от увеличения/уменьшения значения ячеек)? В Excel выделение цветом ячеек по условиям можно сделать очень быстро и просто. За выделение цветом ячеек отвечает специальная функция «Условное форматирование». Настоятельно рекомендую! Подробнее читаем дальше:

Excel выделение цветом

Я описал основные функции в начале статьи, но на самом деле их очень много. Узнайте больше о самых полезных из них

Читайте так же:
Как добавить префикс или суффикс в значения ячеек в таблицах Google?

Условное форматирование, где найти?

Чтобы начать работу, найдите в главном меню панели задач раздел Стили и выберите Условное форматирование.

Условное 1

При нажатии откроется меню опций редактирования. Очевидно, что здесь имеется довольно много возможностей.

Excel выделение цветом

Вот самые полезные:

Excel выделение цветом ячеек по условиям. Простые условия

Для этого перейдите в раздел Правила выбора ячеек. Если, например, вы хотите выбрать все ячейки больше 100, нажмите кнопку Еще. В окне:

Excel выделение цветом 0

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

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

Чтобы выделить все повторяющиеся значения, выберите соответствующее меню «Повторяющиеся значения».

Excel выделение цветом

Снова появится окно форматирования. Настройте его по своему усмотрению. Например, можно выделить только уникальные элементы. Форматирование значений и курсива (пользовательское)

Как найти повторения, когда задействовано несколько столбцов, например, когда имена находятся в нескольких столбцах? Поместите значения в другой столбец и добавьте формулу =CREATE(). у вас будет Иванов-Иванович в отдельной ячейке. Наличие такого столбца позволит вам легко определить повторяющиеся значения. Важно понимать, что если порядок слов отличается, Excel будет считать такие строки неповторяющимися (например, Иванов-Иванович-Иванович-Иванов).

Excel. значения повторяются

Выделение цветом первых/последних значений. Опять же условное форматирование

Excel выделение цветом 1

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

Excel выделение цветом 2

Построение термальной диаграммы и гистограммы

Классная функция для визуализации данных — термальная/температурная диаграмма. Суть в том, что в зависимости от величины значения в столбце или строке, ячейка подсвечивается определенным оттенком цвета, чем больше, тем краснее, например. Таблицы воспринимаются гораздо лучше на глаз, а принимать решение становится проще. Ведь один из лучших анализаторов зачастую — это наш глаз, соответственно, мозг, а не машина!

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

Рекомендую. Для презентаций и аналитики — гистограммы в ячейках и термальные диаграммы основа простой визуализации при помощи Excel.

Excel выделение цветом 3

Выделение цветом ячеек, содержащих определенный текст

Очень часто нужно найти ячейки, которые содержат определенный набор символов, можно конечно воспользоваться функцией = ПОИСК() , но проще и быстрее применить условное форматирование, пройдите — Правила отбора ячеек — Текст содержит

Читайте так же:
Как выделить определенный текст в ячейке на основе другого текста?

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

Excel выделение цветом. Фильтр по цвету

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

В этой статье вы можете узнать больше о фильтрах.

Excel выделение цветом 4

Проверка условий форматирования

Чтобы проверить какие условные форматирования у Вас заданы, пройдите Главная — Условное форматирование — Управление правилами. Здесь вы сможете отредактировать уже заданные условия, диапазон применения, а также выбрать приоритет заданного форматирования (кто выше, тот главнее, изменить можно кнопками — стрелками).

Excel выделение цветом 5

Неправильное условное форматирование диапазона

Важно! Условное форматирование при неправильном использовании зачастую является причиной сильных тормозов Excel . Происходит задвоение форматирований, для примера если вы много раз копируете ячейки с выделением цветом. Тогда у вас появится множество условий с цветом. Я сам видел более 3 тысяч условий — тормозил файл безобразно. Также файл может тормозить, когда задан диапазон как на картинке выше, лучше, указывать A:A — для всего диапазона.

Чтобы узнать больше о тормозах Excel и их причинах, нажмите здесь. Эта статья помогла сотням людей 😉

Статья представлена на уровне 2. Курс для пользователей среднего уровня Узнайте больше, начав с самого начала.

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

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

Типы связей: входящая и исходящая

Существует два типа клеточных соединений.

  • Если в ячейке есть входящая связь, это означает, что ячейка получает своё значение из ячейки в другой таблице.

    Ячейка, содержащая входящую связь, является конечной ячейкой для неё, а таблица, содержащая конечную ячейку, — конечной таблицей. Конечная ячейка может содержать только одну входящую связь. Конечные ячейки обозначаются голубой стрелкой справа.
  • Если ячейка содержит исходящую связь, её значение передаётся в ячейку другой таблицы.

    Ячейка, содержащая исходящую связь, является исходной ячейкой для неё, а таблица, содержащая исходную ячейку, — исходной таблицей. Исходная ячейка может быть связана с несколькими конечными ячейками. Исходные ячейки обозначаются серой стрелкой в правом нижнем углу.
Читайте так же:
Как вывести список всех сводных таблиц из книги?

Чтобы отобразить имя таблицы для входящей или исходящей связи, выберите соответствующую ячейку:

Выделив ячейку и наведя курсор на появившийся текст, щелкните по ссылке на таблицу, значение которой используется во входящем или исходящем сообщении:

Наведите курсор мыши на появившееся информационное окно и нажмите кнопку Удалить, чтобы удалить входящее или исходящее сообщение.

Создание входящей связи ячеек

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

  1. Откройте конечную таблицу.
  2. Выберите ячейку и на панели инструментов щёлкните Связывание ячеек, чтобы открыть форму связывания ячеек.

Рекомендации по эффективному использованию связей ячеек

При создании входящего вызова в таблице источников автоматически создается исходящий вызов.

Можно выделить несколько ячеек, чтобы создать ссылку в каждой.

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

Из одной исходной таблицы ссылки могут быть созданы из 500 ячеек, а итоговая таблица может содержать до 20 000 исходящих ссылок.

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

Изменение и удаление связей

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

Входящие связи

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

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

  1. Щёлкните ячейку, содержащую входящую связь (или удерживайте нажатой кнопку мыши и потяните рамку, чтобы выделить группу ячеек).
  2. Щёлкните ячейку правой кнопкой мыши и выберите пункт Удалить ссылку.

Исходящие связи

Необходимо удалять входящие соединения по одному. Для удаления исходящего сообщения выполните следующие действия:

Ссылка «удалить»

  1. Выберите исходную ячейку в таблице с исходящей ссылкой.
  2. Наведите курсор мыши на связанную ячейку, чтобы отобразить ссылку «удалить».
  3. Нажмите на ссылку «Удалить».

Н О Т Е. Строки со связанными ячейками затрагиваются при их удалении. При удалении строки с исходной ячейкой связь разрывается в таблице назначения. Удаление строки со связанной конечной ячейкой также удаляет связь из исходной таблицы.

Создание связей с помощью специальной ставки (из исходной таблицы)

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

Чтобы создать ссылку с помощью функции «Пользовательская вставка», выполните следующие шаги.

  1. Откройте исходную таблицу и скопируйте ячейку или диапазон ячеек (с помощью контекстного меню или сочетаний клавиш).
  2. Откройте конечную таблицу, выберите ячейку, в которой нужно создать связь, а затем щёлкните её правой кнопкой мыши (пользователи Mac могут щёлкнуть её, удерживая нажатой клавишу CTRL) и выберите пункт Специальная вставка. Откроется форма Специальная вставка.
  3. Выберите параметр Ссылки на скопированные ячейки, а затем нажмите кнопку ОК. Связи со скопированными ячейками создаются начиная с выделенной ячейки.

Типы ячеек, не допускающие связывания

В колонках Вложения и Обсуждения ссылки не могут быть созданы.

Если в проекте или таблице диаграммы Ганта включены зависимости, нельзя создавать входящие ссылки в следующих типах ячеек:

  • Ячейки с формулами в столбцах;
  • Даты окончания;
  • Предшественники;
  • Итоговые ячейки в родительских строках («дата начала», «дата окончания», «% завершения»);
  • Начальные даты с корреляциями.

Однако вы можете создавать ссылки в столбцах продолжительности и даты начала (если у строки нет предшественника). Дата окончания рассчитывается автоматически, и вы можете добавить предшественников после создания ссылки.

Следующие обстоятельства также препятствуют модификации клеток:

  • Из опубликованной таблицы;
  • Из запроса на изменение;
  • Из мобильного приложения Smartsheet;
  • Из планшетного приложения Smartsheet;
  • Из отчета;
  • Из формы изменения.

4 Функция СОВПАД

Достаточно просто выполнить в Эксель сравнение двух столбцов с помощью еще двух полезных операторов — распространенного ИЛИ и встречающейся намного реже функции СОВПАД. Для ее использования выполняются такие действия:

  1. В третьем столбце, где будут размещаться результаты, вводится формула =ИЛИ(СОВПАД(I6;$H$6:$H$19))
  2. Вместо нажатия Enter нажимается комбинация клавиш Ctr + Shift + Enter. Результатом станет появление фигурных скобок слева и справа формулы.
  3. Формула протягивается вниз, до конца сравниваемой колонки — в данном случае проверяется наличие данных из второго столбца в первом. Это позволит изменяться сравниваемому показателю, тогда как знак $ закрепляет диапазон, с которым выполняется сравнение.

Результатом такого сравнения будет вывод уже не найденного совпадающего значения, а булевой переменной. В случае нахождения это будет «ИСТИНА». Если ни одного совпадения не было обнаружено — в ячейке появится надпись «ЛОЖЬ».

Стоит отметить, что функция COUNTRY сравнивает как числа, так и другие типы данных, учитывая верхний регистр. Одно из самых распространенных применений формулы сравнения двух столбцов в Excel — это поиск информации в базе данных. Например, отдельные виды мебели в каталоге.

 Как в Экселе посчитать сумму определенных ячеек

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector