Благодарю.
Я бы хотел, что бы УФ проверяло 2 условия и закрашивало зеленым (например):
- и что бы значения были во второй таблице (если нет значения, значит не применять УФ):
- и если текущее значение (факт) больше значения нижнего из таблицы (план), находя одинаковые наименования.
А через функцию СУММЕСЛИ() что вы хотели получить? суммировать значения Плана и сравнивать с Фактом?
Картинку итогового результата прилагаю к сообщению.

Привет всем. Есть пример таблицы (открыл доступ):
https://docs.google.com/spreadsheets/d/125OGYfTkexodCfcur1y0ugiK9Gi5i7x30-W-lN47tbQ/edit?usp=sharing

Для условного форматирования, хочу добавить условие И() и что бы ВПР оставался рабочим.
=И(A3>0;ВПР(A3;$A$31:$G$42;B$29;0)>0)

Раньше было так: условия для первой таблицы Больше условия: =И(A3>0;ВПР(A3;$A$31:$G$42;B$29;0)>0) Зеленым.
Если есть идеи или способы решить другим способом, с радостью воспользуюсь.

В итоге оказалось, что на всех компах в компании, где установлен 2016 Офисс, не создавались запросы корректно. А на 2019 версиях отрабатывало штатно. Переустановили на новую версию-пока полет нормальный. Не знаю, поможет решение или нет? Мне помогло!
Отдельное спасибо участникам беседы!

Ma_Ri, и вам спасибо.
georgem, спасибо за предположения, обращусь к нашим администраторам.

На любых запросах, которые я создаю. Даже самые простые, с текстовым док-ом, в котором значения 1,2,3. Не дает нормально работать с PQ.  

Добрый день. Спасибо за проявленный интерес к описанной проблеме. Нет, макросов нет. На другом компьютере (домашнем), все работает. Ошибок нет.
А проблемы возникают на работе. Думал воссоздать ситуацию дома-не получается. Может администратор у нас что-то должен разрешить для моего компьютера? Файлы расположены на рабочем столе, и доступ к ним есть.
Когда захожу в запрос, выдает картинку№1 (прилагаю к письму).

В панели формул информация:

= Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\d********k\Desktop\Текстовый документ.txt"),null,null,1251)})

Просит обновить источник. Обновляю путь к томуу же документу, выдает окно Картинка2.

При открытии, выдает ошибку и говорит о восстановлении информации. Если доверяете, нажимайте "Да".
Эта ошибка из-за запроса внутри.
Аналогичный пример, прилагаю к письму.
Такое чувство, что все запросы, мною созданные, с ошибкой...

Привет всем.
В PQ создал запрос на текстовый документ. Если нажать "Закрыть и загрузить в", выбрать лист и ячейку, выдает ошибку Сбой инициализации источника данных (скрин прилагаю). На саомо запросе, стоит ошибка "Не удалось обновить таблицу запроса: Сбой инициализации источника данных" (скрин прилагаю).
Думал дело в документах (и Экселе и блокноте). Создал простой txt и из него "Создать запрос - из файла - из текста". В самм запросе ничего не делал, сразу "Закрыть и загрузить в..". Ошибка осталась.
Версия Экселя 2016, скрин прилагаю.
Подскажите пожалуйста решение, возможно настройки какие-то стоят у меня, которые я пропустил и не отметил, возможно дело в другом....

Анастасия, благодарю. Работает формула. И отдельное спасибо за то, что напомнили про ДВССЫЛ, даже и не вспомню, когда ей пользовался в последний раз
Извиняюсь, что забыл открыть.

Привет форумчане.
Помогите пожалуйста дописать формулу расчета средней цены в документе:
https://docs.google.com/spreadsheets/d/11-yL9Lp1GgREE3Lcq3_JkRjzVYNxeS4e2v3w-EsaCP8/edit?usp=sharing

Есть структура: Наименование (игрушки, карандаши и прочее), в состав ее входят артикулы с ценами.

В ячейке C2 и ниже есть формула: =ЕСЛИ(И(A3<>"";B3="");"";ВПР(B3;'Арт.'!A:B;2;0)), которая смотрит на структуру, если Наименование есть, а артикула нет-"ПУСТО". Если нет артикула, формула тянет цену с другого листа.
Цель: вместо значения "ПУСТО" необходимо считать среднюю цену по нижним показателям. Например: по Игрушкам, считать среднюю C4:С5, в Заколках среднюю по С10:С11.
Плюс есть наименования с одним артикулом и больше артикулов не будет, там значение просто впр подтягивается.
Не представляю как посчитать среднюю по нижним значениям, когда артикулов может быть как 2, так и 10!
Был вариант, дублировать наименование для каждого артикула, потом просто использовать формулу СРЗНАЧЕСЛИМН по необходимому наименованию, но не вариант, структуру не менять(
Еще вариант был, который сейчас у меня в голове, на другом листе делать таблицу с ссылками на ячейки из нашей таблицы, а там уже структуру менять, дублируя наименования к каждому артикулу, что бы потом просто через СРЗНАЧЕСЛИМН искать результат, но хотелось бы без этого костыля...

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

Спасибо. Проверил.
В базом Bing пробовал, открыть любое объявление, скопировать ссылку, вставить в Эксельку. Не помогло. Ошибка сохраняется.
На счет блокировки. Вообще, тоже думал, но открываются сами объявления, я их вижу, и хочу добавить в эксель. Думаю, если бы ресурс был в блоке - не открывался был вообще.

Привет, еще раз.

Гиперссылки не открываются именно Авито, у меня выдает ошибку, (возможно настройки какие-то не включены), фото ошибки прилагаю. Ссылки на другие сайты открывает без проблем.


Прошу помочь с решением проблемы! Может, как и написал выше, не включил настройку какую-то? Причем из Эксельки копирую ссылку и вставляю в браузер, открывается страница

Привет! Подскажите пожалуйста, почему не открываются гиперссылки именно с авито, а с циана или другого сайта открывает? Пример прилагаю.

=--C2:INDEX(C:C;COUNTA(C:C)) вариант не работает(

16-го года версия офиса, уверен у всех в компании 16-я.

Попробовал с массивом, его все равно тянуть надо. А есть вариант, где при добавлении данных, не надо протягивать формулу? Или изначально не тянуть ее на 400к строк, а после добавлять данные

Спасибо, он не сильно будет грузить документ, если таких несколько столбцов будет?

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

P.s. изначально была формула примерная =если(B2<>"";ФОРМУЛА;""). Но тогда надо тянуть сразу до 400к строки вниз, что бы формула как бы была, но не мешала расчетам, и выводила в результату значение "Пусто", если не заполнены данные. Но очень грузит документ. А таких формул может быть несколько.

Спасибо большое за оперативную помощь.

Добрый день.
Макрос записью макроса был создан, для сводной таблицы на другой лист (лист2), но он создает всегда новый, и если информация на 2-м листе есть, он пишет ошибку.
Подскажите пожалуйста, как изменить макрос, что бы создавал на конкретный лист "Сводная" сводную, перед этим очищал этот лист, и не создавал новый лст каждый раз, когда запускается макрос?


Sub Своданя_на_Лист2()
'
' Макрос4 Макрос
'

'
   Columns("A:X").Select
   Sheets.Add
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
       "Начисления!R1C1:R1048576C24", Version:=6).CreatePivotTable TableDestination _
       :="Лист2!R3C1", TableName:="Сводная таблица6", DefaultVersion:=6
   Sheets("Лист2").Select
   Cells(3, 1).Select
   With ActiveSheet.PivotTables("Сводная таблица6").PivotFields("Тип начисления")
       .Orientation = xlPageField
       .Position = 1
   End With
   With ActiveSheet.PivotTables("Сводная таблица6").PivotFields("Артикул")
       .Orientation = xlRowField
       .Position = 1
   End With
   ActiveSheet.PivotTables("Сводная таблица6").AddDataField ActiveSheet. _
       PivotTables("Сводная таблица6").PivotFields("Количество"), _
       "Количество по полю Количество", xlCount
   ActiveSheet.PivotTables("Сводная таблица6").PivotFields("Тип начисления"). _
       ClearAllFilters
   ActiveSheet.PivotTables("Сводная таблица6").PivotFields("Тип начисления"). _
       CurrentPage = "Доставка и обработка возврата, отмены, невыкупа"
End Sub

Спасибо) аж неловко.... Все работает)  

Чуть выше описал характер ошибки

И получается, что если оставить так:
Sub FileList()
   
   Dim V As String
   Dim BrowseFolder As String
   
BrowseFolder = "\\sbc01\shares\Marketing\Trade Marketing\Фоотчеты LSM\2023\433"
Intersect(Rows("1:" & Rows.Count), ActiveSheet.UsedRange).ClearContents
   With Range("A1:С1")
       .Font.Bold = True
       .Font.Size = 12
   End With
   Range("A2").Value = "Имя файла"
   Range("B2").Value = "Путь"
   Range("c2").Value = "Дата изменения"

   ListFilesInFolder BrowseFolder, True
   
End Sub

Ругается на строку    With Range("A1:С1"), пишет ран-тайм 1004, метод Range of object'_Global'Failed

А с блоком как быть With на открытие диалогового окна? убрать?

Добрый день. Прошу помочь добавить в сам макрос путь конкретной папки, что бы каждый раз не выбирать одну и туже папку через диалоговое окно.
Путь к папке предположим: \\sbc01\shares\Marketing\Trade Marketing\Фоотчеты LSM\2023\433.
Документ с примером прилагаю.
Макрос:

Sub FileList()
   
   Dim V As String
   Dim BrowseFolder As String
   
   With Application.FileDialog(msoFileDialogFolderPicker)
       .Title = "Выберите папку или диск"
       .Show
       On Error Resume Next
       Err.Clear
       V = .SelectedItems(1)
       If Err.Number <> 0 Then
           MsgBox "Вы ничего не выбрали!"
           Exit Sub
       End If
   End With
   BrowseFolder = CStr(V)
Intersect(Rows("1:" & Rows.Count), ActiveSheet.UsedRange).ClearContents
   With Range("A1:С1")
       .Font.Bold = True
       .Font.Size = 12
   End With
   Range("A2").Value = "Имя файла"
   Range("B2").Value = "Путь"
   Range("c2").Value = "Дата изменения"

   ListFilesInFolder BrowseFolder, True
   
End Sub


Private Sub ListFilesInFolder(ByVal SourceFolderName As String, ByVal IncludeSubfolders As Boolean)

   Dim FSO As Object
   Dim SourceFolder As Object
   Dim SubFolder As Object
   Dim FileItem As Object
   Dim r As Long

   Set FSO = CreateObject("Scripting.FileSystemObject")
   Set SourceFolder = FSO.getfolder(SourceFolderName)

   r = Range("A65536").End(xlUp).Row + 1
   
   For Each FileItem In SourceFolder.Files
       Cells(r, 1).Formula = FileItem.Name
       Cells(r, 2).Formula = "=HYPERLINK(""" & FileItem.Path & """)"
       Cells(r, 3).Formula = FileItem.DateLastModified
       r = r + 1
       X = SourceFolder.Path
   Next FileItem
   
   If IncludeSubfolders Then
       For Each SubFolder In SourceFolder.SubFolders
           ListFilesInFolder SubFolder.Path, True
       Next SubFolder
   End If

   Columns("A:E").AutoFit

   Set FileItem = Nothing
   Set SourceFolder = Nothing
   Set FSO = Nothing
Columns("A:A").ColumnWidth = 20
Columns("A:A").ColumnWidth = 35
Columns("A:A").ColumnWidth = 15
End Sub

Private Sub Workbook_Open()

End Sub

Добрый день. Есть популярный макрос на поиск самого нового документа в папке.
Подскажите пожалуйста, как вывести второй по новизне файл (так сказать 2 самых новых файла)?

Sub ПримерИспользованияФункции_LastFile()
   ' Ищем на рабочем столе все файлы TXT, и выводим имя самого нового файла.
   ' Просматриваются папки с глубиной вложения не более трёх.

   Dim ПутьКПапке$, СамыйПоследнийФайл$
   ' получаем путь к папке РАБОЧИЙ СТОЛ
   ПутьКПапке = CreateObject("WScript.Shell").SpecialFolders("Desktop")
   ' получаем путь к самому новому файлу (проверяется дата последнего сохранения)
   СамыйПоследнийФайл$ = LastFile$(ПутьКПапке, ".txt", 3)

   If СамыйПоследнийФайл$ = "" Then MsgBox "Не найдено ни одного файла", vbExclamation: Exit Sub
   MsgBox СамыйПоследнийФайл$, vbInformation, "Самый свежий файл"
End Sub
Код функции:
Function LastFile$(ByVal FolderPath As String, Optional ByVal Mask As String = "", _
                  Optional ByVal SearchDeep As Long = 999)
   ' Получает в качестве параметра путь к папке FolderPath,
   ' маску имени искомых файлов Mask (будут проверены только файлы с такой маской/расширением)
   ' и глубину поиска SearchDeep в подпапках (если SearchDeep=1, то подпапки не просматриваются).
   ' Возвращает полный путь к файлу, имеющему самую позднюю дату создания
   ' (применяется рекурсивный вызов процедуры GetAllFileNamesUsingFSO)

   Dim FilenamesCollection As New Collection    ' создаём пустую коллекцию
   Set FSO = CreateObject("Scripting.FileSystemObject")    ' создаём экземпляр FileSystemObject
   GetAllFileNamesUsingFSO FolderPath, Mask, FSO, FilenamesCollection, SearchDeep    ' поиск
   Set FSO = Nothing: Application.StatusBar = False    ' очистка строки состояния Excel
   Dim maxFileDate As Double
   For Each file In FilenamesCollection ' перебираем все файлы среди найденных
       currFileDate = FileDateTime(file) ' считываем дату последнего сохранения
       ' проверяем очередной файл - не новее ли он предыдущих
       If currFileDate > maxFileDate Then LastFile$ = file: maxFileDate = currFileDate
   Next file
End Function

Function GetAllFileNamesUsingFSO(ByVal FolderPath As String, ByVal Mask As String, ByRef FSO, _
                                ByRef FileNamesColl As Collection, ByVal SearchDeep As Long)
   ' перебирает все файлы и подпапки в папке FolderPath, используя объект FSO
   ' перебор папок осуществляется в том случае, если SearchDeep > 1
   ' добавляет пути найденных файлов в коллекцию FileNamesColl
   On Error Resume Next: Set curfold = FSO.GetFolder(FolderPath)
   If Not curfold Is Nothing Then    ' если удалось получить доступ к папке
       Application.StatusBar = "Поиск в папке: " & FolderPath

       For Each fil In curfold.Files    ' перебираем все файлы в папке FolderPath
           If fil.Name Like "*" & Mask Then FileNamesColl.Add fil.Path
       Next
       SearchDeep = SearchDeep - 1    ' уменьшаем глубину поиска в подпапках
       If SearchDeep Then    ' если надо искать глубже
           For Each sfol In curfold.SubFolders    ' ' перебираем все подпапки в папке FolderPath
               GetAllFileNamesUsingFSO sfol.Path, Mask, FSO, FileNamesColl, SearchDeep
           Next
       End If
       Set fil = Nothing: Set curfold = Nothing    ' очищаем переменные
   End If
End Function

В примере по ссылке показывает только последнюю, а мне необходимо 2 результата, с последним и предпоследним документом в папке.
ПредпоследнийФайл$ = PreFile$ в Sub?

Добрый день. Есть популярный макрос, который находит последний документ (самый новый по дате создания) в папке:
https://excelvba.ru/code/LastFile
Как можно скорректировать для поиска предпоследнего документа в папке?
я вижу, что в самой функции lastfile$, есть PreFile$, и в Locals я его тоже вижу, но как этот адрес сохранить для переменной какой-то?

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

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