Как получить список уникальных значений

Выборка данных в Excel (как сделать из таблицы массива по условию)

Первый способ: Применение расширенного автофильтра

На листе Excel необходимо выделить область, среди данных, которых и нужно осуществить выборку. Во вкладке «Главная» нажимаете «Сортировка и фильтр» (находится в блоке настроек «Редактирование»). Далее нажимаете на фильтр.

Можно сделать иначе, после выделения области переходите во вкладку «Данные» и нажимаете на «Фильтр», размещенной в группе «Сортировка и фильтр».

Когда эти действия выполнены, в шапке таблицы должны появиться пиктограммы для запуска фильтрования. Они будут отображены острием вниз небольшими треугольниками в правом крае ячеек. Нажимаете на этот значок в начале того столбца, по которому и собираетесь сделать выборку. Запуститься меню, в нем переходите в «Текстовые фильтры» и выбираете «Настраиваемый фильтр…».

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

После фильтрации остаются только те строчки, в которых сумма выручки превышает значение 10000 (как пример).

В этом же столбце есть возможность добавить и второе условие. Нужно снова вернуться в окно пользовательской фильтрации, а в его нижней части установить другую границу отбора. Переключатель выставляете в позицию «Меньше», а в поле справа вписываете «15000».

В таблице останутся только те строки, в которых сумма выручки не меньше 10000, но и не больше 15000.

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

Должно запуститься окно пользовательского автофильтра. Выполните, к примеру, отбор результатов в таблице с 4 по 6 мая 2016 года включительно. Нажимаете «После или равно», а в поле справа выставляете значение «04.05.2016». В нижнем блоке переключатель ставите в позицию «До или равно», а в правом поле вносите «06.05.2016». Переключатель совместимости условий оставляете в положении по умолчанию, то есть «И». Для применения фильтрации кликаете на ОК.

Список теперь должен сократиться еще больше, потому что останутся только строки, в которых сумма выручки варьируется от 10000 до 15000 и это за период с 04.05 по 06.05.2016 включительно.

В одном из столбцов при желании можно сбросить фильтрацию. К примеру, можно сделать это для значений выручки. Нажимаете на значок автофильтра в соответствующем столбце. Выбираете «Удалить фильтр».

Выборка по сумме выручки отключится и останется только отбор по датам (с 04.05.2016 по 06.05.2016).

В таблице есть и еще одна колонка под названием «Наименование». В ней расположенные данные в текстовом формате. По этим значениям тоже можно сформировать выборку. В наименовании столбца нажмите на значок фильтра. Переходите на «Текстовые фильтры», а затем «Настраиваемый фильтр…».

Снова откроется окно пользовательского фильтра, в котором можно сделать выборку, к примеру, по наименованиям «Мясо» и «Картофель». В первом блоке нужно установить переключатель в позиции «Равно» а в поле справа от него внести «Картофель». Переключатель нижнего блока поставить в позицию «Равно», а в поле напротив – «Мясо». Теперь следует установить переключатель совместимости условий в позиции «ИЛИ». Нажимаете ОК.

В новой выборке выставлены ограничения по дате (с 04.05.2016 по 06.05.2016) и по наименованию (Мясо и Картофель). Ограничений нет только по сумме выручки.

Можно фильтр удалить полностью и делается это теми же способами, которые применялись для его выставления. Для того чтобы сбросить фильтрацию во вкладке «Данные» нажмите на «Фильтр» в группе «Сортировка и фильтр».

Во втором варианте можно перейти во вкладку «Главная» и нажать там на «Сортировка и фильтр» в «Редактирование». Далее кликаете на «Фильтр».

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

Второй способ: Применение формулы массива

На том же листе Excel создаете пустую таблицу с теми же наименованиями столбцов в шапке, которые имеются у исходника.

Все пустые ячейки необходимо выделить в первой колонке новой таблицы. В строку формул устанавливаете курсор, чтобы занести формулу — =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000

Отбор уникальных значений по условию.

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

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

В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:

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

Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.

Вот наша формула массива:

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

В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).

Объединение и разбиение данных в ячейках в Excel с форматированием

Форматирование и редактирование ячеек в Excel – удобный инструмент для наглядного представления информации. Такие возможности программы для работы бесценны.

Значимость оптимальной демонстрации данных объяснять никому не нужно. Давайте посмотрим, что можно сделать с ячейками в Microsoft Excel. Из данного урока вы узнаете о новых возможностях заполнения и форматирования данных в рабочих листах.

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

Порядок объединения ячеек в Excel:

1. Возьмем небольшую табличку, где несколько строк и столбцов.

Для объединения ячеек используется инструмент «Выравнивание» на главной странице программы.

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

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

Точно таким же образом можно объединить несколько вертикальных ячеек (столбец данных).

Можно объединить сразу группу смежных ячеек по горизонтали и по вертикали.

Если нужно объединить только строки в выделенном диапазоне, нажимаем на запись «Объединить по строкам».

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

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



Разбить на две ячейки можно только объединенную ячейку. А самостоятельную, которая не была объединена – нельзя. НО как получить такую таблицу:

Давайте посмотрим на нее внимательнее, на листе Excel.

Черта разделяет не одну ячейку, а показывает границы двух ячеек. Ячейки выше «разделенной» и ниже объединены по строкам. Первый столбец, третий и четвертый в этой таблице состоят из одного столбца. Второй столбец – из двух.

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

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

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

1. Щелкаем правой кнопкой по ячейке и выбираем инструмент «Формат» (или комбинация горячих клавиш CTRL+1).
2. На закладке «Граница» выбираем диагональ. Ее направление, тип линии, толщину, цвет.
3. Жмем ОК.

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

На вкладке «Иллюстрации» выбираем «Фигуры». Раздел «Линии».

Проводим диагональ в нужном направлении.

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

Преобразовать ячейки в один размер можно следующим образом:

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

Можно изменить ширину ячеек во всем листе. Для этого нужно выделить весь лист. Нажмем левой кнопкой мыши на пересечение названий строк и столбцов (или комбинация горячих клавиш CTRL+A).

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

Как разбить ячейку на строки?

В Excel можно сделать несколько строк из одной ячейки. Перечислены улицы в одну строку.

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

Выделяем ячейку. На вкладке «Выравнивание» нажимаем кнопку «Перенос текста».

Данные в ячейке автоматически распределятся по нескольким строкам.

Пробуйте, экспериментируйте. Устанавливайте наиболее удобные для своих читателей форматы.

Случайный выбор значения из списка

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

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

или

Вот и все! Средство выбора случайных имен для Excel настроено и готово к работе:

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

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

Как работают эти формулы

Мы используем функцию ИНДЕКС для извлечения значения из списка на основе случайного порядкового номера, возвращаемого .

То есть, функция СЛУЧМЕЖДУ генерирует случайное целое число между двумя указанными вами значениями. Для нижнего порога вы указываете число 1. Для верхнего — используете СЧЁТЗ() или ЧСТРОК(), чтобы получить общее количество ячеек с данными. В результате СЛУЧМЕЖДУ() возвращает случайный номер ячейки в вашем наборе данных. Этот номер передается в функцию ИНДЕКС, сообщая ей, какую по счёту ячейку выбрать. Второй аргумент (номер столбца) можно не указывать, поскольку он у нас только один.

Примечание. Этот метод хорошо подходит для выбора одного случайного значения из списка. Если ваш выбор должен включать несколько результатов, приведенная выше формула может возвращать несколько вхождений одного и того же значения, поскольку функция СЛУЧМЕЖДУ не защищена от дубликатов. Это особенно актуально, когда вы выбираете относительно большую выборку из относительно небольшого списка. 

Эту задачу можно также решить с помощью формулы

Функция СЛУЧМЕЖДУ() случайным образом выбирает позицию списка, из которой нужно взять одно значение ( для этой функции вероятность выбрать любую строку одинакова).

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

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

Что такое выборка

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

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

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

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

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

=Отчет – на всю Таблицу=Отчет – только на данные (без строки заголовка)=Отчет – только на первую строку заголовков=Отчет – на итоги=Отчет – на всю текущую строку (где вводится формула)=Отчет – на весь столбец «Продажи»=Отчет – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Сумма без учета ошибок

Отличный способ применения формул массивов – сложить числа в массиве, содержащем ошибки. Пусть в массиве В2:F5 у нас результаты вычислений, среди которых встречаются ошибки. Как сложить эти числа, игнорируя ошибки? Очень просто, вот вам формула: =СУММ(ЕСЛИОШИБКА(B2:F5;0)) . Здесь функция ЕСЛИОШИБКА проверит содержание каждой ячейки и заменит ( в памяти компьютера, не на листе ) ошибки на нули. Далее, функция СУММ просуммирует все обработанные значения и выдаст результат без учета ошибок.

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

Эксперты MS Excel выстраивают из подобных формул огромные комбинации, выполняя в одной ячейке столько операций, сколько обычные пользователи умещают на несколько листов. Так что, экспериментируйте, пробуйте разные варианты применения, и не забывайте: для завершения ввода нажимаем Ctr+Shift+Enter , вместо клавиши «Ввод».

А в следующей публикации мы с вами будем рисовать диаграммы, а это слишком важная тема, чтобы ее пропустить, подписывайтесь на электронную рассылку и читайте свежие статьи пока горячи!

Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

Для отбора строк используются формулы массива, аналогичные Задаче1 (вместо критерия =$B$12:$B$20)*(СТРОКА($B$12:$B$20)-СТРОКА($B$11));$J$12-СТРОКА(A12)+СТРОКА($B$11)+1))

Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

Аналогичную формулу нужно ввести и для дат в столбец E.

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

Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .

Этот пример еще раз наглядно демонстрирует насколько предварительная сортировка данных облегчает написание формул.

Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет возвращать несколько значений ).

Те же манипуляции произведем с диапазоном E11:E19 (столбец Цена) куда и введем аналогичную формулу массива :

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

Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи.

Если в Исходную таблицу добавить новый товар с Ценой 80, то в новую таблицу автоматически будет добавлена новая запись.

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

Если Вам не удобно использовать формулу массива , которая возвращает несколько значений, то можно использовать другой подход, который рассмотрен в разделах ниже: 5.а, 7, 10 и 11. В этих случаях используются формулы массива, возвращающие одно значение .

Какие вопросы обычно ставится перед исследователем

Любое исследование если и проводится, то для какой-то цели и для получения каких-то результатов. При проведении выборочного исследования, как правило, ставятся начальные вопросы:

1. Определение необходимого количества единиц выборочной совокупности, то есть то, сколько единиц будет исследоваться. К тому же, для точного исследования совокупность должна быть репрезентативной.
2. Расчет погрешности репрезентативности с установленным уровнем вероятности. Сразу стоит отметить, что выборочных исследований не бывает с уровнем вероятности 100 %. Если та инстанция, которая проводила изучение определенного сегмента, утверждает, что их результаты точны с вероятностью 100 %, то это ложь. Многолетняя практика уже установила процент вероятности правильно проведенного выборочного исследования. Этот показатель равняется 95,4 %.

Как сделать выборку

Итак, чтобы репрезентативность была максимальной, выделяют три основные правила выборки:

1. Самым уникальным показателем числа выборки считается 20 %. Статистическая выборка в 20 % будет практически всегда давать результат максимально приближенный к действительности. В то же самое время нет необходимости переносить в собранную большую часть генеральной совокупности. 20 % выборки – это тот показатель, который выработан многими исследованиями. Приведем еще немного теории. Чем больше выборка, тем меньше ошибка репрезентативности и точнее результат исследования. Чем ближе будет выборочная совокупность к генеральной по количеству единиц, тем более точными и правильными будут результаты. Ведь если исследовать всю систему, тогда результат будет 100 %. Но здесь уже нет выборки. Это те исследования, в которых исследуется весь массив, все единицы, поэтому это нас не интересует.
2. В случае нецелесообразности обработки 20 % генеральной совокупности допускается изучение единиц совокупности в количестве не менее 1001. Это также один из показателей исследования массива объекта исследования, который выработался со временем. Конечно же, он не даст точных результатов при больших массивах исследования, но максимально приблизит к возможной точности выборки.
3. В статистике существует множество формул и сведенных таблиц. В зависимости от объекта исследования и от критерия выборки, существует целесообразность выбора той или иной формулы. Но этот пункт используется в сложных и многоэтапных исследованиях.

Автофильтр

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

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

19

1. Выбираем один из фильтров, заполняем поля в окне «Пользовательский автофильтр» и жмем «ОК». Настройки будут применены к диапазону ячеек.

20

Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.

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

21

1. Заполняем поля – можно выбрать любой тип фильтра, походящий по формату ячеек, из списка и значения, существующие в диапазоне ячеек. После заполнения нажимаем кнопку «ОК». Если заполнение было непротиворечивым, настройки будут применены автоматически.

Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично

Как сравнить две таблицы при помощи формулы ВПР.

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

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

Для наглядности расположим обе таблицы на одном листе.

Формула

берёт наименование товара из второго прайса, ищет его в первом, и в случае удачи извлекает соответствующую цену из первой таблицы. Она будет записана рядом с новой ценой в столбце H. Если поиск завершился неудачей, то есть такого товара ранее не было, то ставим 0. Таким образом, старая и новая цена оказываются рядом, и их легко сравнить простейшей операцией вычитания. Что и сделано в столбце I.

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

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

Разберём действия пошагово. Формула в ячейке J3 ищет наименование товара из первой позиции второй таблицы внутри первой. Если таковое найдено, извлекается соответствующая этому товару старая цена и сразу же сравнивается с новой. Если они одинаковы, то в ячейку записывается пустота «». 

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

Далее если ячейка J3 не пустая, то в I3 будет указано наименование товара —  

а в K3 – его новая цена:  

Ну а далее в L3 просто найдем разность K3-J3.

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

И еще один пример, который может быть полезен. Попытаемся сравнить в итоговой таблице оба прайс-листа с эталонным общим списком товаров.

В ячейке B2 запишем формулу

Так мы выясним, какие цены из второй таблицы встречаются в первой.

Для каждой цены из первого прайса проверяем, совпадает ли она с новыми данными  —

Эталонный список находится у нас в столбце A. В результате мы получили своего рода сводную таблицу цен – старых и новых.

Извлечь уникальные значения из диапазона.

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

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

Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.

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

Как видите, извлекаются все уникальные и первые вхождения дубликатов.

Формулы массива в Excel

Терминология

Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:

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

Пример 1. Классика жанра — товарный чек

Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:

1. выделяем ячейку С7
2. вводим с клавиатуры =СУММ(
3. выделяем диапазон B2:B5
4. вводим знак умножения (звездочка)
5. выделяем диапазон C2:C5 и закрываем скобку функции СУММ — в итоге должно получиться так:

чтобы Excel воспринял нашу формулу как формулу массива жмем не Enter, как обычно, а Ctrl + Shift + Enter

Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.

Обратите внимание на фигурные скобки, появившиеся в формуле — отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно — они автоматически появляются при нажатии Ctrl + Shift + Enter

Пример 2. Разрешите Вас. транспонировать?

При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.

Допустим, имеем двумерный массив ячеек, который хотим транспонировать.

• Выделяем диапазон ячеек для размещения транспонированной таблицы. Поскольку исходный массив ячеек был 8 строк на 2 столбца, то надо выделить диапазон пустых ячеек размером 2 строки на 8 столбцов.
• вводим функцию транспонирования =ТРАНСП(
• в качестве аргумента функции выделяем наш массив ячеек A1:B8

жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:

Редактирование формулы массива

Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.

Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.

Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)

Пример 3. Таблица умножения

Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:

При помощи формул массива она вся делается в одно движение:

1. выделяем диапазон B2:K11
2. вводим формулу =A2:A11*B1:K1
3. жмем Ctrl + Shift + Enter, чтобы Excel воспринял ее как формулу массива

и получаем результат:

Пример 4. Выборочное суммирование

Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:

В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.

Как вставить промежуточные итоги в Excel

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

1. Организуйте исходные данные

Функция «Промежуточные итоги» в Excel требует, чтобы исходные данные располагались в правильном порядке (то есть, однотипные — рядом) и не содержали пустых строк.

Итак, прежде всего обязательно отсортируйте ваши данные по столбцу, по которому вы хотите их сгруппировать. Самый простой способ сделать это – нажать кнопку «Фильтр на вкладке «Данные», затем щелкнуть стрелку фильтра и выбрать сортировку от А до Я или от Я до А:

Чтобы удалить пустые ячейки, не испортив данные, следуйте этим рекомендациям: Как быстро и безопасно удалить пустые строки в Excel.

После этого подготовительную работу можно считать завершенной.

2. Добавьте промежуточные итоги

Выберите любую ячейку в наборе данных, перейдите на вкладку «Данные»> в группу «Структура» и нажмите «Промежуточный итог.

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

3. Определите параметры промежуточных итогов.

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

• В поле При каждом изменении в выберите столбец, по которому вы хотите группировать данные. В нашем случае мы выберем колонку с наименованиями покупателей.
• В списке Использовать функцию выберите одну из следующих:
  • Сумма.
  • Количество – подсчет непустых ячеек (это вставит формулы промежуточных итогов с функцией СЧЁТ ).
  • Среднее – расчет среднего значения.
  • Максимум – вернуть наибольшее число.
  • Минимум – получить наименьшее число.
  • Произведение – вычислить произведение по столбцу.
  • Количество чисел – подсчет ячеек, содержащих числа.
  • Стандартное отклонение – вычисление стандартного отклонения генеральной совокупности на основе выборки чисел.
  • Несмещённое отклонение – возвращает стандартное отклонение, основанное на всей совокупности чисел.
  • Дисперсия – оценка дисперсии генеральной совокупности на основе выборки чисел.
  • Несмещённая дисперсия – оценка дисперсии генеральной совокупности на основе всей совокупности чисел.
• В разделе «Добавить итоги по» установите флажок для каждого столбца, по которому вы хотите получить промежуточный итог.

В этом примере мы группируем данные по столбцу «Код покупателя» и используем функцию СУММ для получения итоговых значений в столбцах «Количество» и «Сумма».

Кроме того, вы можете выбрать любую из дополнительных опций:

• Чтобы вставить автоматический разрыв страницы после каждого промежуточного итога, установите флажок Конец страницы между группами. В итоге каждая группа будет распечатана на отдельном листе. Но в большинстве случаев это не нужно, поэтому эта опция обычно не активна.
• Чтобы отобразить итоговую строку сверху над данными, снимите флажок «Итоги под данными. Этот пункт обычно активирован по умолчанию, так как нам все же привычнее, когда сначала идут данные, а под ними — итоги.
• Чтобы перезаписать любые уже существующие промежуточные итоги, активируйте флажок «Заменить текущие итоги. Если вы изменили данные, то старые итоги вам не нужны. А вот если вы работаете не со всей, а только с частью таблицы (о такой возможности мы говорили выше), тогда, возможно, не нужно удалять то, что уже было посчитано. Кроме того, если не ставить этот флажок, то вы добавите еще один уровень итогов. Например, вы нашли сумму продаж по каждой группе, и можете добавить еще количество продаж или средний размер заказа. То есть, по каждой группе можно рассчитать несколько разных итогов.

Наконец, нажмите кнопку ОК. Промежуточные итоги появятся под каждой группой данных, а общая сумма будет добавлена ​​в конец таблицы.

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

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

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

Укажите, какую операцию нужно выполнить. И не забудьте убрать птичку в пункте «Заменить текущие итоги».

В результате получаем вот такую картину:

Как видите, подсчитаны и среднее, и сумма.

Похожие записи:

Гугл диск Восстановление windows 7. руководство по устранению проблемы загрузки ос windows 7 Как рассчитать мощность блока питания для компьютера Устраняем ошибку «сбой запроса дескриптора неизвестного usb устройства» на windows 10 Как пользоваться paint 3d. общая концепция рисования Как установить живые обои на виндовс 7