Как из множества excel
Обновлено: 06.07.2024
Всем привет! В Excel есть множество способов облегчить и ускорить свою работу, прибегая к некоторым уловкам и лайфхакам. Об этих моментах не рассказывается подробно в инструкциях и встроенной справке, поэтому по ходу публикаций о стандартной работе в программе, я также буду выкладывать разных хитрости и трюки )) Если вы считаете, что для вас это будет полезно, следите за обновлениями!
Итак. Все знают, что такое рабочие книги в Excel — это модельное представление. Большинство знают, что много открытых одновременно рабочих книг, могут вызывать большое количество загвоздок и помех. Особенно это касается если необходимо работать со всеми сразу. Потому первый сегодняшний трюк о том, как облегчить ваши страдания при работе с множеством открытых окон рабочих книг.
Чтобы одновременно отобразить на экране несколько рабочих книг, в любой из открытых книг выберите Окно → Упорядочить все . Если установлен флажок «Только окна текущей книги» уберите его, а затем установите нужный переключатель и щелкните на кнопке ОК.
Если выбрать отметку Рядом , ваши книги будут располагаться рядом черепицей.
Выбрав отметку Сверху вниз , вы расположите книги друг над другом.
Выберите отметку Слева направо , чтобы расположить книги поочередно, друг с другом.
Отметка Каскадом позволит расположить все книги поверх друг друга.
Расположив книги удобнее, вы легко сможете копировать, вставлять, перетаскивать и изменять в них данные из одной книги в другую, если это нельзя сделать другими способами типа формул.
Если вы хотите повторить созданное представление, то можно сохранить его как рабочую область. Для этого выберите команду Файл → Сохранить рабочую область . Файл будет сохранен с расширением .xlw, а не стандартным .xlsx. Если вы захотите позже открыть те же файлы, откройте файл .xlw , и ранее сохраненное представление чудесным образом будет восстановлено для всех книг.
Затратив немного времени на настройку личных представлений для задач, требующих одновременного открытия нескольких рабочих книг, вы со временем убедитесь, что справляться с этими задачами станет легче. Для разных задач можно конструировать разные представления, в зависимости от типа задачи или вашего самочувствия в конкретный день.
На этом у меня всё. Если вам понравился сегодняшний трюк, ставьте лайки и подписывайтесь на канал чтобы не пропустить еще более интересных материалов )) Спасибо!
Пусть имеется несколько множеств — , , , количество элементов в которых может быть различно. Требуется составить все возможные комбинации элементов этих множеств таким образом, чтобы в комбинации присутствовал только один элемент из каждого множества: (A1, B1, C1), (A1, B2, C1), (A1, B2, C2), … Также подсчитаем в MS EXCEL количество таких комбинаций.
Начнем со множеств, состоящих из последовательностей целых чисел без повторов с шагом =1. Например, или . В файле примера создана форма для 4-х множеств, состоящих из 6 элементов (максимум).
В пределах одного множества все элементы должны быть разными, а элементы, принадлежащие разным множествам, могут повторяться. Количество элементов во множествах может быть различным.
Число различных комбинаций, содержащих по одному элементу из каждого множества, равно произведению количеств элементов каждого множества. Например, как следует из картинки выше, число комбинаций =6*4*3*2=144.
С помощью простых формул можно найти все эти 144 комбинации (см. файл примера лист Пример Числа )
Изменяя количество элементов во множествах, будут автоматически изменяться число комбинаций и сами комбинации (необходимо следить, чтобы количество строк с формулами было достаточным для отображения всех комбинаций).
При необходимости, в файле примера можно легко увеличить как количество множеств, так и количество элементов в каждом из множеств. Для этого нужно скопировать формулы в ячейки справа.
Примечание : Если все множества идентичны (состоят из одинаковых элементов), то задача сводится к Размещению с повторениями . Действительно, пусть в каждом из k множеств по n элементов. Произведение количеств элементов (n) k раз, равно n^k, что равно количеству Размещений с повторениями.
Множества, состоящие из букв (символов)
Если необходимо составить комбинации нечисловых элементов, например, взяв множества ; , то вышеуказанный подход также можно использовать. Для этого произвольным образом нужно сопоставить буквам последовательные числовые значения.
После того, как все комбинации для числовых эквивалентов будут построены, необходимо сделать обратное преобразование: каждому числу сопоставить соответствующую букву (см. лист Пример Буквы1 ).
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Сочетаниями из n различных элементов по k элементов называются комбинации, которые отличаются хотя бы одним элементом. Например, ниже перечислены ВСЕ 3-х элементные сочетания, взятые из множества, состоящего из 5 элементов :
(1; 2; 3); (1; 2; 4); (1; 2; 5); (1; 3; 4); (1; 3; 5); (1; 4; 5); (2; 3; 4); (2; 3; 5); (2; 4; 5); (3; 4; 5)
Примечание : Это статья о подсчете количества сочетаний с использованием MS EXCEL. Теоретические основы советуем прочитать в специализированном учебнике. Изучать сочетания по этой статье - плохая идея.
Отличие Сочетаний от Размещений
В отличие от Размещений следующие 3-х элементные комбинации (1; 2; 3); (1; 3; 2); (2; 1; 3); (2; 1; 3); (3; 2; 1); (3; 1; 2) считаются одинаковыми, и в набор Сочетаний включается только одна из этих комбинаций. Очевидно, что для тех же n и k число Сочетаний всегда меньше чем число Размещений (так как при размещениях порядок важен, а для сочетаний - нет), причем в k! раз.
Подсчет количества Сочетаний
Число всех Сочетаний из n элементов по k можно вычислить по формуле:
Например, количество 4-х элементных комбинаций из 6 чисел равно 15=6!/(4!(6-4)!)
Примечание : Для Сочетаний из n элементов по k также используется и другая запись:
В MS EXCEL для подсчета количества комбинаций без повторов существует специальная функция ЧИСЛКОМБ() , английское название функции - COMBIN(). Для предыдущего примера формула =ЧИСЛКОМБ(6;4) , разумеется, также вернет 15. Альтернативная формула для подсчета сочетаний =ФАКТР(6)/ФАКТР(6-4)/ФАКТР(4) .
Очевидно, что k меньше или равно n, т.к. нельзя выбрать из множества элементов n больше элементов, чем в нем содержится (предполагается, что элементы после выбора обратно не возвращаются). При k=n количество сочетаний всегда равно 1.
Примечание : О Сочетаниях с повторениями (с возвращением элементов) можно прочитать в статье Сочетания с повторениями: Комбинаторика в MS EXCEL
Вывод всех комбинаций Сочетаний
В файле примера созданы формулы для вывода всех Сочетаний для заданных n и k.
Задавая с помощью элементов управления Счетчик количество элементов множества (n) и количество элементов, которое мы из него выбираем (k), с помощью формул можно вывести все Сочетания.
В файле примера не забывайте увеличивать количество строк с формулами, чтобы поместились все ваши комбинации. Для этого выделите последние ячейки с формулами (сочетание №330) и скопируйте их вниз на нужно количество строк. При увеличении строк с формулами размер файла быстро растет, а скорости пересчета листа падает. Если строк 4 тысячи, то размер файла составляет около 2 Мб.
Задача
Автовоз может перевозить по 4 легковые машины. Необходимо перевезти 7 разных машин (LADA Granta, Hyundai Solaris, KIA Rio, Renault Duster, Lada Kalina, Volkswagen Polo, Lada Largus). Сколькими различными способами можно заполнить первый автовоз? Конкретное место машины в автовозе не важно.
Нам нужно определить число Сочетаний 7 машин на 4-х местах автовоза. Т.е. n=7, а k=4. Оказывается, что таких вариантов =ЧИСЛКОМБ(7;4) равно 35.
Воспользуемся файлом примера (ссылка внизу статьи) , чтобы наглядно убедиться, что мы решили задачу правильно.
Произвольным образом сопоставим маркам машин числовые значения и сделаем сокращения названий марок: LADA Granta (LG=1), Hyundai Solaris (HS=2), …
Выставив в ячейках В5 и В6 значения 7 и 4 соответственно, определим все варианты размещений машин в автовозе (см. столбцы AJ:AM).
Примечание : О Перестановках можно прочитать в статье Перестановки без повторений: Комбинаторика в MS EXCEL , а о Размещениях в статье Размещения без повторений: Комбинаторика в MS EXCEL .
Статистическая обработка данных – это сбор, упорядочивание, обобщение и анализ информации с возможностью определения тенденции и прогноза по изучаемому явлению. В Excel есть огромное количество инструментов, которые помогают проводить исследования в данной области. Последние версии этой программы в плане возможностей практически ничем не уступают специализированным приложениям в области статистики. Главными инструментами для выполнения расчетов и анализа являются функции. Давайте изучим общие особенности работы с ними, а также подробнее остановимся на отдельных наиболее полезных инструментах.
Статистические функции
Как и любые другие функции в Экселе, статистические функции оперируют аргументами, которые могут иметь вид постоянных чисел, ссылок на ячейки или массивы.
Запустить Мастер функций можно тремя способами:
-
Кликнуть по пиктограмме «Вставить функцию» слева от строки формул.
Затем нужно кликнуть по полю «Категория» и выбрать значение «Статистические».
После этого откроется список статистических выражений. Всего их насчитывается более сотни. Чтобы перейти в окно аргументов любого из них, нужно просто выделить его и нажать на кнопку «OK».
Для того, чтобы перейти к нужным нам элементам через ленту, перемещаемся во вкладку «Формулы». В группе инструментов на ленте «Библиотека функций» кликаем по кнопке «Другие функции». В открывшемся списке выбираем категорию «Статистические». Откроется перечень доступных элементов нужной нам направленности. Для перехода в окно аргументов достаточно кликнуть по одному из них.
Оператор МАКС предназначен для определения максимального числа из выборки. Он имеет следующий синтаксис:
В поля аргументов нужно ввести диапазоны ячеек, в которых находится числовой ряд. Наибольшее число из него эта формула выводит в ту ячейку, в которой находится сама.
По названию функции МИН понятно, что её задачи прямо противоположны предыдущей формуле – она ищет из множества чисел наименьшее и выводит его в заданную ячейку. Имеет такой синтаксис:
СРЗНАЧ
Функция СРЗНАЧ ищет число в указанном диапазоне, которое ближе всего находится к среднему арифметическому значению. Результат этого расчета выводится в отдельную ячейку, в которой и содержится формула. Шаблон у неё следующий:
СРЗНАЧЕСЛИ
Функция СРЗНАЧЕСЛИ имеет те же задачи, что и предыдущая, но в ней существует возможность задать дополнительное условие. Например, больше, меньше, не равно определенному числу. Оно задается в отдельном поле для аргумента. Кроме того, в качестве необязательного аргумента может быть добавлен диапазон усреднения. Синтаксис следующий:
МОДА.ОДН
Формула МОДА.ОДН выводит в ячейку то число из набора, которое встречается чаще всего. В старых версиях Эксель существовала функция МОДА, но в более поздних она была разбита на две: МОДА.ОДН (для отдельных чисел) и МОДА.НСК(для массивов). Впрочем, старый вариант тоже остался в отдельной группе, в которой собраны элементы из прошлых версий программы для обеспечения совместимости документов.
МЕДИАНА
Оператор МЕДИАНА определяет среднее значение в диапазоне чисел. То есть, устанавливает не среднее арифметическое, а просто среднюю величину между наибольшим и наименьшим числом области значений. Синтаксис выглядит так:
СТАНДОТКЛОН
Формула СТАНДОТКЛОН так же, как и МОДА является пережитком старых версий программы. Сейчас используются современные её подвиды – СТАНДОТКЛОН.В и СТАНДОТКЛОН.Г. Первая из них предназначена для вычисления стандартного отклонения выборки, а вторая – генеральной совокупности. Данные функции используются также для расчета среднего квадратичного отклонения. Синтаксис их следующий:
НАИБОЛЬШИЙ
Данный оператор показывает в выбранной ячейке указанное в порядке убывания число из совокупности. То есть, если мы имеем совокупность 12,97,89,65, а аргументом позиции укажем 3, то функция в ячейку вернет третье по величине число. В данном случае, это 65. Синтаксис оператора такой:
НАИМЕНЬШИЙ
Данная функция является зеркальным отражением предыдущего оператора. В ней также вторым аргументом является порядковый номер числа. Вот только в данном случае порядок считается от меньшего. Синтаксис такой:
РАНГ.СР
Эта функция имеет действие, обратное предыдущим. В указанную ячейку она выдает порядковый номер конкретного числа в выборке по условию, которое указано в отдельном аргументе. Это может быть порядок по возрастанию или по убыванию. Последний установлен по умолчанию, если поле «Порядок» оставить пустым или поставить туда цифру 0. Синтаксис этого выражения выглядит следующим образом:
Выше были описаны только самые популярные и востребованные статистические функции в Экселе. На самом деле их в разы больше. Тем не менее, основной принцип действий у них похожий: обработка массива данных и возврат в указанную ячейку результата вычислительных действий.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Читайте также: