Как сделать поиск в Excel 2010?

Работа с табличным редактором Эксель включает в себя различные функции. Одной из таких возможностей Excel является «поиск решений», которая позволяет пользователь значительно упростить работу в табличном процессоре.

Обзор и возможности функции

Надстройка «Поиск решений» — специфическая возможность Excel 2007, 2010, 2013, 2016, которая предназначена для работы с формулой при наличии определённых условий. Описать её логику можно следуя принципу «что если?».

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

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

Включение возможности

Функция поиска решений является надстройкой Excel. Для версий Excel ниже 2010 её потребуется запускать следующим образом:

1. Открыть «Параметры» программы.
2. Перейти в раздел «Надстройки».
3. Найти «поиск решений» и клацнуть по нему дважды. В случае если пакет не установлен Office предложит загрузить его. С таким предложением потребуется согласиться.

Теперь возможность можно будет найти в разделе «Данные». Отмечена она названием «Поиск решений».

Подготовка таблицы

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

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

Как построить диаграмму Ганта в Excel, онлайн сервисах и других программах

В случае соответствия группы пункту она получает «вес» равный «1». В противном случае – «0». Это потребуется для дальнейших операций.

1. Теперь добавим 2 группы формул, которые выражают взаимодействие между группами, «весомостью» (значение) и пунктами. Пускай это будет сложение и умножение, для простоты. Таким образом мы получим сумму важности пунктов (итоговое значение для каждой из групп) и что-то вроде важности затрат для каждого из пунктов.
2. Теперь постараемся прийти к конечным решениям по затратам и итогам. Сделаем это при помощи операций сложения в соответствующих столбцах.
3. Последним действием будет сведений разницы между затратами групп.

Теперь таблицу можно считать сведённой и подготовленной. Требуется только завершить работу с функцией и настроить формулы для её эффективного срабатывания. Предположим, что нас интересует результат, при котором разница между затратами групп будет минимизирована или сравняется с нулём (ячейка «Разница»).

Запускаем функцию подбора решений и начинаем задавать ей необходимые переменные и значения:

1. Указывается, в какой ячейке находится формула, результат которой интересует пользователя.
2. Указывается результат, к которому должно привести изменений значений. Если указать в качестве значения 0, то, вероятнее всего, таблица будет заполнена нулями. Чтобы избежать подобного требуется выставить хотя-бы «1».
3. Диапазон ячеек, подлежащих изменению.
4. Ограничения, которые можно логически вписать в переменные. В указанном случае это должны быть целые числа 0 или 1.
5. Метод решения. Лучше оставить без изменений, если ранее не было произведено знакомство с алгоритмами работы.
6. Запуск поиска решений.

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

Конкретные примеры использования

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

Работа со сводными таблицами в MS Excel

Изготовление йогурта

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

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

В результате вычислений (с учётом дробного остатка, поскольку условие работы только с целыми числами добавлено не было), получилось, что эффективнее всего производить 1 и 3 йогурты, а второй полностью игнорировать.

Затраты на рекламу

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

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

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

Отсюда и вытекает главный недостаток «поиска решений». Он оперирует лишь конечной (одной) ячейкой. Чтобы максимизировать прибыль требуется работать с последней ячейкой (прибыль – всего), что сопряжено с вероятностью появления ошибки в программе, если формулы настроены неверно.

Оптимизация игрового процесса

Данный пример будет выглядеть сложнее.

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

Что такое коэффициент корреляции и как его использовать в Excel

Итоговое доступное время по условиям подбора решения ограничено 4 единицами (время устанавливаем условно, не важно будут это часы, дни или месяцы). Графа «выгода» представляет собой формулу, говорящую, что будет если выделить «х» времени на сбор определённого комплекта. Задачей Excel является оптимизация максимальной (суммарной) выгоды.

В условиях имеем: требуется получить максимальную выгоду при лимите времени. Следовательно, программа определяет на каком комплекте сфокусировать внимание. Результат предсказуем: самый дорогой комплект достоин 100% временных затрат.

Установка ограничений

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

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

Поэтому прежде чем ставить перед Excel задачу нужно сориентироваться в ней самому.

Источник: http://composs.ru/gde-naxoditsya-poisk-resheniya-v-excel/

Используем поиск решений в Excel 2010 для решения сложных задач

Значительная часть задач, которые решаются с помощью электронных таблиц, предполагают, что для обнаружения нужного результата у пользователя уже есть хоть какие-то исходные данные. Однако Exсel 2010 располагает необходимыми инструментами, с помощью которых можно решить эту задачу наоборот – подобрать нужные данные, чтобы получить необходимый результат.

«Поиск решения» и является одним из таких инструментов, максимально удобных для «задач оптимизации». И если ранее вам еще не приходилось его использовать, то сейчас самое время исправить это.

Итак – начинаем с установки данной надстройки (поскольку самостоятельно она не появится). К счастью сейчас сделать это можно достаточно просто и быстро – открываем меню «Сервис», а уже в нем «Надстройки»

Останется только в графе «Управление» указать «Надстройки Excel», а после нажать кнопочку «Перейти».

После этого несложного действия кнопка активации «Поиска решения» будет отображаться в «Данных». Как и показано на картинке

Давайте рассмотрим, как правильно используется поиск решений в Excel 2010, на нескольких простых примерах.

Пример первый.

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

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

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

А сейчас внимание – целевая ячейка С8 должна быть с помощью формул связана с искомой изменяемой ячейкой под адресом Е2. Это критично.

В примере мы связываем их используя промежуточные формулы, которые и отвечают за высчитывание премии каждому сотруднику (С2:С7).

Теперь можно активировать «Поиск решений». Откроется новое окошко, в котором нам необходимо указать необходимые параметры.

Под «1» обозначена наша целевая ячейка. Она может быть только одна.

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

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

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

«5» — кнопка перехода к интерактивным вычислениям на основе заданной нами программы.

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

Для этого можно использовать ряд определенных (и знакомых всем пользователям Excel 2010) знаков «=», «>=», «

Источник: https://AdvancedUser.ru/microsoft-excel/ispolzuem-poisk-reshenij-v-excel-2010.html

Как правильно применять поиск по таблице Excel 2010?

Кнопка Найти и выделить группы Редактирование ленты тоже имеет большой список (рис. 2.57). Поиск удобен, а иногда и просто необходим для больших таблиц! Представляете, открываете вы прайс-лист, а там сотни наименований. Не пролистывать же его весь, чтобы найти то, что вас интересует!

Найти и выделить

Например, в прайсе фирмы, продающей канцтовары, вы можете найти все карандаши (рис. 2.58).

Рис. 2.58. Ищем нужное в таблице

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

Если нажать кнопку Найти все в окне Найти и заменить, то появится список ячеек, содержащих нужное значение. Можно также скопом заменить одно наименование на другое.

Например, одним махом поменять все лабораторные работы на практические (рис. 2.59).

Рис. 2.59. Найти и заменить

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

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

Нажимая ее, вы можете «стереть» либо содержимое ячейки, либо формат ячейки. Либо и то и другое вместе. (А еще вы можете выделить ячейку и нажать клавишу Delete, это тоже очистит содержимое ячейки.) Если вы очищаете ячейку, то она остается в таблице, но становится пустой.

Если вы удаляете ячейку, вы выкидываете ее из таблицы.

Еще одна неизвестная нам кнопка в группе Редактирование — . Я думаю, вы знаете, что по законам сериалов нужно каждую серию прерывать на самом интересном месте, чтобы завтра все в едином порыве устремились к телевизору в ожидании того, что раскроется тайна! Так вот, продолжение следует. Предназначение загадочной кнопки будет раскрыто в следующей серии!

Источник: http://ladyoffice.ru/kak-pravilno-primenyat-poisk-tablice-excel-2010

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

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

Пример выгрузки

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

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

• В Excel 2003 расширенный фильтр находится в разделе Данные | Фильтр | Расширенный фильтр

• В Excel 2007 и 2010 расширенный фильтр находится в меню Данные | Дополнительно

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

• После этого откроется меню расширенного фильтра.

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

1. Переставить переключатель в поле скопировать результаты в другое место (можно не переставлять, но в этом случае ваш старый список будет заменен)
2. Проверить, что исходный диапазон выбран правильно – исходный диапазон списка всех телефонов.
3. Поставить галочку в поле Только уникальные значения
4. В поле Поместить результат в диапазон выбрать начальную ячейку куда вы хотите поместить уникальный список. (на рисунке мы выбрали ячейку B1)
5. Нажать ОК

Полученный результат Вы можете посмотреть во вложении ниже.

Очень надеемся, что наша статья помогла Вам в решении Вашей проблемы. Будем благодарны, если Вы нажмете +1 и/или Мне нравится внизу данной статьи или поделитесь с друзьями с помощью кнопок расположенных ниже.

Источник: http://smartexcel.ru/osvaivaem-excel/kak-sdelat-spisok-s-unikalnymi-znacheniyami-v-excel/index.html

Поиск решения в Excel 2010. Где находится надстройка Поиск решения Excel

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

Любое ПО для офиса – это удобный инструмент, созданный для работы с большими базами данных, количество вспомогательных элементов в которых значительно увеличилось.

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

Да и сам Microsoft Excel 2010 работает намного быстрее.

Пример тому — «поиск решения» в Excel 2010. Эта надстройка не только полезна, но и помогает сделать вашу работу с табличным редактором куда продуктивнее, позволяя решать намного более сложные задачи. Особенно он удобен для оптимизации, которая актуальна для многих современных компаний.

Почему именно 2010 версия?

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

Кроме того, в состав нынешней версии сего табличного редактора были введены новые типы формул, которые окажутся наиболее полезными бухгалтерам и экономистам. Это обстоятельство опять-таки показывает «реверанс» Microsoft в сторону именно корпоративных пользователей. Учитывая, что нижеприведенные ситуации характерны именно для них, то ничего удивительного в этом нет.

Важно! Если вы до этого не применяли «поиск решения» в Excel 2010, то данную надстройку нужно устанавливать отдельно.

Как его установить?

К счастью, сделать это совершенно несложно. Если вы используете Ексель 2003 и старше, то для этого необходимо зайти в пункт меню «Сервис», а затем выбрать там пункт «Надстройки». А где в Excel «поиск решений», если речь идет о более поздних и современных версиях программы?

В случае же применения Ексель от 2007 версии кнопку «Поиск решения» можно отыскать во вкладке «Данные».

Как с этим работать?

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

Итак, как же работает «поиск решения» в Excel 2010? Предположим, что перед вами стоит задача — распределение премии в вашей организации.

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

С чего начинается работа?

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

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

Какие параметры имеются в диалоговом окне?

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

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

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

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

Как добавить ограничения?

Если вам требуется добавить какие-то ограничения, то необходимо использовать кнопку «Добавить». Учтите, что при задании таких значений нужно быть предельно внимательным. Так как в Excel «Поиск решения» (примеры для которого мы рассматриваем) используется в достаточно ответственных операциях, чрезвычайно важно получать максимально правильные значения.

Кстати, именно от ограничений эти самые результаты и зависят. Имейте в виду, что задавать их можно не только для каких-то отдельных ячеек, но и для целых их диапазонов. Какие варианты знаков и формул можно при этом использовать?

Вами могут применяться знаки: «=», «>=», «

Источник: https://autogear.ru/article/133/768/poisk-resheniya-v-excel-gde-nahoditsya-nadstroyka-poisk-resheniya-excel/