Auditportal.ru

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

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

Суммирование дубликатов в Excel

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

Я отсортировал список и просуммировал их для каждого продукта, но использовал только функцию sum. Мне кажется, что должна существовать формула, которая использует оператор IF для обнаружения элементов, которые равны друг другу, а затем суммирует их соответствующие строки количества, но я не могу понять этого.

Я пытаюсь получить результат IF (любое поле в столбце A = 3792, затем сложить все связанные поля количества в столбце C).

Есть ли у кого-нибудь идея?

В этом вам поможет формула SUMIF() Excel.

Как описано выше, синтаксис

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

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

Иной подход к предмету:

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

Например, в столбце «Сумма А:» введите формулу:

В то время как D1 — ячейка, содержащая число 3792 (см. выше), а 2 в C2 — фактический ряд, в котором находится формула.

Этого было достаточно, чтобы потянуть ее вправо и вниз.

Вот что случится в результате:

Как только вы просуммируете результаты, все готово. Добавьте еще один столбец, содержащий соответствующее значение, а затем замените его в формуле IF.

Вы можете автоматизировать это еще больше, используя HLOOKUP() для номера виджета вверху.

Используйте функцию sumif. См. объяснение в этом блоге.

Предполагая, что значения начинаются в A1, вставьте эту функцию в D1 и скопируйте в D1-D16, чтобы получить суммы для каждого поля (конечно, будут дубликаты)

Поскольку это отсортированный список, я бы использовал функцию СУММЕСЛИ, а не формулу. Выберите все данные и перейдите в меню Данные > Промежуточный итог. Затем скорректируйте его в зависимости от изменения продукта.

Другой вариант — создать сводную таблицу, в которой суммируются элементы. Для меня важно, чтобы данные и сводки были разделены.

Оригинальное сообщение было написано 7 лет назад, но ниже приводится мой подход с использованием более грубой силы.

Вся «Колонка А» была скопирована на новый лист (справа или сбоку). Выделите скопированный набор данных и удалите дубликаты, оставив в новом листе один экземпляр «Колонка A». Разрешите назвать этот новый набор данных «Колонка D». После этого выведите «Столбец E».

Затем я использовал SUMPRODUCT вместе с SUMIF в «столбце E» для получения этой формулы:

Столбец E = SUMPRODUCT (SUMIF (Исходный ранг столбца A, Соответствующее значение столбца D, Исходный ранг столбца C)).

Все экземпляры суммируются и получается общая сумма.

Очевидные проблемы с этим — результаты не будут автоматически обновляться, чтобы включить новые экземпляры «Column A», но если у вас есть существующий набор данных, который вы хотите извлечь, это может быть полезно как быстрое решение.

Читайте так же:
Как изменить цвет формы в зависимости от значения ячейки в Excel?

Понимание ячеек Excel и функций диапазона в VBA

Вы, вероятно, уже знаете многие трюки с использованием формул и автоформатирования, но с помощью функций Cells и Spectrum в VBA вы сможете вывести аналитику Excel на новый уровень.

Проблема с использованием функций Cells и Range в VBA заключается в том, что на продвинутом уровне большинству людей трудно понять, как эти функции работают на самом деле. Их использование может быть очень запутанным. Вот как вы можете использовать их так, как, возможно, даже не представляли.

Функция клеток

Функции Cells и Range позволяют сообщать вашему сценарию VBA

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

Ячейки обычно относятся к одной ячейке за раз, а Спектр относится сразу к группе ячеек. Его формат — Cells (строка, столбец).

Это относится ко всем ячейкам во всем рабочем листе. Это единственный пример, когда функция Cells не ссылается на отдельную ячейку:

Это третья ячейка слева в верхнем ряду. Ячейка C1 :

Следующий код относится к ячейке D15:

Если вы хотите, вы также можете ссылаться на ячейку D15 с помощью «Ячейки (15,« D »)» — вам разрешено использовать букву столбца.

Сценарии, которые очень быстро вызывают большое количество ячеек (и выполняют в них вычисления), используют возможность ссылаться на ячейку по номеру. Вот более подробная информация об этом.

Функция диапазона

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

Диапазон (ячейка №1, ячейка №2) соответствует этой функции. Каждая ячейка может быть помечена буквой с номером.

Рассмотрим несколько примеров.

В этом случае функция диапазона относится к ячейке A5:

В данном примере функция диапазона относится ко всем ячейкам между A1 и E20:

Как упоминалось выше, нет необходимости использовать числовые распределения. Фактически, вы можете использовать две функции Cell в функции Range для определения диапазона на листе, например, следующим образом:

Приведенный выше код ссылается на тот же диапазон, что и функция Range («A1: E20»). Ценность его использования заключается в том, что он позволит вам писать код, который динамически работает с диапазонами, используя циклы

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

Обработка данных с функцией ячеек

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

Давайте рассмотрим простой пример. Если вы руководите отделом продаж из 11 человек, то каждый месяц вам нравится смотреть на их эффективность.

У вас может быть лист 1, на котором отслеживается количество продаж и объем продаж.

обработка данных - Excel функции VBA

Именно на Листе2 вы отслеживаете отзывы клиентов за последние 30 дней.

Читайте так же:
Как защитить верхний и нижний колонтитулы в Excel?

обработка данных - Excel функции VBA

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

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

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

Клетки функционируют в действии

Если вы никогда ранее не писали VBA в Excel, вам нужно включить пункт меню «Разработчик». Для этого перейдите в файл >Опции. Нажмите на Настроить ленту. Наконец, выберите Developer из левой панели, добавлять на правой панели, и убедитесь, что флажок установлен.

Microsoft Excel настроить ленту

Если вы нажмете OK и вернетесь на основной лист, вы увидите пункт меню Разработчик.

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

Microsoft Excel просмотр кода

В этом примере мы будем запускать скрипт каждый раз при открытии книги. Для этого просто щелкните View Code в меню Developer и вставьте следующую новую функцию в окно кода.

Вот как будет выглядеть ваше окно кода.

Excel функции VBA код

Теперь пришло время написать код для обработки вычислений. Можно пройтись по всем 11 сотрудникам и ввести три переменные, необходимые для расчета, с помощью функции Cells.

Помните, что функция Cells имеет строку и столбец в качестве параметров для идентификации каждой отдельной ячейки. Мы сделаем «x» в строке, будем использовать число для запроса данных каждого столбца. Количество строк — это число сотрудников, поэтому оно будет от 1 до 11. Идентификатор столбца будет 2 для количества продаж, 3 для объема продаж и 2 из листа 2 для оценки обратной связи.

В окончательном расчете используются следующие проценты, которые складываются в 100 процентов от общей суммы бонуса. Он основан на идеальном количестве продаж 50, объеме продаж 50 000 долларов и обратной связи 10.

  • (Количество продаж / 50) x 0,4
  • (Объем продаж / 50 000) x 0,5
  • (Рейтинг отзывов / 10) x 0,1

Этот простой подход дает продавцам взвешенный бонус. Достигнув объема в $50 500 и заработав 10 баллов, они получают максимальный бонус за месяц. Бонус уменьшается на все, что находится в идеальном состоянии. Любое улучшение по сравнению с идеальным состоянием увеличивает бонус.

Теперь давайте продемонстрируем, как можно реализовать всю эту логику в очень простом и коротком сценарии VBA:

Вот как выглядит результат работы данного сценария.

Excel функции VBA выход

Если вы хотите, чтобы в столбце «Бонус» отображался фактический долларовый бонус, а не процент, вы можете умножить его на максимальную сумму бонуса. А еще лучше, поместите эту сумму в ячейку на другом листе и укажите ее в своем коде. Это упростит изменение значения позже без необходимости редактировать код.

С помощью Cells вы можете извлекать данные из многих ячеек на разных листах и выполнять с их помощью довольно сложные расчеты.

Вы можете выполнять всевозможные действия с ячейками с помощью функции Cells — например, очистка ячеек, изменение форматирования шрифта и многое другое.

Читайте так же:
Как извлечь дату из текстовых строк в Excel?

Дополнительную информацию см. на странице MSDN Microsoft, посвященной объекту Cell.

Форматирование ячеек с функцией диапазона

Функция «Ячейки» идеально подходит для циклического прохождения множества ячеек по одной за раз. Но если вы хотите применить что-то сразу ко всему диапазону ячеек, функция Range будет гораздо более эффективной.

Сценарий можно использовать для форматирования диапазона ячеек на основе условий.

форматирование ячеек - функции Excel vba

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

Давайте посмотрим, как это можно сделать с помощью оператора IF.

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

Это лишь один простой пример из множества действий, которые можно выполнять над группами ячеек с помощью функции Range. Можно выполнить и другие действия, например:

  • Применить план вокруг группы
  • Проверьте правильность написания текста внутри диапазона ячеек
  • Очистить, скопировать или вырезать ячейки
  • Поиск по диапазону с помощью метода «Найти»
  • Намного больше

Обязательно прочитайте страницу Microsoft MSDN для объекта Range, чтобы увидеть все его возможности.

Возьмите Excel на следующий уровень

Изучив различия между функциями Cells и Range, вы теперь готовы к созданию собственных сценариев VBA. Статья Данна об использовании функций count и add в Excel позволит вам создать еще более сложные сценарии, которые смогут очень быстро накапливать значения по всем наборам данных.

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

Диапазон ячеек Excel

Начнем с того, что дадим ячейке имя. В поле Имя (2) вместо адреса ячейки введите любое имя, которое легко запомнить.

Назовите ячейку С7.

Вы ограничены 255 символами, поэтому имени более чем достаточно. Кроме того, имя не должно содержать пробелов, поэтому вы можете использовать знак подчеркивания для разделения нескольких слов, если они расположены близко друг к другу.

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

Использование обозначения ячеек

Ячейки, строки, столбцы

Давайте начнем с выделения ячеек, строк и столбцов.

    Чтобы выбрать ячейку C3, нажмите на поле на пересечении столбца C и строки 3.

Примеры диапазона

Интервал — это набор из двух или более клеток.

    Чтобы выделить диапазон B2:C4, щелкните в правом нижнем углу ячейки B2 и перетащите указатель мыши в ячейку C4.

Заполнение диапазона

Для заполнения диапазона выполните следующие действия:

    В ячейку B2 введите значение 2.

Метод перетаскивания является жизненно важным для Excel; вы будете использовать его часто. Вот еще один пример:

Именованный диапазон

Как и диапазон ячеек, диапазон ячеек можно назвать аналогичным образом, то есть выделить диапазон (1) и ввести его название в поле названия (2):

Читайте так же:
Как импортировать несколько текстовых файлов на несколько листов?

Создание названной зоны

На основе этого имени можно писать формулы, например, для вычисления суммы:

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

Вы также можете создать именованную область на вкладке «Формулы», выбрав инструмент «Имя».

Создание поименованного диапазона с помощью панели инструментов

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

Создание имени с использованием диалогового окна

Чтобы облегчить работу с существующими диапазонами, на вкладке Формулы есть менеджер имен.

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

Управление именным диапазоном

Важно понимать, что удаление имени именованного диапазона в формуле приводит к ошибкам.

Задача

Он содержит таблицу продаж определенных товаров за месяц (см. пример файла):

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

Наше решение заключается в создании двух динамических диапазонов: один для выпадающего списка с месяцами, а другой для диапазона сумм.

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

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

Для создания динамического вентилятора :

  • На вкладке Формулы в группе Определенные имена выберите Назначить имя;
  • В поле Имя введите: Месяц;
  • В поле Домен выберите Лист регистрации;
  • В поле Диапазон введите формулу =MEMBERSY(sheet1!$B$5;;;;1;COUNT(sheet1!$B$5:$I$5)).
  • Нажмите OK.

Теперь более подробно. Любой диапазон в EXCEL определяется координатами верхней левой и нижней правой ячеек диапазона. Начальная ячейка, от которой рассчитывается положение нашего динамического диапазона, — B5. Это левая верхняя ячейка диапазона, если не указаны значения offset_by_rows и offset_by_columns (как в нашем случае). Высота и ширина задаются для правой нижней ячейки диапазона. Здесь мы имеем значение высоты 1 и ширины диапазона 4 (строка 5 содержит четыре месяца с января по апрель), оба значения вычислены по методу SCHOTZ(sheet1!$B$5:$I$5). Таким образом, адрес правой нижней ячейки нашего динамического диапазона — E 5.

Формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и тем самым определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.

В АЖНО: При использовании функции SCRETZ() убедитесь, что в списке нет пустых ячеек! То есть, вы должны заполнить список месяцами без пробелов между ними.

Теперь создадим еще один динамический ряд для подведения итогов продаж.

Для создания динамической области :

  • На вкладке Формулы в группе Определенные имена выберите Назначить имя;
  • В поле Имя введите: Продажи_в_месяц;
  • В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
  • Нажмите OK.
Читайте так же:
Как изменить текстовые строки в ячейках в листе Google?

Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Левый верхний угол нашего динамического диапазона (из ячейки A6) смещается на это количество столбцов, высота диапазона остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества элементов в диапазоне).

Наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж за конкретный месяц.

Например, в мае месяце.

Или, например, в апреле месяце.

Если вы хотите подсчитать, сколько месяцев осталось, вы можете использовать INDEX() вместо SMUM() : = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ), возвращая ссылку на диапазон B5:E5.

Визуальное отображение динамического диапазона

Текущий динамический диапазон можно выделить с помощью условного форматирования. В примере файла правило условного форматирования применяется к ячейкам диапазона B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяце)

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

Как посчитать количество ссылок на столбцы таблицы Excel

Пример 2: Определите количество колонок в таблице и введите это значение в ячейку А16.

В качестве параметра мы выделяем каждый столбец ячеек через формулу OVERALL. Удерживая клавишу Ctrl, выделите следующий столбец. Добавление «)» и нажатие Enter приводит к появлению диалогового окна, сообщающего, что вы ввели слишком много аргументов. Поместите дополнительные круглые скобки между открывающей и закрывающей частью предложения.

Определение принадлежности ячейки к диапазону таблицы

Пример 3: Проверяет, принадлежит ли заданная ячейка диапазону ячеек.

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

    1. Введите часть формулы «=WORLD(()» в любую ячейку и выберите любой диапазон ячеек, чтобы заполнить аргументы:
      1. Вставьте пробел и выделите любую ячейку диапазона:
        1. Закройте обе скобки и нажмите клавишу Enter. Результат следующий:
          1. Если вы выберете ячейку из диапазона, отличного от указанного, вы получите ошибку #empty!

          Если отображается эта ошибка, это означает, что выбранный диапазон не включает ячейку.

          Если в диапазоне выбрано более одной ячейки, функция ПЕРЕМЕННАЯ возвращает количество выбранных ячеек:

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

          Перемещение и копирование ячеек и их содержимого

          Для макроса «Фильтр» нужно скопировать формулу = ЕСЛИ(ANSWER(A2);A2;B2). Она должна работать только в таблице: Я думаю, что это будет работать, если столбец включен. Т.е. получается, что ВСЕ», затем выполнить и нажать кнопку Вставить более сложную процедуру, нажать значок Вставка на следующих шагах. Вставить, вы можете выбрать для временного отображения данных, выбранный раздел наПримечание: Вставить в качестве выходных значений таблицы с необходимостью?

          Сначала нужно скопировать выделенный диапазон, который вы выбрали для вышеуказанных действийCtrl+Paste. Чтобы переместить ячейку, щелкните ее. Вставляйте варианты, которые не требуют другого рабочего листа или Попробуйте как можно чаще

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