MS, Libreoffice & Google docs

Как вы видите, я не заморачивался с названиями.

Код:

Public SrcName

Private Sub CommandButton1_Click()

SrcName= ""

If ListBox1.ListIndex >= 0 Then

SrcName= ListBox1.List(ListBox1.ListIndex)

UserForm1.Hide

End If

End Sub

Private Sub CommandButton2_Click()

SrcName= ""

UserForm1.Hide

End Sub

Private Sub OpnButton_Click()

iOpen = Application.Dialogs(xlDialogOpen).Show

If iOpen = True Then

SrcName= ActiveWorkbook.Name

UserForm1.Hide

Else

MsgBox "отмена", vbCritical, ""

Exit Sub

End If

End Sub

Private Sub UserForm_Activate()

SrcName= ""

ListBox1.Clear

NoShow = ThisWorkbook.Windows(1).Caption

For i = 1 To Application.Windows.Count

If Application.Windows(i).Caption <> NoShow Then ListBox1.AddItem (Application.Windows(i).Caption)

Next ' enum windows

End Sub

Пикабу сожрал все отступы, это не я!

Пример использования:

Dim SrcWB As Worksheet
UserForm1.Show
If UserForm1.SrcName= "" Then Exit Sub
Windows(UserForm1.SrcName).Activate
Set SrcWB = ActiveWorkbook

P.S. Баянометр считает, что эксель на 41% похож на клубничку. Мне кажется, что он недалёк от истины.

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

Примеры использования ВПР:

- подставить из штатного расписания данные о сотруднике (адрес, оклад, телефон) по его ФИО;

- подставить из каталога продукции подробную информацию о товаре по его артикулу;

- подставить из реестра договоров по номеру договора все подробности его заключения (с кем заключен, реквизиты, сумму и т.д.);

- и так далее.

Выделяем первую ячейку (D3), куда будем вводить функцию ВПР, на вкладке «Формулы» нажимаем «Вставить функцию». В категории «Ссылки и массивы» (Lookup and Reference) находим ВПР (VLOOKUP) и жмём ОК

Появится окно ввода аргументов для функции:

Заполняем поля по очереди:

- Искомое значение – наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа (слово «Вода» из ячейки B3).

- Таблица – таблица, из которой берутся искомые значения, т.е. наш прайс-лист. Чтобы при копировании функции вниз на весь столбец ссылка на прайс не сбилась, ее нужно сделать абсолютной, нажав клавишу F4.

- Номер_столбца – порядковый номер (не буква!) столбца в прайс-листе, из которого берём значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

- Интервальный_просмотр – ЛОЖЬ (0) или ИСТИНА (1):

- ЛОЖЬ КЛАДИ - поиск точного соответствия. Если товар отсутствует в прайс-листе или написан с ошибкой, то функция выдаст ошибку #Н/Д.

- ИСТИНА - поиск приблизительного соответствия. Функция попытается найти товар с максимально похожим наименованием и выдаст цену для этого товара.

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

После ввода всех аргументов нажимаем ОК и протягиваем введенную функцию на весь столбец.

Функция ВПР выдаёт ошибку #Н/Д если:

1. Включен точный поиск (Интервальный просмотр=0) и искомого наименования нет в Таблице.

2. Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск, не отсортирована по возрастанию наименований.

3. Форматы ячеек первого столбца и образцовой таблицы отличаются (числовой и текстовый). Можно использовать функции Ч и ТЕКСТ для преобразования форматов данных.

4. Функция не может найти нужного значения, потому что в коде есть пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ для их удаления:

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));$G$3:$H$19;0)

=VLOOKUP(TRIM(CLEAN(B3));$G$3:$H$19;0)

Для подавления сообщения об ошибке #Н/Д в тех случаях, когда функция не может найти точного соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR). В результате любые ошибки, создаваемые ВПР, заменятся на нули или пустые строки:

Итого получаем, 5 минут на заполнение формулы и куча сэкономленного времени и нервов, в которое имитируем бурную деятельность и читаем Пикабу)) Всем здоровья, берегите себя!

Дополнение к посту: #comment_166519497

Задача: рассчитать необходимое кол-во кабелей.

Решение:

1. У меня программа 2007г, но этот инструмент есть в любом порядочном Excel, просто он прячется и нам надо его достать.

Заходим в Файл – Параметры – Надстройки – Управление Надстройки Excel - Перейти

В появившемся окне щелкаем на поле «Поиск решения» и нажимаем ОК. Готово!

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

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

Расшифровка:

Целевая ячейка $D$8 – ячейка, которая суммирует итоги.

Значение 160 – наше целевое значение, под которое надо подогнать длины кабелей.

Изменяя ячейки $C$3:$C$7 - тут диапазон ячеек, значения которых необходимо подбирать, чтобы выйти на заданное целевое значение (кол-во кабелей разной длины).

Ограничение $C$3:$C$7 = целое – это означает, что подобранное кол-во кабелей должно быть целым числом, а не дробным.

Ограничение $C$3:$C$7 >=0 – это означает, что подобранное кол-во кабелей должно быть больше нуля. Иногда инструмент «поиск решения» может находить отрицательные величины и считать, что он со своей задачей справился на 120%.

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

Спустя 0,5 мин. я получила такой результат.

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

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

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

Так же если у Вас возникнут трудности с применением данного инструмента – готова пообщаться и подсказать :)

P.S.: Я пользователь новый, поэтому только начала въезжать в то, как тут писать посты. Внизу в графе "возможные дубликаты" появился совсем не дубликат, а скриншот переписки. К моему посту это отношения никакого не имеет. Писала сама, пруфы есть))