Привет форумчане.
Помогите пожалуйста дописать формулу расчета средней цены в документе:
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!
Был вариант, дублировать наименование для каждого артикула, потом просто использовать формулу СРЗНАЧЕСЛИМН по необходимому наименованию, но не вариант, структуру не менять(
Еще вариант был, который сейчас у меня в голове, на другом листе делать таблицу с ссылками на ячейки из нашей таблицы, а там уже структуру менять, дублируя наименования к каждому артикулу, что бы потом просто через СРЗНАЧЕСЛИМН искать результат, но хотелось бы без этого костыля...

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

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

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


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

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

Добрый день.
Есть данные, которые постоянно дополняются вниз, и могут за год составлять до 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

Добрый день. Прошу помочь добавить в сам макрос путь конкретной папки, что бы каждый раз не выбирать одну и туже папку через диалоговое окно.
Путь к папке предположим: \\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

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

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

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

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

Добрый день.
Имеется документ "ИТОГИ", в котором собирается разная информация из документов "Справочники" и рассчитываются различные показатели. Рассчитываются показатели, по номеру магазина, напрямую обращая к другому документу:

=ИНДЕКС('Z:\Rent\Сводная\[СВОДНАЯ.xlsm]Data'!$D$7:$ANT$500;$BV$23;CB8+96*(2+$BT$3))
*документ "СВОДНАЯ" один из документов "Справочников"

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


Документ "ИТОГИ" работает следующим образом: при изменении номера магазина ячейке, он подтягивает показатели из "Справочники", ну и дополнительные производит рассчеты.

Вопрос/проблема: почему при копировании документа "ИТОГИ" на другой компьютер, где нет документов "Справочники", он все равно показывает информацию по магазинам и рассчитывает показатели. Чего не должно быть, ведь нет документов, из которых он тянул бы информацию.

После копирования на другой компьютер, документ при открытии говорит, что нельзя обновить связи и т.д. Меняются формулы на диск .....'C:\Rent\Сводная\......
При изменении формулы, открывается окно с выбором документа, что говорит о том, что ни путь ни документов Excel из "Справочников" не видит.
Документ приложить не могу, скрины, формулы или видео предоставить смогу.
Изначально сделано было для того, что бы "недобросовестные сотрудники" не смогли "утащить" важную информацию по всей сети.