Спасибо за ценную информацию!

Но особенно благодарю за это Теперь буду понимать почему может тормозиться открытие листа

ПавелW, спасибо большое за оба варианта!!! А почему ВПР в данном случае предпочтительней? Где то читал, что на больших массивах ИНДЕКС работает быстрее ВПР.

Всем привет! Прошу помощь в доработке формулы. На листе1 есть диапазон, в котором через функцию Индекс нужно получить значение из таблицы "Склады" (Лист2). Речь идет о параметрах складских помещений, площадь, высота и т.д. Сейчас формула реализована так:

На Листе1 в соответствующей ячейке (в примере площадь) введена формула =ИНДЕКС(Склады[С-1];1) . Как видно в функции ИНДЕКС массив задан как "Склады[C-1]". По сути [С-1] это название столбца в таблице "Склады". На Листе1 также есть ячейка B2 с текстовым значением  "С-1" - название склада. В итоге требуется чтобы название склада для формулы =ИНДЕКС(Склады[С-1];1) бралось из ячейки B2 , что то типа =ИНДЕКС(Склады["&B2&"];1). Понятное дело формула не работает. Прошу помощи коллеги, как правильно воткнуть ссылку на значение из ячейки B2, чтобы название столбца бралось из текстового значения данной ячейки? Файл пример во вложении.

MikeVol, Alex, спасибо вам большое за помощь! Извините, что сразу не ответил.  Времени не хватает (впрочем, как и у всех в этой жизни). Alex, ваш вариант интересен, но, к сожалению, не подойдет, поскольку файл источника в реале доступен только с правами на просмотр, никаких редактирований не допускается. Повторюсь, сама концепция интересная.
MikeVol, пробую ваш код для перевода текста в дату(число).

MikeVol, это вам спасибо

Спасибо за информацию! На забугорных форумах видел что средствами SQL как то получают адрес занимаего пространства, может там не ListObject и просто именованный диапазон был  и на основании этого делалась выборка Select. Ну да ладно, в принципе явное указание адреса диапазона пока не ставило для меня проблем. Но вопрос с конвертированием текста в число остался, (текст в дату).

Всем привет! Помогите пожалуйста решить такую задачу. Поскольку речь идет про SQL запрос то опишу путь файлов. Для простоты на диске размещена папка C:\Test\  В папке два файла: Source.xlsx - Файл Источник и Import.xlsx - Файл импортирующий данные с встроенным SQL запросом.

Файл Source.xlsx на листе "Источник" имеет Таблицу "Data1", причем таблица с заголовком размещена не на первой строке листа, а на строку ниже, что несколько усложняет импорт данных с листа, т.к. стоит задача импортировать только таблицу "Data1", а не все данные с листа. Также выяснилось, что "Data1" в перовом столбце с заголовком [Дата] имеет изъян, а именно дата в ячейках содержится в текстовом формате, то есть воспринимается Экселем как текст, а не как дата.

В итоге стоит задача импортировать данные из книги Source.xlsx непосредственно из таблицы "Data1" , а не со всего листа в книгу Import.xlsx на Лист1.
При этом требуется преобразование данных в столбце [Дата] из текстовой строки в формат даты (типа 16.01.2026)  

Я немного понимаю VBA, но в SQL полнейший ноль.

С помощью "гугл окей" и Microsoft Query максимум, что мог родить такие параметры

Строка подключения
DSN=Excel Files;DBQ=C:\Test\Source.xlsx;DefaultDir=C:\Test;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;

Тип команды SQL

Текст команды SELECT * FROM [Источник$A2:F30] - только так получилось импортировать полноценную таблицу с заголовком, хотя признаю "топорность" метода.

Как прописать текст команды SQL для импорта данных только из  "Data1" (что то типа SELECT * FROM [Data1])не указывая явно диапазон как это сделано выше и произвести преобразование текста в столбце [Дата] в дату (что то типа CAST ("Data1"[Дата]).

Я знаю как это сделать в Power Query на раз-два, но пользуюсь Excel 2007, так что нужен SQL запрос. И еще пожалуйста, кто нибудь сориентируйте есть ли какой нибудь краткий справочник по синтаксису SQL именно для связки c Excel? Пока гуглил выяснилось что язык SQL имеет несколько разновидностей (SQL Lite, PostgreSQL, MySQL...) , какой именно SQL используется для построения запросов в среде Excel?

Файлы во вложении.


 

Sanja, Здорово! Спасибо за расширение функционала. VBA наше все!

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

БМВ, я мало что понял, НО РАБОТАЕТ!!! ОГРОМНЕЙШЕЕ СПАСИБО!

Вот переведенный вариант формулы русскоязычной версии.

=СУММ(ЕСЛИОШИБКА(ЕСЛИ(Таблица1[Товар]=H26;Таблица1[Масса, кг])/СЧЁТЕСЛИМН(Таблица1[Товар];H26;Таблица1[Дата];$E$24;Таблица1[Принято/Отгружено];$I$24;Таблица1[Время];">="&$F$24;Таблица1[Время];"<="&$G$24;Таблица1[№];Таблица1[№]);0))

Причем работоспособность сохранилась даже в упрощенной записи части "ЕСЛИ". Я верно понимаю - здесь логика такая. <ЕСЛИ> фильтрует данные  в массиве по товару <Яблоки> и соответствующие им данные по весам и делит на массу отфильтрованную через СЧЁТЕСЛИМН. Далее результаты деления складываются через СУММ. Функция ЕСЛИОШИБКА сохраняет работоспособность формулы в случае  ошибки.    

Всем привет! Требуется помощь. На листе таблица эксель (структурированные ссылки) где фиксируется приемка отгрузка товара с привязкой по датам и времени. Каждая операция приемки-отгрузки прописывается с порядковым номером по столбцу <C>.   Если происходит операция  приемки-отгрузки одного товара от одного поставщика на несколько складов, то порядковый номер операции одинаковый. Например, 01.12.2025  13:00 от Поставщика1 поступили яблоки в количестве 1000 кг, которые отгрузили на Склад №2, Склад №3, Склад №4, в таблице строки 3,4,5 имеют повторяющийся порядковый номер <2> по столбцу <C>. По итогам смены 01.12.2025 было принято 1300 кг яблок. Для подсчета итогов используется функция СУММЕСЛИМН. Очень удобный инструмент, но не могу настроить в этой функции отбор по уникальным критериям порядкового номера операции по столбцу <C>. Функция считает что яблок было принято 3000 кг
Вот формула в одной из итоговоых ячеек по яблокам.

=СУММЕСЛИМН(Таблица1[Масса, кг];Таблица1[Товар];H26;Таблица1[Дата];$E$24;Таблица1[Принято Отгружено];$I$24;Таблица1[Время];">="&$F$24;Таблица1[Время];"<="&$G$24;Таблица1[№];">0")

Выделил красным кусок, который отвечает за соответствие критерию по Столбцу <C>. Конечно, понимаю, что это тупо, но я специально его ввел в формулу чтобы показать в чем затык. Не могу понять как здесь настроить отбор по уникальным значениям

  В итоге - Как настроить функцию <СУММЕСЛИМН> , чтобы суммировалась масса с учетом уникального порядкового номера по столбцу <C> ? Прошу помощи. Файл прилагаю.

Vanin00, Спасибо большое за вашу помощь! Особенно за файл с примером. Загвоздка в том, что хотелось бы универсальный инструмент. Я чаще всего работаю с Excell 2007, а там только Web Query. Но за пример еще раз большое спасибо, посмотрю на примере как работает PQ

Форумчание привет! Давно сталкиваюсь с такой проблемой - при импорте табличных данных из интернета  (Web Query) из электронного магазина Эксель автоматически конвертирует строки содержащиеие длинный числовой ряд в экспоненциальный формат типа 1,41E+129; 6,31E+36; 4,40E+19; 6,12E+65. До сих пор вопрос решался просто - после импорта данных менял формат ячейки с экспоненциального на текстовой и получал полный числовой ряд. Отличие между числом как число и числом как текст я понимаю, знаю также, что Эксель все числа больше 11 символов переводит в экспоненциальный формат. Почему вопрос возник сейчас. С недавнего времени электронный магазин стал размещать в таблице реестровые номера контрактов, которые содержат символ "E" латиницей. При импорте Эксель делает очень странную предварительную конвертацию, видимо двойную, пологая, что реестровый номер контракта изначально это не текст, а экспоненциальное число. Простой перевод формата ячейки из экспоненциального в текстовый не работает, все данные после символа "E" заменяются нулями, что приводит к потере данных о номере контракта. Файл с импортом данных во вложении, в файл уже внедрено "подключение" (Данные-подключения). Также на листе после строк выгузки данных прямая ссылка на источник данных электронного магазина.
Вопрос как - как настроить импорт данных таблицы, чтобы по столбцу реестровый номер контракта импортировались данные сразу в формате текст без преобразования в  экспоненциальный формат? Прошу помощи.  

doober, Огромное спасибо! То что доктор прописал! Это как таблетка от головной боли, выпил голова не болит, но причины не знаешь). Если можно в формате этой темы, пожалуйста, поделитесь профессиональным секретом, как вы пришли к такому решению? Нужно знать JAVA или вы используете что то типа сниффера Fiddler, или и то и другое?    

Всем привет! Пытаюсь на коленке сделать простенький парсер из Экселя под Электронный магазин Ленинградской области. К примеру, вот адрес страницы закупки https://zakupki.lenreg.ru/ProductRequestGroup/Index/111526 . Нетрудно догадаться, что последние шесть цифр - это по сути номер закупки. Далее встроенным в Эксель MS Query (Не PowerQuery, я все еще на Excel 2007) произвожу загрузку через <Получить внешние данные из Веба>. В целом большую часть информации в таком запросе удается получить, даже с учетом того, что Эксель подключается к сайту через устаревший IE. Но не везде так везет, в других магазинах без поддержки JAVA страница отказывается грузится. Но здесь пока прокатывает. Итак, выяснилось что  MS Query не цепляет два важных для меня поля: <Адрес поставки> и <Ответственное лицо>. При чем в окне предварительного просмотра перед загрузкой IE эти поля отображает, но в выгрузке они отсутствую. В итоге загрузил HTML код страницы в текстовм формете. И вот что обнаружил, информация в этих полях генерируется при загрузке страницы через  ajax запрос. Вот куски HTML кода этих полей:
Скрипт для генерирования <Адрес поставки>, где видно что в нечто похожее на переменную (a) записывается результат запроса ajax, и при некоем ответе на запрос в переменную записывается значение: либо извлеченный из какой то закрытой базы данных Адрес поставки, либо, если там пусто записывается значение  'Адрес не указан' :И ниже второй скрипт для генерирования поля <Ответственное лицо>. Все построено по логике описаной выше
   В переменную (a)  записывается ответ на запрос ajax с выгрузкой результата - 'Конкретное ФИО' либо 'Ответственное лицо не указано'. Видно также, что для индексации запрос ajax использует все тот же шестизначный номер <idGroup: "111526">, что указан в гиперссылке - последние 6 цифр. Собственно стоит задача сделать макрос на формирование  запроса и получением результата текстовая строка с записью в переменную, например для адреса доставки - DeliveryAddress, для ФИО - ResponcibleUser. Пытался найти какое нибудь похожее решение в интернете, нашел вот такой пример тыц . Но ничего там не понял, кроме того что используется Get. Прошу помощи уважаемые форумчане!

БМВ, спасибо, теперь логика понятна. Сейчас уже пробую.

Ну вот никак на вашу статью гугл не давал ссылку в результатах поиска. Всё статьи были о стандартном способе через .Address. Огромное спасибо за материал, как раз описан алгоритм кода если есть символ #. Уже изучаю

Разбираюсь с вариантом Тут что то с синтаксисом в части условия iif. Если я верно понял, то здесь проверяется содержит ли часть субадреса символ "." , и если да, то выполняется кусок кода присоединяющий символ "#" и правую часть интернет адреса из гиперссылки.  Но, во-первых, двойные кавычки vbe не пропускает и перестает ругаться,  если оставить одинарные. Однако в таком виде обработка условия всегда возвращает ложь, и код не возвращает субадрес. Конечно, вместо точки ставил символ "#", но не срабатывает, всегда ложь. Помогите люди добрые, как здесь прописать условие проверки на содержание символа "#".

ZVI, спасибо за вариант, но гиперссылке "вшита" в текстовое значение и при применении вашего варианта возвращается просто текстовое значение ячейки.

БМВ,  вариант интересный! Читал мануал по MS про субадресс, пришел к выводу что он возвращает адрес на диапазон на листе эксель, про https  там ничего не говорилось. Ваш способ немного смущает тем что символ # будет дорисовываться искусственно, даже в том случае если его в гиперссылке нет. На листе не все гиперссылки имеют неиспользуемые символы. Однако ж можно наверное условия какие нибудь прикрутить. Спасибо в любом случае, дали хорошее направление для поиска!

Всем привет! Столкнулся с такой проблемой. На листе есть https гиперссылки, в адресе которых включен символ #. Так вот, при попытке извлечь адрес в текстовую строку с помощью vba извлекается только часть до символа #, все остальное отсекается. Например в ячейке A1 содержится значение с привязанной гиперссылкой: https://internet.site/#/567875. Создаю процедуру

Скрытый текст
Sub HyperlinkExtract () Dim HL As Hyperlink Set HL=Range ("A1").Hyperlinks(1) Msgbox HL.Address End Sub

по итогам свойство .Address извлекает только https://internet.site/ остальное не видит. Пробовал через Replace(HL.Address, "#" ,"") не помогает,т.к. .Address упирается в символ # и не пускает для обработки дальше. Нагуглил что символ # не редкость и нужен для навигации по странице, однако получается, что не всю кодировку url понимает .Address. Подскажите пожалуйста, как извлечь всю строку с учётом всех символов в ссылке?

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

Hugo, огромное спасибо! Работает! Я догадывался что нужно как то использовать функцию Поиск, но как её здесь прикрутить ко всему дипазону было не понятно. Скажу больше, я и сейчас мало что понял, кроме того что проверка выполнения условия по маске происходит только в ячейке G3, причем без абсолютной привязки столбца через $, а заливка работает по всему диапазону. Магия  

Здравствуйте товарищи! Есть задача с помощью УФ выделять цветом строки в таблице с условием частичного совпадения текста. Примеры, которые находил работают исключительно с точным совпадением текста. Для меня же требуется, чтобы под условие подподали текстовые значения с маской
"*Солн*" по столбцу G. Однако при попытке изменить формулу условного форматирования  

=$G3="Скид: 0 - ООО ""Солнышко"""

в такой вид =$G3="*Солн*"

заливка не работает. Помогите кто знает, как прикрутить маску в условное форматриование.

gling, огромное спасибо! Вариант рабочий!

БМВ, sokol92, спасибо за ценную наводку! По результатам отпишусь обязательно!

Всем привет! Создал пользовательскую форму (UserForm) в VBA. Форма Нужна для работы в Word. Понимаю, что форум по Excell, но общие приёмы, которые используется в VBA Excell подходят и для VBA Word. Суть проблемы. Форма содержит большое количество объектов TextBox расположенных вертикально в стобец. Объектов много и на форме не помещаются. Помогла ветка форума из архива с помощью Свойства ScrollBars форму можно скролить и разместить нужное количество объектов. Все хорошо, одно "НО". В форме есть группа кнопок по нижней границе и они скроляться со всеми объектами формы. Коврялся в свойствам этих кнопок хотел отключить их от общего скролла, но не нашел. Задача стоит, чтобы кнопки всегда находились поверх формы и нескролились.Подскажите люди добрые как поставить запрет на скролл моим кнопкам?  

Бахтиёр, Спасибо огромное!!!

Kuzmich, спасибо, за наводку, но нужно решение через формулу, не макросом.  Вариант от Бахтиёр - то что нужно!