Диапазон excel

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

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

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

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

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

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

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

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

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

Функция ПОИСКПОЗ

Возвращает позицию элемента, заданного по значению, в диапазоне либо массиве.

Синтаксис: =ПОИСКПОЗ(искомое_значение; массив; ), где:

• искомое_значение – обязательный аргумент. Значение элемента, который необходимо найти в массиве.
• Массив – обязательный аргумент. Одномерный диапазон либо массив для поиска элемента.
• тип_сопоставления – необязательный аргумент. Число 1, 0 или -1, определяющее способ поиска элемента:
  • 1 – значение по умолчанию. Если совпадений не найдено, то возвращается позиция ближайшего меньшего по значению к искомому элементу. Массив или диапазон должен быть отсортирован от меньшего к большему или от А до Я.
  • 0 – функция ищет точное совпадение. Если не найдено, то возвращается ошибка #Н/Д.
  • -1 – Если совпадений не найдено, то возвращается позиция ближайшего большего по значению к искомому элементу. Массив или диапазон должен быть отсортирован по убыванию.

Пример использования:=ПОИСКПОЗ(«Г»; ) – функция возвращает результат 4.

Извлечение уникальных значений с помощью Duplicate Remover.

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

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

А теперь давайте посмотрим, как работает инструмент Duplicate Remover.

Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, включая уникальные и первые повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover работа выполняется за несколько шагов.

1. Выберите любую ячейку в исходной таблице и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.

Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.

2. Выберите тип значения, который вы хотите найти, и нажмите Далее :
   • Уникальные
   • Уникальные + 1е вхождения (различные)

В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:

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

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

В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.

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

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

Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому  не трогаем опцию Case-sensitive match.

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

Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).

В этом примере давайте выберем новый лист:

5. Нажмите кнопку « Готово» , и все готово!

В итоге у нас осталось всего 20 записей.

Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.

В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.

Как сделать выборку в Excel по условию

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

Сначала научимся делать выборку по одному числовому критерию. Задача – выбрать из таблицы товары с ценой выше 200 рублей. Один из способов решения – применение фильтрации. В результате в исходной таблице останутся только те товары, которые удовлетворяют запросу.

Другой способ решения – использование формулы массива. Соответствующие запросу строки поместятся в отдельный отчет-таблицу.

Сначала создаем пустую таблицу рядом с исходной: дублируем заголовки, количество строк и столбцов. Новая таблица занимает диапазон Е1:G10.Теперь выделяем Е2:Е10 (столбец «Дата») и вводим следующую формулу: .

Чтобы получилась формула массива, нажимаем сочетание клавиш Ctrl + Shift + Enter. В соседний столбец – «Товар» — вводим аналогичную формулу массива: . Изменился только первый аргумент функции ИНДЕКС.

В столбец «Цена» введем такую же формулу массива, изменив первый аргумент функции ИНДЕКС.

В результате получаем отчет по товарам с ценой больше 200 рублей.

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

Задача №2 – выбрать из исходной таблицы товары, которые поступили в продажу 20.09.2015. То есть критерий отбора – дата. Для удобства искомую дату введем в отдельную ячейку, I2.

Для решения задачи используется аналогичная формула массива. Только вместо критерия >.

Подобные формулы вводятся и в другие столбцы (принцип см. выше).

Теперь используем текстовый критерий. Вместо даты в ячейку I2 введем текст «Товар 1». Немного изменим формулу массива: .

Такая большая функция выборки в Excel.

Вычислить условный диапазон в Excel

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

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

Например, у вас может быть набор данных, в котором все значения меньше 100, но есть одно значение выше 500.

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

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

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

Если вы внимательно посмотрите на эти данные, вы заметите, что есть два магазина, где значения довольно низкие (Магазин 1 и Магазин 3).

Это может быть связано с тем, что это новые магазины или какие-то внешние факторы повлияли на продажи в этих конкретных магазинах.

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

В этом примере, скажем, я хочу игнорировать все те магазины, где стоимость продажи меньше 20 000.

Ниже приведена формула, по которой можно найти диапазон с условием:

В приведенной выше формуле вместо использования функции MIN я использовал функцию MINIFS (это новая функция в Excel2021-2022 и Microsoft 365).

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

Таким образом, функция MINIFS просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000.

Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, игнорируются выбросы).

Обратите внимание, что MINIFS — это новая функция в Excel. доступно только в Excel2021-2022 и подписке Microsoft 365. Если вы используете предыдущие версии, у вас не будет этой функции (и вы можете использовать формулу, описанную далее в этом руководстве)

Если в вашем Excel нет функции МИНИМУМ, воспользуйтесь приведенной ниже формулой, в которой для того же результата используется комбинация функций ЕСЛИ и МИНИМУМ: Так же, как я использовал условную функцию MINIFS, вы также можете использовать функцию MAXIFS, если вы хотите избежать точек данных, которые являются выбросами в другом направлении (т. Е. Пара больших точек данных, которые могут исказить данные)

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

Надеюсь, вы нашли этот урок полезным.

Простейшие примеры обмена значениями

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

Пример 1

Заполнение двумерного массива значениями и и их присвоение диапазону ячеек на рабочем листе Excel:

1 2 3 4 5 6 7 8 9 10 11 12 13

SubTest1() Dima(2,2)AsVariant a(,)=»телепузик» a(,1)=»журналист» a(,2)=»ящерица» a(1,)=»короед» a(1,1)=»утенок» a(1,2)=»шмель» a(2,)=200 a(2,1)=300 a(2,2)=400 Range(«A1:C3»).Value=a EndSub

В данном случае переменная массива не обязательно должна быть универсального типа (As Variant), например, если бы в нее записывались только текстовые данные, ее можно было бы объявить как строковую (As String), и все бы работало.

Пример 2

Объявление обычной переменной универсального типа, присвоение ей значений из диапазона ячеек «A1:C3», записанных кодом первого примера, и вставка этих значений из полученного двумерного массива в диапазон «D10:F12»:

1 2 3 4 5

SubTest2() DimaAsVariant a=Range(«A1:C3») Range(«D10:F12»)=a EndSub

Естественно, указанные диапазоны ячеек расположены на активном листе.

Пример 3

Допустим, на рабочем листе «Лист1» в ячейках «A1:A5» записано количество какого-то товара, а в ячейках «B1:B5» — его цена. Необходимо к этой информации добавить сумму каждого товара, умножив количество на цену, и перенести данные на «Лист2».

1 2 3 4 5 6 7 8 9

SubTest3() DimaAsVariant,iAsLong a=Лист1.Range(«A1:C5») Fori=1To5 a(i,3)=a(i,1)_ *a(i,2) Next Лист2.Range(«A1:C5»)=a EndSub

Массив создан сразу с размерностью 5×3 с элементами под суммы. Даже если на первом листе в ячейках «C1:C5» есть какие-то значения, в массиве они будут перезаписаны результатами вычислений.

Овладейте анализом данных

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

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

Post Views: 1 032

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Немного теории

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

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

Плюсы использования такого вида группировки данных:

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

Обучение работе со сводными таблицами в Excel не займет много времени и может основываться на видео.

Копирование значений из массива в диапазон ячеек

Значения в диапазон ячеек добавляются из массива с помощью оператора присваивания (=):

1 2 3 4

Range(«A6:F15»)=a ‘или Range(«A6:F15»).Value=a ‘где a — переменная двумерного массива

Обратите внимание, что вставить значения в диапазон ячеек можно только из двумерного массива. Размерность такого массива может начинаться с нуля (0)

Количество элементов в измерениях массива должно совпадать с количеством строк и столбцов в диапазоне ячеек. Если вам нужно вставить значения в одну строку или в один столбец, укажите размерность единственной строки или единственного столбца как (0) или (1 To 1), если вы хотите использовать нумерацию измерений своего массива с единицы. Например, для записи десяти значений из массива в одну строку можно объявить такой массив — massiv(9, 0), или в один столбец — massiv(0, 9).

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

Примеры использования функции ПОИСК в Excel

Пример 1. Ищем слово внутри текстовой строки (с начала)

На примере выше видно, что когда мы ищем слово “доброе” в тексте “Доброе утро”, функция возвращает значение “1”, что соответствует позиции слова “доброе” в тексте “Доброе утро”.

Так как функция не чувствительна к регистру, нет разницы каким образом мы указываем искомое слово “доброе”, будь то “ДОБРОЕ”, “Доброе”, “дОброе” и.т.д. функция вернет одно и то же значение.

Если вам необходимо осуществить поиск чувствительный к регистру – используйте функцию НАЙТИ в Excel.

Пример 2. Ищем слово внутри текстовой строки (с указанием стартовой позиции поиска)

Третий аргумент функции указывает на порядковый номер позиции внутри текста, с которой будет осуществлен поиск. На примере выше, функция возвращает значение “1” при поиске слова “доброе” в тексте “Доброе утро”, начиная свой поиск с первой позиции.

Вместе с тем, если мы указываем функции, что поиск следует начинать со второго символа текста “Доброе утро”, то есть функция в этом случае видит текст как “оброе утро” и ищет слово “доброе”, то результатом будет ошибка.

Если вы не указываете в качестве аргумента стартовую позицию для поиска, функция автоматически начнет поиск с начала текста.

Пример 3. Поиск слова при наличии нескольких совпадений в тексте

Функция начинает искать текст со стартовой позиции которую мы можем указать в качестве аргумента, или она начнет поиск с начала текста автоматически. На примере выше, мы ищем слово “доброе ” в тексте “Доброе доброе утро” со стартовой позицией для поиска “1”. В этом случае функция возвращает “1”, так как первое найденное слово “Доброе” начинается с первого символа текста.

Если мы укажем функции начало поиска, например, со второго символа, то результатом вычисления функции будет “8”.

Пример 4. Используем подстановочные знаки при работе функции ПОИСК в Excel

При поиске функция учитывает подстановочные знаки. На примере выше мы ищем текст “c*l”

Наличие подстановочного знака “*” в данном запросе обозначает что мы ищем любо слово, которое начинается с буквы “c” и заканчивается буквой “l”, а что между этими двумя буквами не важно. Как результат, функция возвращает значение “3”, так как в слове “Excel”, расположенном в ячейке А2 буква “c” находится на третьей позиции

Как выбрать значение с наибольшим числом в Excel

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

Если необходимо изменить условия формулы так, чтобы можно было в Excel выбрать первое максимальное, но меньше чем 70:

=70;»»;B6:B18));СТРОКА(B6:B18)-СТРОКА(B5);»»)))’ class=’formula’>

Как в Excel выбрать первое минимальное значение кроме нуля:

Как легко заметить, эти формулы отличаются между собой только функциями МИН и МАКС и их аргументами.

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

Как сбросить настройки по умолчанию в Excel?

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

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

Включите JavaScript

Как сбросить/восстановить код Visual Studio до настроек по умолчанию

Изменения, сделанные с помощью параметров Excel, будут применены ко всем файлам Excel. И если вы когда-нибудь захотите вернуться к исходным настройкам, в Excel нет единой кнопки, которая позволила бы вам вернуть все обратно. Кроме того, невозможно изменить все параметры один за другим. Итак, в этой статье Ourtechroom покажет вам, как сбросить все настройки в Excel.

Обратите внимание: перед применением изменений закройте открытые файлы Excel

Метод 1: сброс Excel с помощью редактора реестра

Редактор реестра — это графическая программа, в которой хранятся все конфигурации, связанные с операционной системой, программным обеспечением и настройками.

Изменения в файле реестра могут быть повреждены в любое время

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

Шаги следующие:

1 Нажмите Ctrl + R , чтобы открыть Run.

2 Введите regedit и нажмите Enter

3 Во всплывающем окне нажмите кнопку « Да ». Это откроет реестр, как показано ниже.рис. Введите regedit в поле «Выполнить» и нажмите кнопку «ОК».

4  На правой панели разверните HKEY_CURRENT_USER. рис. Развернуть HKEY ТЕКУЩИЙ ПОЛЬЗОВАТЕЛЬ

5 Развернуть Программное обеспечение рис. Расширенное программное обеспечение

6 Развернуть Microsoft рис. Развернуть Microsoft

7 Развернуть Office рис. Развернуть папку Office

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

HKEY_CURRENT_USER\Software\Microsoft\Office

8 Здесь вы можете получить другую версию офиса, например 14.0, 15.0 ,16.0 и скоро.

• Если вы используете Excel 2007 , затем разверните 12,0
• Если вы используете Excel 2010 , затем разверните 14,0
• , если вы используете Excel 2013 , то Expand 15,0
• .

Здесь я собираюсь сбросить Excel 2019, поэтому я расширим 16.0, как показано ниже.

9 Затем щелкните правой кнопкой мыши Excel Папка и выберите Удалить ит.рис. Щелкните правой кнопкой мыши Desire Office Version, а затем нажмите Удалить 9. 0003

10 Закройте редактор реестра

11 Откройте Excel, и вы увидите, что Excel сброшен до настроек по умолчанию.

Метод 2: сброс Excel с помощью Powershell

Powershell — мощный инструмент, и мы будем использовать командлеты для удаления этой папки из реестра. Мы удалим папку Excel с помощью Remove-Item. Шаги следующие:

1 Нажмите Win + R, чтобы открыть команду «Выполнить». 2 Введите Powershell и нажмите Enter. Это откроет программу синего экрана, которая является программой PowerShell. 3 Введите команду ниже и замените на установленную версию Office

Remove-Item -Path HKCU:\Software\Microsoft\Office\\Excel -Recurse

Если вы используете Excel 2007, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 12.0 \Excel -Recurse

Если вы используете Excel 2010, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 14.0 \Excel -Recurse

Если вы используете Excel 2013, введите:

Remove-Item -Path HKCU:\Software\Microsoft\Office\ 15. 0 \Excel -Recurse

Если вы используете Excel 2016 или более позднюю версию, введите:

Remove-Item -Path HKCU:\Software\Microsoft\ Office\ 16.0 \Excel -Recurse

Здесь я использую Excel 2019, поэтому я буду использовать последнюю команду, как показано ниже:

Что делает эта команда? Эта команда удаляет все элементы, рекурсивно присутствующие в пути «HKCU:\Software\Microsoft\Office\\Excel» файла реестра.