Объединение таблиц на нескольких листах в одну на новом листе.

Автор ekkl, 30 января 2014, 21:53

0 Пользователи и 1 гость просматривают эту тему.

useb64

#15
Цитата: JohnSUN от 14 февраля 2017, 20:14
Цитата: useb64 от 14 февраля 2017, 13:36
у Вас сохранился этот макрос?
В теме не осталось названия файла, пришлось искать по дате создания... А уже когда нашелся - посмотрел на имя файла. Оборжался... "Элементарно, Ватсон!.."

Благодарю Вас
Вы мне очень помогли )

Может быть Вам несложно будет еще на один вопрос ответить.
Описание ситуации:

sheet() - номер текущего листа
sheet("Лист2") - номер Листа2, в "стандартной" нередактированной книге = 2
... и т.п.


есть ли функция "обратная" функции sheet(), которая выдает имя листа по его номеру, например:

SheetName() = наименование текущего листа
в "стандартной" нередактированной книге:
SheetName(1) = Лист1
SheetName(2) = Лист2
...


SheetName() - искомая функция, существует ли такая, или м.б. нужна комбинация каких-то функций?
Функция эта нужна для того, чтобы из любой строки "сводного" (сборного) листа обратится к определенным
ячейкам листа соответствующего номеру строки на "сводном" листе, например


        столбец А                             столбец B                             столбец С
стр 4   =SheetName(ROW()).$A$17    =SheetName(ROW()).$A$20    =SheetName(ROW()).$A$23
стр 5   =SheetName(ROW()).$A$17    =SheetName(ROW()).$A$20    =SheetName(ROW()).$A$23
...

таким образом у нас получится "сводная" таблица.
повторю вопрос: существует ли функция типа SheetName() или аналог, или можно реализовать
данную задачку другим (простым) путем?


С уважением

economist

=LEFT(CELL("address";Лист2!C8);-1+FIND("!";CELL("address";Лист2!C8)))

вернет Лист2
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

economist

Или пользовательской функцией на StarBasic, он же VBA, в LibreOffice Calc это прекрасно работает:


Option VBASupport 1
Option Compatible
'-----------------------------------------------


Function NameSheetByIndex(n as integer) as string
'Возвращает имя листа по его индексу (1,2 итд)

'Раскомментить строку ниже и запустить 1 раз, потом можно строку удалить  
'ThisComponent.BasicLibraries.VBACompatibilityMode=true

NameSheetByIndex=Sheets(n).Name
End function
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

useb64


для того чтобы получить имя листа Лист2

Цитата: economist от 15 февраля 2017, 09:57
вернет Лист2

надо в аргументах функции написать имя листа Лист2?

Цитата: economist от 15 февраля 2017, 09:57
=LEFT(CELL("address";Лист2!C8);-1+FIND("!";CELL("address";Лист2!C8)))

я ведь не знаю имя листа, я знаю только номер...
я видимо что-то не понял...
Извините, я начинающий.
Если не сложно поясните свой ответ )

С уважением

mikekaganski

С уважением,
Михаил Каганский

economist

useb64 - вы спросили есть ли функция "обратная" функции sheet(), которая выдает имя листа по его номеру. Было предложено два варианта - формулой и BASIC-функцией. В формуле нужно просто сослаться щелчком мыши на любую ячейку нужного (т.е. другого) листа. В моём примере это ссылка на Лист2!C8:

=LEFT(CELL("address";Лист2!C8);-1+FIND("!";CELL("address";Лист2!C8)))

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

economist

Изучите функцию =INDIRECT(), она превращает текстовую строку - в ссылку, и возвращает по ссылке значение. Много в Сети примеров по аналогичной функции =ДВССЫЛ() из Excel.

Имена листов - это более понятные сущности, чем номера листов, и проверять их проще.

Если "сводная" таблица это просто все строки со всех листов на отдельном листе, и строки добавляются/удаляются - то формулы рано или поздно "слетят", и лучше собрать строки SQL-запросом:

1) Именуете диапазоны на листах (можно с запасом)  
2) Создаете в OpenOffice|LibreOffice BASE Базу данных - подключение к Таблица (данный ODS), видите в ней список Таблиц (диапазонов)
3) Пишете и сохраняете "консолидирующий" запрос вида

SELECT * FROM Диапазон1
UNION
SELECT * FROM Диапазон2
UNION
... итд
Тут же можно задать сортировку, выборку, фильтрацию и др. группировку итогов, если надо.  

4) Переходите на "сводный" пустой лист и Жмете на F4 или Ctrl+Shift+F4 (зависит от версии OpenOffice|LibreOffice)
5) Перетаскиваете слева сверху имя вашего Запроса на лист/ Всё, самообновляемые (при открытии файла) данные получены. Можно макросом задать интервал обновления или кнопку, или ловить активацию листа и тогда обновлять. Работает это очень быстро.  
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

useb64

извините Ваш вариант

Цитата: economist
...нужно просто сослаться щелчком мыши на любую ячейку нужного (т.е. другого) листа...

не подходит по следующей причине:
Цитата: useb64114 листов... и так каждый день...

Цитата: economist
...Имена листов - это более понятные сущности, чем номера листов...

имена листов неизвестны, а номера листов всегда одни и те же от 1 до 114

Цитата: economist
...лучше собрать строки SQL-запросом...
это выходит за рамки привычного для меня


economist

#23
useb64 - тогда изучите еще "трехмерные" ссылки, обрабатывающие все листы от X и до Y, ну типа:

=SUM(Лист1!A10:Лист114!A10),

- ими тоже можно быстро сделать "свод".

Если у вас OpenOffice и каждый день - новый лист, то на год не хватит. Максимальное количество листов в книге составляет 256.

Не знаю вашу учетную задачу, но уверен что подход день=лист - неправильный. Правильный - это когда одна форма ввода и одна таблица со всеми данными. А вся отчетность - на базе инструмента Calс "Сводная таблица".
Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...

useb64

to economist

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

с уважением

mikekaganski

Создайте у себя макрос в My Macros->Standard (или в Мои макросы->Standard):

Function SheetName(num)
  if (num > 0 AND num <= ThisComponent.Sheets.Count) Then
    SheetName = ThisComponent.Sheets.ElementNames(num-1)
  else
    SheetName = "ERROR!"
  end if
End Function


И пользуйтесь как встроенной функцией Calc. Правда, работать будет только на Вашей машине. Зато нет макроса в файле, нет предупреждений.
Если надо, запишите его не в общую библиотеку макросов, а в документ.
С уважением,
Михаил Каганский