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

Вход

Регистрация

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

 

= Мир MS Excel/Как заложить в формулу ссылку на ячейку из предыдущего листа - Страница 2 - Мир MS Excel

Старая форма входа
  • Страница 2 из 2
  • «
  • 1
  • 2
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как заложить в формулу ссылку на ячейку из предыдущего листа (Формулы/Formulas)
Как заложить в формулу ссылку на ячейку из предыдущего листа
dim34rus Дата: Вторник, 13.12.2016, 01:13 | Сообщение № 21
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 10 ±
Замечаний: 0% ±

Excel 2007 - 2013
Веселиться в VBA, так по полной программе и с формами :-)

в модуль вставляем
[vba]
Код
Sub List_PLUS()
UserForm1.Show
End Sub
[/vba]

Делаем форму с двумя лейблами (во втором лейбле будет новое имя листа), spinbutton и две простые кнопки (Ок и Cancel)

Код для формы

[vba]
Код
Private Sub CommandButton1_Click()
   OldSheet = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name))
   
    ActiveWorkbook.Sheets(OldSheet).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    NewSheet = UserForm1.Label2.Caption
    
    ActiveWorkbook.Sheets.Item(ActiveWorkbook.Sheets.Count).Name = NewSheet
    ActiveWorkbook.Sheets(NewSheet).Activate
    
    i = 3
    
    While ActiveWorkbook.Sheets(NewSheet).Cells(i, 10) <> "Z-ОТЧЕТ"
        If (ActiveWorkbook.Sheets(NewSheet).Cells(i, 2).Value <> "") And (ActiveWorkbook.Sheets(NewSheet).Cells(i, 1).Value <> "") Then
            ActiveWorkbook.Sheets(NewSheet).Cells(i, 3).FormulaR1C1 = "='" & OldSheet & "'!RC[6]"
            ActiveWorkbook.Sheets(NewSheet).Range(Cells(i, 4), Cells(i, 7)).ClearContents
        End If
        i = i + 1
    Wend
   
   UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
   UserForm1.Hide
End Sub

Private Sub SpinButton1_Change()
   If SpinButton1.Value < 0 Then
     SpinButton1.Value = 0
   End If
   
   UserForm1.Label2.Caption = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name) + 1 + SpinButton1.Value)
End Sub

Private Sub UserForm_Activate()
   SpinButton1.Value = 0
   UserForm1.Label2.Caption = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name) + 1)
End Sub
[/vba]

Единственное не понятно что делать с разделом доставка, поэтому в коде он обрабатывается наравне с другими строками.
К сообщению приложен файл: 8183703.xlsm (42.3 Kb)

Извращение - это писать формулы в Word'овских таблицах.
ЯД 410014340958327
 
Ответить
СообщениеВеселиться в VBA, так по полной программе и с формами :-)

в модуль вставляем
[vba]
Код
Sub List_PLUS()
UserForm1.Show
End Sub
[/vba]

Делаем форму с двумя лейблами (во втором лейбле будет новое имя листа), spinbutton и две простые кнопки (Ок и Cancel)

Код для формы

[vba]
Код
Private Sub CommandButton1_Click()
   OldSheet = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name))
   
    ActiveWorkbook.Sheets(OldSheet).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
    NewSheet = UserForm1.Label2.Caption
    
    ActiveWorkbook.Sheets.Item(ActiveWorkbook.Sheets.Count).Name = NewSheet
    ActiveWorkbook.Sheets(NewSheet).Activate
    
    i = 3
    
    While ActiveWorkbook.Sheets(NewSheet).Cells(i, 10) <> "Z-ОТЧЕТ"
        If (ActiveWorkbook.Sheets(NewSheet).Cells(i, 2).Value <> "") And (ActiveWorkbook.Sheets(NewSheet).Cells(i, 1).Value <> "") Then
            ActiveWorkbook.Sheets(NewSheet).Cells(i, 3).FormulaR1C1 = "='" & OldSheet & "'!RC[6]"
            ActiveWorkbook.Sheets(NewSheet).Range(Cells(i, 4), Cells(i, 7)).ClearContents
        End If
        i = i + 1
    Wend
   
   UserForm1.Hide
End Sub

Private Sub CommandButton2_Click()
   UserForm1.Hide
End Sub

Private Sub SpinButton1_Change()
   If SpinButton1.Value < 0 Then
     SpinButton1.Value = 0
   End If
   
   UserForm1.Label2.Caption = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name) + 1 + SpinButton1.Value)
End Sub

Private Sub UserForm_Activate()
   SpinButton1.Value = 0
   UserForm1.Label2.Caption = CStr(CDate(Sheets.Item(ActiveWorkbook.Sheets.Count).Name) + 1)
End Sub
[/vba]

Единственное не понятно что делать с разделом доставка, поэтому в коде он обрабатывается наравне с другими строками.
Автор - dim34rus
Дата добавления - 13.12.2016 в 01:13
ilya-yurasov Дата: Вторник, 13.12.2016, 18:22 | Сообщение № 22
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Использовал решение предложенное Wasilich в Сообщение № 18. Так же благодарю krosav4ig за помощь.
 
Ответить
СообщениеИспользовал решение предложенное Wasilich в Сообщение № 18. Так же благодарю krosav4ig за помощь.
Автор - ilya-yurasov
Дата добавления - 13.12.2016 в 18:22
ilya-yurasov Дата: Вторник, 20.12.2016, 23:43 | Сообщение № 23
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Wasilich, вынужден вернуться к обсуждению, поскольку в ходе работы над файлом предложенное решение в Сообщение № 18 не совсем корректно работает, а именно если добавляем новые товары в таблицу (строки), то все съезжает на одну строку. Ну допустим в листе 19.12.2016 - 100 строк с товарами, им соответствует определенное количество товаров со своими наименованиями, которое считается по предложенной вами формуле с учетом предыдущего листа, в котором то же количество строк, и чтобы понятно было в 88 строке то же название товара как и на 88 строке следующего листа. Но стоит нам добавить в последний лист на 20.12.2016 1 товар (строку), где-нибудь посередине, то получиться уже 101 товар и все, что ниже добавленного съезжает, а считает то по предыдущему листу, а там на 1 строку меньше, получается неверно. Приходиться во всех листах дублировать добавление этой новой строки, а как вы понимаете это нереально. Как быть?
 
Ответить
СообщениеWasilich, вынужден вернуться к обсуждению, поскольку в ходе работы над файлом предложенное решение в Сообщение № 18 не совсем корректно работает, а именно если добавляем новые товары в таблицу (строки), то все съезжает на одну строку. Ну допустим в листе 19.12.2016 - 100 строк с товарами, им соответствует определенное количество товаров со своими наименованиями, которое считается по предложенной вами формуле с учетом предыдущего листа, в котором то же количество строк, и чтобы понятно было в 88 строке то же название товара как и на 88 строке следующего листа. Но стоит нам добавить в последний лист на 20.12.2016 1 товар (строку), где-нибудь посередине, то получиться уже 101 товар и все, что ниже добавленного съезжает, а считает то по предыдущему листу, а там на 1 строку меньше, получается неверно. Приходиться во всех листах дублировать добавление этой новой строки, а как вы понимаете это нереально. Как быть?
Автор - ilya-yurasov
Дата добавления - 20.12.2016 в 23:43
Wasilich Дата: Среда, 21.12.2016, 00:23 | Сообщение № 24
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
по предложенной вами формуле
Не проверял но, по логике, так будет по любой формуле. Если
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
добавить ... (строку), где-нибудь посередине,
Сдвигается формула - сдвигается ссылка.
 
Ответить
Сообщение
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
по предложенной вами формуле
Не проверял но, по логике, так будет по любой формуле. Если
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
добавить ... (строку), где-нибудь посередине,
Сдвигается формула - сдвигается ссылка.
Автор - Wasilich
Дата добавления - 21.12.2016 в 00:23
dim34rus Дата: Среда, 21.12.2016, 01:09 | Сообщение № 25
Группа: Пользователи
Ранг: Участник
Сообщений: 66
Репутация: 10 ±
Замечаний: 0% ±

Excel 2007 - 2013
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
если добавляем новые товары в таблицу (строки), то все съезжает на одну строку
, так это же логично. В чем прелесть формулы заключается. Ссылается она на соседнюю строку на предыдущем листе, НО, что самое главное на ТЕЖЕ самые данные. Всего то надо: 1. восстановить формулы с суммами, а формулу, которая берет данные с предыдущего листа необходимо скопировать/восстановить только на СЛЕДУЮЩЕМ листе.
И не надо синхронизировать номенклатуру разных листов.

Извращение - это писать формулы в Word'овских таблицах.
ЯД 410014340958327
 
Ответить
Сообщение
Цитата ilya-yurasov, 20.12.2016 в 23:43, в сообщении № 24 ()
если добавляем новые товары в таблицу (строки), то все съезжает на одну строку
, так это же логично. В чем прелесть формулы заключается. Ссылается она на соседнюю строку на предыдущем листе, НО, что самое главное на ТЕЖЕ самые данные. Всего то надо: 1. восстановить формулы с суммами, а формулу, которая берет данные с предыдущего листа необходимо скопировать/восстановить только на СЛЕДУЮЩЕМ листе.
И не надо синхронизировать номенклатуру разных листов.
Автор - dim34rus
Дата добавления - 21.12.2016 в 01:09
ilya-yurasov Дата: Среда, 21.12.2016, 18:08 | Сообщение № 26
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
dim34rus, логично, но из-за этого предложенное решение не подходит.

Не понял что значит
Цитата dim34rus, 21.12.2016 в 01:09, в сообщении № 26 ()
Всего то надо: 1. восстановить формулы с суммами


И это:
Цитата dim34rus, 21.12.2016 в 01:09, в сообщении № 26 ()
а формулу, которая берет данные с предыдущего листа необходимо скопировать/восстановить только на СЛЕДУЮЩЕМ листе.
тоже непонятно. Это каждый раз при создании листов нужно что-то менять в формулах, восстанавливать формул на текущем и предыдущем листе. Есть решение которое позволит при добавлении строки на последнем листе, автоматически добавлять эту же строку на всех предыдущих листах, чтобы на 1-м и на 100-м листе, допустим, в итоге было одинаковое количество строк?
 
Ответить
Сообщениеdim34rus, логично, но из-за этого предложенное решение не подходит.

Не понял что значит
Цитата dim34rus, 21.12.2016 в 01:09, в сообщении № 26 ()
Всего то надо: 1. восстановить формулы с суммами


И это:
Цитата dim34rus, 21.12.2016 в 01:09, в сообщении № 26 ()
а формулу, которая берет данные с предыдущего листа необходимо скопировать/восстановить только на СЛЕДУЮЩЕМ листе.
тоже непонятно. Это каждый раз при создании листов нужно что-то менять в формулах, восстанавливать формул на текущем и предыдущем листе. Есть решение которое позволит при добавлении строки на последнем листе, автоматически добавлять эту же строку на всех предыдущих листах, чтобы на 1-м и на 100-м листе, допустим, в итоге было одинаковое количество строк?
Автор - ilya-yurasov
Дата добавления - 21.12.2016 в 18:08
ilya-yurasov Дата: Среда, 21.12.2016, 18:09 | Сообщение № 27
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Wasilich, не понял, так что делать то?
 
Ответить
СообщениеWasilich, не понял, так что делать то?
Автор - ilya-yurasov
Дата добавления - 21.12.2016 в 18:09
Wasilich Дата: Четверг, 22.12.2016, 22:38 | Сообщение № 28
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
Цитата ilya-yurasov, 21.12.2016 в 18:09, в сообщении № 28 ()
Wasilich, не понял, так что делать то?

Не добавлять строки
Цитата ilya-yurasov, 21.12.2016 в 18:09, в сообщении № 28 ()
где-нибудь посередине
а добавлять в конец списка, сделав энное количество запасных строк. Которые, что бы не мешали, можно просто скрыть.


Сообщение отредактировал Wasilich - Четверг, 22.12.2016, 23:22
 
Ответить
Сообщение
Цитата ilya-yurasov, 21.12.2016 в 18:09, в сообщении № 28 ()
Wasilich, не понял, так что делать то?

Не добавлять строки
Цитата ilya-yurasov, 21.12.2016 в 18:09, в сообщении № 28 ()
где-нибудь посередине
а добавлять в конец списка, сделав энное количество запасных строк. Которые, что бы не мешали, можно просто скрыть.
Автор - Wasilich
Дата добавления - 22.12.2016 в 22:38
Wasilich Дата: Суббота, 24.12.2016, 10:11 | Сообщение № 29
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
О как, сразу и не заметил
Цитата ilya-yurasov, 21.12.2016 в 18:08, в сообщении № 27 ()
чтобы на 1-м и на 100-м листе, допустим, в итоге было одинаковое количество строк?
Это как? Вы собираетесь нагромождать книгу листами до бесконечности? Да еще и хотите вставку строк на всех листах делать? А в году примерно 245 р.д. :'( %) Я думал, максимум на месяц - 20 - 22 листов.
Не верным путем идёте товариСЧ! :D Заблудитесь в листах. Надо поменять подход к решению задачи. Ибо, в вашем случае со ссылкой на предыдущий лист, будут ещё проблемы.
А подход простой, убрать ссылки на пред. лист оставив значения, потом просто сделать копию текущего листа, сменить имя текущего листа (не копии) на новую дату, значения из графы "на конец смены" скопировать в графу "на начало смены". И будет Вам счастье. Потом добавляйте строки куда хотите. И эти действия можно легко автоматизировать. :)
Только этот метод желательно применить в новой книге, скопировав таблицу из последнего рабочего листа старой книги в новую книгу на Лист1.


Сообщение отредактировал Wasilich - Суббота, 24.12.2016, 11:02
 
Ответить
СообщениеО как, сразу и не заметил
Цитата ilya-yurasov, 21.12.2016 в 18:08, в сообщении № 27 ()
чтобы на 1-м и на 100-м листе, допустим, в итоге было одинаковое количество строк?
Это как? Вы собираетесь нагромождать книгу листами до бесконечности? Да еще и хотите вставку строк на всех листах делать? А в году примерно 245 р.д. :'( %) Я думал, максимум на месяц - 20 - 22 листов.
Не верным путем идёте товариСЧ! :D Заблудитесь в листах. Надо поменять подход к решению задачи. Ибо, в вашем случае со ссылкой на предыдущий лист, будут ещё проблемы.
А подход простой, убрать ссылки на пред. лист оставив значения, потом просто сделать копию текущего листа, сменить имя текущего листа (не копии) на новую дату, значения из графы "на конец смены" скопировать в графу "на начало смены". И будет Вам счастье. Потом добавляйте строки куда хотите. И эти действия можно легко автоматизировать. :)
Только этот метод желательно применить в новой книге, скопировав таблицу из последнего рабочего листа старой книги в новую книгу на Лист1.
Автор - Wasilich
Дата добавления - 24.12.2016 в 10:11
bmv98rus Дата: Суббота, 24.12.2016, 11:22 | Сообщение № 30
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
ilya-yurasov,
Буквально на днях решал подобную задачу. Без дополнительного листа, с учетом поставленных условий, не обойтись и все строится на корректном наименовании листов.
Пример в приложении. Первая дата, из начального листа, далее столбцы A,B размножить по количеству дней.
Принцып прост. на странице settings сперва определяем какие страницы есть в наличии, в данном случае исходя из даты.
Дальше
Код
=ДВССЫЛ(АДРЕС(2;9;;1;ТЕКСТ(МАКС(СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))


АДРЕС(2;9 - строка 2 столбец 9 (i2)
Ну и подставляетя ближайшее предыдущее имя листа.
Ищется просто в массиве из предыдущих значений уже готовых в столбце B
Код
СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1)

единственное что надо ограничить область строкой, за которой следует наименование текущего листа
Код
ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""))
;
где
Код
ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1)
- имя текущего листа, а в дату переводим, для упрощения поиска.
К сообщению приложен файл: Copy_of_9894033.xlsx (22.2 Kb)


Сообщение отредактировал bmv98rus - Воскресенье, 25.12.2016, 10:27
 
Ответить
Сообщениеilya-yurasov,
Буквально на днях решал подобную задачу. Без дополнительного листа, с учетом поставленных условий, не обойтись и все строится на корректном наименовании листов.
Пример в приложении. Первая дата, из начального листа, далее столбцы A,B размножить по количеству дней.
Принцып прост. на странице settings сперва определяем какие страницы есть в наличии, в данном случае исходя из даты.
Дальше
Код
=ДВССЫЛ(АДРЕС(2;9;;1;ТЕКСТ(МАКС(СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))


АДРЕС(2;9 - строка 2 столбец 9 (i2)
Ну и подставляетя ближайшее предыдущее имя листа.
Ищется просто в массиве из предыдущих значений уже готовых в столбце B
Код
СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1)

единственное что надо ограничить область строкой, за которой следует наименование текущего листа
Код
ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""))
;
где
Код
ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1)
- имя текущего листа, а в дату переводим, для упрощения поиска.
Автор - bmv98rus
Дата добавления - 24.12.2016 в 11:22
ilya-yurasov Дата: Воскресенье, 25.12.2016, 23:19 | Сообщение № 31
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
bmv98rus, смотрю на ваш файл и не могу понять как вы это сделали? В своей книги:
1. Создал лист settings как у вас
2. На последнем листе вижу у вас формулу в ячейке С2, копирую вставляю ее туда же на свой лист
Код
=ДВССЫЛ(АДРЕС(2;9;;1;ТЕКСТ(МАКС(СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))
, в ответ на что всплывающее окно "невозможно ввести указанную формулу поскольку она содержит больше уровней вложения, чем допускается текущим форматом файла".
3. Далее вы приводите еще три формулы
Цитата bmv98rus, 24.12.2016 в 11:22, в сообщении № 31 ()
АДРЕС(2;9 - строка 2 столбец 9 (i2)
Ну и подставляетя ближайшее предыдущее имя листа.
Ищется просто в массиве из предыдущих значений уже готовых в столбце B
[Перевод / Translate]
СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1)

единственное что надо ограничить область строкой, за которой следует наименование текущего листа
[Перевод / Translate]
ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""))
;
где
[Перевод / Translate]
ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1)
- имя текущего листа, а в дату переводим, для упрощения поиска.

Тут я вообще не понял куда их вставлять нужно?
4. Так же пробовал в вашем файле скопировать формулу на 3-ю строку где будет следующий товар, как вы понимаете товаров там будет очень много, так во копирую, данные не меняются для третьей строки, а просто копируется то же самое со второй строки, те же цифры получаются как и на строке от куда копирую формулу.
 
Ответить
Сообщениеbmv98rus, смотрю на ваш файл и не могу понять как вы это сделали? В своей книги:
1. Создал лист settings как у вас
2. На последнем листе вижу у вас формулу в ячейке С2, копирую вставляю ее туда же на свой лист
Код
=ДВССЫЛ(АДРЕС(2;9;;1;ТЕКСТ(МАКС(СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))
, в ответ на что всплывающее окно "невозможно ввести указанную формулу поскольку она содержит больше уровней вложения, чем допускается текущим форматом файла".
3. Далее вы приводите еще три формулы
Цитата bmv98rus, 24.12.2016 в 11:22, в сообщении № 31 ()
АДРЕС(2;9 - строка 2 столбец 9 (i2)
Ну и подставляетя ближайшее предыдущее имя листа.
Ищется просто в массиве из предыдущих значений уже готовых в столбце B
[Перевод / Translate]
СМЕЩ(Settings!$B$1;1;0;ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""));Settings!$B:$B;0)-2;1)

единственное что надо ограничить область строкой, за которой следует наименование текущего листа
[Перевод / Translate]
ПОИСКПОЗ(ДАТАЗНАЧ(ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1));""))
;
где
[Перевод / Translate]
ЗАМЕНИТЬ(ЯЧЕЙКА("filename";A1);1;НАЙТИ("]";ЯЧЕЙКА("filename";A1)
- имя текущего листа, а в дату переводим, для упрощения поиска.

Тут я вообще не понял куда их вставлять нужно?
4. Так же пробовал в вашем файле скопировать формулу на 3-ю строку где будет следующий товар, как вы понимаете товаров там будет очень много, так во копирую, данные не меняются для третьей строки, а просто копируется то же самое со второй строки, те же цифры получаются как и на строке от куда копирую формулу.
Автор - ilya-yurasov
Дата добавления - 25.12.2016 в 23:19
bmv98rus Дата: Понедельник, 26.12.2016, 08:20 | Сообщение № 32
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
ilya-yurasov,
"невозможно ввести указанную формулу поскольку она содержит больше уровней вложения, чем допускается текущим форматом файла". - проверю в 2010 отпишу. Странно, вроде ничего такого не намудрил с глубиной. Проверил, видимо глюк при вставке формулы с портала. Если c Excel в Excel переносить, то все корректно.

по поводу копирования , тут еще проще . Весь набор формул был приведен только для описания решения. Там же была сразу сноска на АДРЕС(2;9 - строка 2 столбец 9 (i2). Это жестко на ячейку i2 завязано. Если нужно по другим ячейкам , то
Код
=INDIRECT(ADDRESS(ROW(I2);COLUMN(I2);;1;TEXT(MAX(OFFSET(Settings!$B$1;1;0;MATCH(DATEVALUE(REPLACE(CELL("filename";A1);1;FIND("]";CELL("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))


в данном случае универсально ссылаемся на на любую ячейку относительно I2 , для примера на лист "10.12.2016"
Код
ADDRESS(ROW(I2);COLUMN(I2);;1;"10.12.2016")

при желании закрепите I - $I2'

добавил в файл.
Но если товаров много, то желательно минимизировать расчеты. например имя прдыдущего листа достаточно расчитать один раз и положив его в одну из ячеек (скрытых, например в первой строке столбца следующего за последним (M1)
Код
=TEXT(MAX(OFFSET(Settings!$B$1;1;0;MATCH(DATEVALUE(REPLACE(CELL("filename";A1);1;FIND("]";CELL("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)


а уже непосредственно в c2
Код
=INDIRECT(ADDRESS(ROW(I2);COLUMN(I2);;1;$M$1))


в С3 оставлен вариант длинный.
К сообщению приложен файл: 7216812.xlsx (22.3 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 26.12.2016, 16:58
 
Ответить
Сообщениеilya-yurasov,
"невозможно ввести указанную формулу поскольку она содержит больше уровней вложения, чем допускается текущим форматом файла". - проверю в 2010 отпишу. Странно, вроде ничего такого не намудрил с глубиной. Проверил, видимо глюк при вставке формулы с портала. Если c Excel в Excel переносить, то все корректно.

по поводу копирования , тут еще проще . Весь набор формул был приведен только для описания решения. Там же была сразу сноска на АДРЕС(2;9 - строка 2 столбец 9 (i2). Это жестко на ячейку i2 завязано. Если нужно по другим ячейкам , то
Код
=INDIRECT(ADDRESS(ROW(I2);COLUMN(I2);;1;TEXT(MAX(OFFSET(Settings!$B$1;1;0;MATCH(DATEVALUE(REPLACE(CELL("filename";A1);1;FIND("]";CELL("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)))


в данном случае универсально ссылаемся на на любую ячейку относительно I2 , для примера на лист "10.12.2016"
Код
ADDRESS(ROW(I2);COLUMN(I2);;1;"10.12.2016")

при желании закрепите I - $I2'

добавил в файл.
Но если товаров много, то желательно минимизировать расчеты. например имя прдыдущего листа достаточно расчитать один раз и положив его в одну из ячеек (скрытых, например в первой строке столбца следующего за последним (M1)
Код
=TEXT(MAX(OFFSET(Settings!$B$1;1;0;MATCH(DATEVALUE(REPLACE(CELL("filename";A1);1;FIND("]";CELL("filename";A1));""));Settings!$B:$B;0)-2;1));Settings!$E$2)


а уже непосредственно в c2
Код
=INDIRECT(ADDRESS(ROW(I2);COLUMN(I2);;1;$M$1))


в С3 оставлен вариант длинный.
Автор - bmv98rus
Дата добавления - 26.12.2016 в 08:20
Wasilich Дата: Понедельник, 26.12.2016, 20:49 | Сообщение № 33
Группа: Друзья
Ранг: Старожил
Сообщений: 1232
Репутация: 326 ±
Замечаний: 0% ±

2003
Да уж! Придется Вам ilya-yurasov, еще раз внимательно почитать мое сообщение № 30, и не морочить себе голову. :D
 
Ответить
СообщениеДа уж! Придется Вам ilya-yurasov, еще раз внимательно почитать мое сообщение № 30, и не морочить себе голову. :D
Автор - Wasilich
Дата добавления - 26.12.2016 в 20:49
bmv98rus Дата: Понедельник, 26.12.2016, 21:22 | Сообщение № 34
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4147
Репутация: 772 ±
Замечаний: 0% ±

Excel 2013/2016
Wasilich,
Я б наверно тоже порекомендовал сменить подход, но часто пользователю удобно именно так и не иначе, "солнце встает на востоке ....". А мой вариант не так и сложен, если приглядется. А с очень незначительной доработкой по замене "]" на соответствующий разделить, заработает и в Libre в Open, хотя ресурс всеж про любимый Excel. Меня тоже порой угнетает увлечение такими решениями Сумма прописью без макросов или Сумма прописью без макросов 2, но бывает это более рационально. Например в компании где использование VBA, под запретом по безопасности.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 26.12.2016, 21:56
 
Ответить
СообщениеWasilich,
Я б наверно тоже порекомендовал сменить подход, но часто пользователю удобно именно так и не иначе, "солнце встает на востоке ....". А мой вариант не так и сложен, если приглядется. А с очень незначительной доработкой по замене "]" на соответствующий разделить, заработает и в Libre в Open, хотя ресурс всеж про любимый Excel. Меня тоже порой угнетает увлечение такими решениями Сумма прописью без макросов или Сумма прописью без макросов 2, но бывает это более рационально. Например в компании где использование VBA, под запретом по безопасности.
Автор - bmv98rus
Дата добавления - 26.12.2016 в 21:22
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как заложить в формулу ссылку на ячейку из предыдущего листа (Формулы/Formulas)
  • Страница 2 из 2
  • «
  • 1
  • 2
Поиск:

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