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

Привет форумчане.
Помогите пожалуйста дописать формулу расчета средней цены в документе:
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 месяца.

Да я с ними и спорю...., но они хотят, что бы эксель считал, а сами не хотят доступ предоставлять сотрудникам....

Добрый день.
Есть документ ИТОГО и документ СПРАВОЧНИК.
Если у сотрудника есть доступ к документу СПРАВОЧНИК, то формулы считают в документе ИТОГО и результат расчетов показывает.
Если нет доступа к документу СПРАВОЧНИК, тогда в документе ИТОГО ссылка.
Можно ли сделать так, что бы формулы считались, даже если доступа к папке у сотрудника нет, где лежит документ СПРАВОЧНИК?

Разобрался, оказывается в моем случае в документе "ИТОГИ", стояла галочка в параметрах "сохранять значения внешних связей". Благодаря которой, информация в каком-то "кэше" хранилась из других документов.... Убрал галочку, перестал доукмент обновлять информацию, если документ скопировать на другой комп.

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

Интересная статья, почитаю обязательно.
Подскажите пожалуйста, а можно ли ограничить сохранение документа?
К примеру, запретить в документе кнопку "Сохранить как"?

Добрый день.
Подскажите пожалуйста, или поделитесь возможно своим опытом, можно ли в Excel реализовать разделение видимой/педоставляемой информации для различных пользователей.
Пример: есть документ с информацией по магазинам в разных городах России и ответственными ФИО. В каждом городе есть свой менеджер. Можно ли каким-то образом сделать так, что бы пользователь видел только информацию по своему городу, или по ФИО, только свои магазины? Возможно можно настроить доступ к информации завязанной на каком-то пароле (вводимом в поле определенном), или вообще при открытии документа, если вводится пароль один, тогда и права одни.
Возможно ли реализовать как-то через сервисы у Excel, PQ или Pivot.... Возможно вообще другой способ?

Спасибо)

Конечно, поэтому я и написал про строку, куда на мой взгляд стоит обратить внимание:

"тип данных для переменной i стоит Long, а далее задаем значение для i = "Admin"" (это в изначальном макросе, не моя идея)