Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Сортировка без потерь формул - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка без потерь формул (Формулы/Formulas)
Сортировка без потерь формул
marmusia86 Дата: Среда, 06.07.2022, 12:16 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Доброго времени суток! Такая проблема: при новой сортировке по возрастанию формулы, которые ссылались на эту ячейку, не меняют динамический адрес.
Чтобы быть точнее, прикрепляю файл с примером: там стоит задача сделать новую сортировку по возрастанию по столбцу В (выделен желтым), так чтобы формулы в столбце I "Остатки итоговые" ссылались на те ячейки, к которым были прикреплены изначально. Буду очень рада помощи.
К сообщению приложен файл: 1125467.xlsx(448.6 Kb)


Сообщение отредактировал marmusia86 - Среда, 06.07.2022, 15:47
 
Ответить
СообщениеДоброго времени суток! Такая проблема: при новой сортировке по возрастанию формулы, которые ссылались на эту ячейку, не меняют динамический адрес.
Чтобы быть точнее, прикрепляю файл с примером: там стоит задача сделать новую сортировку по возрастанию по столбцу В (выделен желтым), так чтобы формулы в столбце I "Остатки итоговые" ссылались на те ячейки, к которым были прикреплены изначально. Буду очень рада помощи.

Автор - marmusia86
Дата добавления - 06.07.2022 в 12:16
китин Дата: Среда, 06.07.2022, 13:19 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 6895
Репутация: 1056 ±
Замечаний: 0% ±

Excel 2007;2010;2016
нет файла


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениенет файла

Автор - китин
Дата добавления - 06.07.2022 в 13:19
marmusia86 Дата: Среда, 06.07.2022, 15:48 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Спасибо, добавила
 
Ответить
СообщениеСпасибо, добавила

Автор - marmusia86
Дата добавления - 06.07.2022 в 15:48
bigor Дата: Среда, 06.07.2022, 16:12 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 681
Репутация: 139 ±
Замечаний: 0% ±

нет
Доброго.
Не воспроизведу описываемую проблему. Как ссылается i5 на =H5-J5-J223-J309 до сортировки, так и после на них же ссылается, т.е.
формулы в столбце I "Остатки итоговые" ссылались на те ячейки, к которым были прикреплены изначально

или Вы хотите, что бы формула была привязана не к ячейкам, а к значениям?
 
Ответить
СообщениеДоброго.
Не воспроизведу описываемую проблему. Как ссылается i5 на =H5-J5-J223-J309 до сортировки, так и после на них же ссылается, т.е.
формулы в столбце I "Остатки итоговые" ссылались на те ячейки, к которым были прикреплены изначально

или Вы хотите, что бы формула была привязана не к ячейкам, а к значениям?

Автор - bigor
Дата добавления - 06.07.2022 в 16:12
marmusia86 Дата: Среда, 06.07.2022, 16:27 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Да, именно к значениям. Т.е. вот внизу есть формула для спрея+масла, которая после изменения сортировки должна ссылаться на спрей и масло (соответственно).


Сообщение отредактировал marmusia86 - Среда, 06.07.2022, 16:37
 
Ответить
СообщениеДа, именно к значениям. Т.е. вот внизу есть формула для спрея+масла, которая после изменения сортировки должна ссылаться на спрей и масло (соответственно).

Автор - marmusia86
Дата добавления - 06.07.2022 в 16:27
bigor Дата: Среда, 06.07.2022, 17:09 | Сообщение № 6
Группа: Проверенные
Ранг: Ветеран
Сообщений: 681
Репутация: 139 ±
Замечаний: 0% ±

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

Автор - bigor
Дата добавления - 06.07.2022 в 17:09
marmusia86 Дата: Четверг, 07.07.2022, 10:36 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - marmusia86
Дата добавления - 07.07.2022 в 10:36
Gustav Дата: Пятница, 08.07.2022, 17:26 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 2136
Репутация: 840 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Что-то совсем какая-то беда... А текстовый формат на колонку I с какой целью установлен? Там же формулы, которые, вроде, пересчитываться должны...

Я попытался зайти в ячейку и включить с помощью клавиши F4 полную абсолютную адресацию всех слагаемых. Т.е. в ячейке I73 сейчас формула вида:
Код
=H73-J73-J218-J183

а должна стать:
Код
=$H$73-$J$73-$J$218-$J$183

По идее, это должно помочь (но на 100 % не уверен - надо проверять). Но меня встретил поголовный текстовый формат... %)
Разумеется, эту операцию по абсолютизации адресов надо будет применить ко всем ячейкам колонки I до сортировки.


[p.s.]Хотя, скорее всего, я НЕ ПРАВ. И всё сложнее - надо все адреса не из текущей строки превращать в формулы с ИНДЕКС/ПОИСKПОЗ, как это обсуждалось в этой теме: http://www.excelworld.ru/forum/2-29353-1

Адреса внутри формулы, ссылающиеся на ячейки текущей строки, после сортировки перестроятся сами. А ссылки на другие строки надо будет превратить в комбинацию функций ИНДЕКС/ПОИСKПОЗ. Для этого также надо будет держать отдельный столбец с зафиксированными первоначальными номерами строк, но, вроде, такой столбец уже имеется - столбец A.[/p.s.]

Т.е. в ячейке I73 сейчас формула вида:
Код
=H73-J73-J218-J183

а должна будет стать:
Код
=H73-J73 - ИНДЕКС(J:J;ПОИСКПОЗ(218;A:A;)) - ИНДЕКС(J:J;ПОИСКПОЗ(183;A:A;))


Чтобы не мучаться с ручной заменой в каждой ячейке колонки I, можно попробовать (где-нибудь "сбоку") сочинить формулу или макрос VBA для выполнения подобной трансформации. Т.е. надо будет к номерам строк, не совпадающим с текущей, "пристраивать" функции ИНДЕКС и ПОИСКПОЗ. В помощь - функция Ф.ТЕКСТ:
Код
=Ф.ТЕКСТ(I73)
а также клавиатурная комбинация Ctrl+~ (контрол+тильда) для переключения ячеек рабочего листа в режим отображения формул.

В общем, задача на автозамену поставлена. Считаю, довольно интересная. Может, кто-нибудь откликнется и поможет. Сам бы хотел поучаствовать, но у меня сейчас нет времени на нее, но позже, если никто не откликнется, попробую что-нибудь сотворить.


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Пятница, 08.07.2022, 18:52
 
Ответить
СообщениеЧто-то совсем какая-то беда... А текстовый формат на колонку I с какой целью установлен? Там же формулы, которые, вроде, пересчитываться должны...

Я попытался зайти в ячейку и включить с помощью клавиши F4 полную абсолютную адресацию всех слагаемых. Т.е. в ячейке I73 сейчас формула вида:
Код
=H73-J73-J218-J183

а должна стать:
Код
=$H$73-$J$73-$J$218-$J$183

По идее, это должно помочь (но на 100 % не уверен - надо проверять). Но меня встретил поголовный текстовый формат... %)
Разумеется, эту операцию по абсолютизации адресов надо будет применить ко всем ячейкам колонки I до сортировки.


[p.s.]Хотя, скорее всего, я НЕ ПРАВ. И всё сложнее - надо все адреса не из текущей строки превращать в формулы с ИНДЕКС/ПОИСKПОЗ, как это обсуждалось в этой теме: http://www.excelworld.ru/forum/2-29353-1

Адреса внутри формулы, ссылающиеся на ячейки текущей строки, после сортировки перестроятся сами. А ссылки на другие строки надо будет превратить в комбинацию функций ИНДЕКС/ПОИСKПОЗ. Для этого также надо будет держать отдельный столбец с зафиксированными первоначальными номерами строк, но, вроде, такой столбец уже имеется - столбец A.[/p.s.]

Т.е. в ячейке I73 сейчас формула вида:
Код
=H73-J73-J218-J183

а должна будет стать:
Код
=H73-J73 - ИНДЕКС(J:J;ПОИСКПОЗ(218;A:A;)) - ИНДЕКС(J:J;ПОИСКПОЗ(183;A:A;))


Чтобы не мучаться с ручной заменой в каждой ячейке колонки I, можно попробовать (где-нибудь "сбоку") сочинить формулу или макрос VBA для выполнения подобной трансформации. Т.е. надо будет к номерам строк, не совпадающим с текущей, "пристраивать" функции ИНДЕКС и ПОИСКПОЗ. В помощь - функция Ф.ТЕКСТ:
Код
=Ф.ТЕКСТ(I73)
а также клавиатурная комбинация Ctrl+~ (контрол+тильда) для переключения ячеек рабочего листа в режим отображения формул.

В общем, задача на автозамену поставлена. Считаю, довольно интересная. Может, кто-нибудь откликнется и поможет. Сам бы хотел поучаствовать, но у меня сейчас нет времени на нее, но позже, если никто не откликнется, попробую что-нибудь сотворить.

Автор - Gustav
Дата добавления - 08.07.2022 в 17:26
Gustav Дата: Пятница, 08.07.2022, 22:54 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2136
Репутация: 840 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Сделал. Написал макрос, который меняет формулы с помощью подхода, обсужденного выше. Прикладываю файл, в котором обработка уже сделана. Попробуйте посортировать в нём строки и посмотреть как ведут себя формулы. Макрос - в файле. Здесь тоже дублирую:
[vba]
Код
Option Explicit

Sub mainLoop()
    'запускать эту процедуру
    Dim cell As Range
    
    'в самой книге делается активным лист обработки (просто встаем на него)
    'а тут задается адрес диапазона применения обработки на этом листе
    For Each cell In Range("I4:I320").Cells
        cell.Activate
        Call oneCellFormulaTransformation
    Next cell
End Sub

Sub oneCellFormulaTransformation()
    Dim strFormula  As String
    Dim lngRow      As Long
    Dim arrAddress  As Variant
    Dim strCellAddr As String
    Dim arrCellAddr As Variant
    Dim i           As Integer
    Dim digits      As Integer
        
    lngRow = ActiveCell.Row
    strFormula = ActiveCell.Formula
    strFormula = _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(strFormula, _
                "=", "|"), _
                "-", "|"), "+", "|"), _
                ";", "|"), ",", "|"), _
                "(", "|"), ")", "|"), _
                "*", "|"), "/", "|"), _
                "<", "|"), ">", "|")
    arrAddress = Split(strFormula, "|")
    strFormula = ActiveCell.Formula
    
    'спешно добавленный обратный цикл по числу знаков номера строки (digits)
    'чтобы корректно обрабатывать встретившуюся формулу типа =МИН(I7;I72)
    'т.е. сначала заменить более длинный I72, а затем более короткий I7
    'неоптимально чудовищно, но работает все равно бодро :)
    'по-хорошему, здесь нужна просто сортировка массива, но где ж ее взять по-быстрому
    For digits = 7 To 1 Step -1
        For i = LBound(arrAddress) To UBound(arrAddress)
            If arrAddress(i) <> "" Then
                strCellAddr = Application.ConvertFormula(arrAddress(i), xlA1, xlA1, xlAbsRowRelColumn)
                arrCellAddr = Split(strCellAddr, "$")
                If UBound(arrCellAddr) = 1 Then
                    If Len(arrCellAddr(1)) = digits Then
                        If CLng(arrCellAddr(1)) <> lngRow Then
                            strFormula = Replace(strFormula, _
                    arrCellAddr(0) & arrCellAddr(1), _
                    "INDEX(" & arrCellAddr(0) & ":" & arrCellAddr(0) & ",MATCH(" & arrCellAddr(1) & ",A:A,))")
                        End If
                    End If
                End If
            End If
        Next i
    Next digits
    ActiveCell.NumberFormat = "General"
    ActiveCell.Formula = strFormula
End Sub
[/vba]
К сообщению приложен файл: marmus.xlsm(460.0 Kb)


Мой tip box - яд 41001663842605
 
Ответить
СообщениеСделал. Написал макрос, который меняет формулы с помощью подхода, обсужденного выше. Прикладываю файл, в котором обработка уже сделана. Попробуйте посортировать в нём строки и посмотреть как ведут себя формулы. Макрос - в файле. Здесь тоже дублирую:
[vba]
Код
Option Explicit

Sub mainLoop()
    'запускать эту процедуру
    Dim cell As Range
    
    'в самой книге делается активным лист обработки (просто встаем на него)
    'а тут задается адрес диапазона применения обработки на этом листе
    For Each cell In Range("I4:I320").Cells
        cell.Activate
        Call oneCellFormulaTransformation
    Next cell
End Sub

Sub oneCellFormulaTransformation()
    Dim strFormula  As String
    Dim lngRow      As Long
    Dim arrAddress  As Variant
    Dim strCellAddr As String
    Dim arrCellAddr As Variant
    Dim i           As Integer
    Dim digits      As Integer
        
    lngRow = ActiveCell.Row
    strFormula = ActiveCell.Formula
    strFormula = _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(Replace( _
        Replace(strFormula, _
                "=", "|"), _
                "-", "|"), "+", "|"), _
                ";", "|"), ",", "|"), _
                "(", "|"), ")", "|"), _
                "*", "|"), "/", "|"), _
                "<", "|"), ">", "|")
    arrAddress = Split(strFormula, "|")
    strFormula = ActiveCell.Formula
    
    'спешно добавленный обратный цикл по числу знаков номера строки (digits)
    'чтобы корректно обрабатывать встретившуюся формулу типа =МИН(I7;I72)
    'т.е. сначала заменить более длинный I72, а затем более короткий I7
    'неоптимально чудовищно, но работает все равно бодро :)
    'по-хорошему, здесь нужна просто сортировка массива, но где ж ее взять по-быстрому
    For digits = 7 To 1 Step -1
        For i = LBound(arrAddress) To UBound(arrAddress)
            If arrAddress(i) <> "" Then
                strCellAddr = Application.ConvertFormula(arrAddress(i), xlA1, xlA1, xlAbsRowRelColumn)
                arrCellAddr = Split(strCellAddr, "$")
                If UBound(arrCellAddr) = 1 Then
                    If Len(arrCellAddr(1)) = digits Then
                        If CLng(arrCellAddr(1)) <> lngRow Then
                            strFormula = Replace(strFormula, _
                    arrCellAddr(0) & arrCellAddr(1), _
                    "INDEX(" & arrCellAddr(0) & ":" & arrCellAddr(0) & ",MATCH(" & arrCellAddr(1) & ",A:A,))")
                        End If
                    End If
                End If
            End If
        Next i
    Next digits
    ActiveCell.NumberFormat = "General"
    ActiveCell.Formula = strFormula
End Sub
[/vba]

Автор - Gustav
Дата добавления - 08.07.2022 в 22:54
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка без потерь формул (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2022 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!