Архив форума buh-info.ee

Уважаемые коллеги, подскажите, как в EXCEL создать перекрестные ссылки с файла на файл и как в один файл добавить ссылку на определенные ячейки, содержащиеся в другом EXCEL файле? Заранее спасибо. :roll:

Конкретнее пожалуйста обьясните, что вы подразумеваете под: - "Перекрёстная ссылка с файла на файл" ? - Ссылки на определённую ячейку в другом файле, это по вашему как должно произойти?

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

В таком случае у вас не ссылка на файл получается, а [B]содержимое[/B] ячеек в этом файле. Ссылка - это просто указатель для перехода с одного файла на другой, типа как в Веб-страничке. Или я что-то не так понял ? В случае простой ссылки вы просто гиперссылку (Hyperlink) создаёте, Там есть ещё кнопочка Закладка (Bookmark)- можете указать на какую ячейку хотите "перепрыгнуть" по данной ссылке.

Это сообщение было отредактировано: Bystander, 21 Мая, 2011 г. - 14:53

а возможно ли как-то в Excel задать округление только до 0,05 и 0,10 (т.е. чтобы в сумме было либо 5 либо 0 сентов)?

[quote][I]2006-11-07 14:12 Perla писал(а):[/I] округление только до 0,05 и 0,10 (т.е. чтобы в сумме было либо 5 либо 0 сентов)?[/quote] Функциями: MROUND(number,multiple) - работает при наличии Analysis Toolpack - выход из положения, который предлагает статистика (Analysis Toolpack Add-In - для статистических расчётов). CEILING(number,significance) - округляет вверх, от нуля. FLOOR(number,significance) - округляет вниз, к нулю. Есть ещё ROUNDUP,ROUNDDOWN,EVEN,ODD,ROUND, но они не подходят потому что либо до какого-то знака после запятой округляют либо совсем не так как в данном случае надо. Пример: =MROUND(B5;0,05) - округлять ячейку до множителя 0,05 (тоесть 1,00 .. 1,05 .. 1,10 .. 1,15 ... и т.д.)

Это сообщение было отредактировано: Bystander, 08 Ноя, 2006 г. - 13:08

[quote][I]2006-11-07 13:47 Bystander писал(а):[/I] To Bystander--ОГРОМНОЕ спасибо Рано поблагодарила :cry-cry: , не открываются файлы. Никак не пойму

Это сообщение было отредактировано: s8eta, 07 Ноя, 2006 г. - 14:33

Проверил все 3 варианта - MROUND,FLOOR,CEILING: [CODE] =MROUND(chislo;0,05) =FLOOR(chislo;0,05) =CEILING(chislo;0,05) [/CODE] Число __ MROUND __ FLOOR __ CEILING 123,20 __ 123,20 __ 123,20 __ 123,20 123,21 __ 123,20 __ 123,20 __ 123,25 123,22 __ 123,20 __ 123,20 __ 123,25 123,23 __ 123,25 __ 123,20 __ 123,25 123,24 __ 123,25 __ 123,20 __ 123,25 123,25 __ 123,25 __ 123,25 __ 123,25 123,26 __ 123,25 __ 123,25 __ 123,30 123,27 __ 123,25 __ 123,25 __ 123,30 123,28 __ 123,30 __ 123,25 __ 123,30 123,29 __ 123,30 __ 123,25 __ 123,30 123,30 __ 123,30 __ 123,30 __ 123,30 Вобщем, округляйте, как вам нужно. ;-)

Это сообщение было отредактировано: Bystander, 08 Ноя, 2006 г. - 15:41

Bystander, спасибо большое! s8eta, вот это может то, что нужно: http://firststeps.ru/msoffice/r.php?35

[quote][I]2006-11-07 17:26 Perla писал(а):[/I] Bystander, спасибо большое! s8eta, вот это может то, что нужно: http://firststeps.ru/msoffice/r.php?35[/quote] Perla, спасибо, нет, там тоже нету.

[quote][I]2006-11-08 07:56 s8eta писал(а):[/I] Perla, спасибо, нет, там тоже нету.[/quote] А в чём, собственно, дело? Нажали "=" в одном файле, указали ячейку в другом - всё как всегда.

[quote][I]2006-11-08 09:47 McSim писал(а):[/I] А в чём, собственно, дело? Нажали "=" в одном файле, указали ячейку в другом - всё как всегда.[/quote] Дело в том что человек некорректно выразил свои мысли. Речь шла изначально о ссылках с файла на файл и обратно (что подразумевает собой простое "перепрыгивание" отсюда-туда-и-обратно, с файла на файл). Если человеку хотелось бы получить результаты данных из одного диапазона ячеек в первом файле в некотором диапазоне ячеек во втором файле, "ссылкой" это никак не назовёшь... Скорее аггрегированием, накоплением или сводными данными.

Это сообщение было отредактировано: Bystander, 08 Ноя, 2006 г. - 13:10

Предположил ситуацию когда есть 2 (n1.xls и n2.xls) или более файлов(n3.xls,.. n4.xls, .. n5.xls, ...) с числовыми данными и нужно в некотором отдельном (glavn.xls) сделать обобщение данных. В данном случае как ни странно действительно появляются "Ссылки" ("Links"). Хотя правильней было бы назвать их "связи" imho. Например хотим сложить числа из файла n1.xls и n2.xls. Для этого в файле glavn.xls, в ячейке куда хотят поставить данные из другого файла пишут: [CODE]=[n1.xls]Otdel1!B4+[n2.xls]Tsehh2!C8[/CODE] Подразумевается что данные были взяты с листов под названием "Otdel1" файла n1.xls и "Tsehh2" файла n2.xls. В результате получили данные из 2-х вспомогательных файлов в третьем - основном. Теперь мы можем оперировать эти данными если бы они находились у нас непосредственно в ячейках. Но! Здесь ещё есть много всяких минусов. Данные в ячейках файлов n1.xls и n2.xls должны быть на фиксированных местах. Иначе в файле glavn.xls придётся всё переделывать. Если количество и тип данных в n1.xls,n2.xls разное, то придётся всё заносить заново с различными условиями (прощай автоматизация). Конечно, тут всё зависит от данных, от постановки задачи. Ещё вариант - сделать всё на VBA и не мучаться.

Уважаемии Bystander ест вопрос по excel, Ест ли возможност в болшои таблице созданнои в excel с данними в еек, менят сразу все значения в Евро? даже с формулами и так далее? Знаю ест currency convertor ot microsoft add in и его можно исползоват, скацал но никак не получается все сразу поменят!не могу разобратся! И приходится почти все по отделности делит на 15,646600

[quote][I]2007-07-11 10:06 vadim325 писал(а):[/I] Уважаемии Bystander ест вопрос по excel, Ест ли возможност в болшои таблице созданнои в excel с данними в еек, менят сразу все значения в Евро? даже с формулами и так далее? Знаю ест currency convertor ot microsoft add in и его можно исползоват, скацал но никак не получается все сразу поменят!не могу разобратся! И приходится почти все по отделности делит на 15,646600 [/quote] Могу предложить Вам такой вариан: Предположим, что Ваши значения в EUR на другом листе, тогда EUR-листе в любой ячейке на видном месте ставите курс EUR 15,6466 Eur лист, должен быть точной копией кронового листа и каждая ячейка должна брать результат кроновой аналогичной ячецки с добавлениме формули *$A$1, где A1=15,6466 $- это значение постоянности. Тогда формулу можно просто копировать без внесения поправок на ячейки. Это, конечно очень емкая работа, но 1. В EUR расчетах происходит автоматическое изменение после внесения поправок на EEK 2. изменив EUR на другую валюту в A1 вы автоматически получите перерасчет на указанную валюту. P.S Когда кроновый отчет готов окончателно, чтобы EUR ,надо просто протащить Sheet1 на Sheet1(2) Вот как могла объяснила. Будет ли Вам понятно. :-? могу и наглядный пример переслать Посмотрела Euro Conversion - так там еков нет?! ... говорят старый ECXEL

Это сообщение было отредактировано: clover, 11 Июль, 2007 г. - 13:45

То clover Даваите нагляднии пример плз! как раз работа с 2 страницами и идет! :-Д

Это сообщение было отредактировано: vadim325, 11 Июль, 2007 г. - 11:37

[quote][I]2007-07-11 13:22 vadim325 писал(а):[/I] То clover Даваите нагляднии пример плз! как раз работа с 2 страницами и идет! :-Д Это сообщение было отредактировано: vadim325, 11 Июль, 2007 г. - 11:37[/quote] Я Вам в личку написала :)

[quote][I]2007-07-11 13:47 clover писал(а):[/I] Я Вам в личку написала :)[/quote] А с другими поделиться?? Вобщем ситуация довольно-таки проста. Тут нужно помнить как интерпретируются адреса ячеек в Экселе. Первая часть адреса вида A10 означает столбец (A) , вторая - строку (10). Столбцы могут быть от A до Z и от AA до ZZ. Строки нумеруются цифрами от 1 до 32700 (с чемто). К сожалению я не помню максимальный номер строки ... Итак, адрес сам по себе является именем данным ячейке самим Экселем. Если захотите задать некоторое имя ячейке или некоторой совокупности ячеек - помните что имя типа A10 уже существует изначально. Про абсолютные и относительные адреса. По умолчанию все адреса в Экселе относительные. Для того чтобы сделать адрес полностью или частично абсолютным, нужно добавить значок доллара $ перед строкой или/и столбцом. Например $A10 или A$10 или $A$10. Для чего это нужно?? Нельзя ли обойтись без абсолютных-относительных адресов?? Хмм ... зависит от обстоятельств. Приведу пример. В ячейку G8 записали необлагаемый налогом минимум. В ячейку G10 записали ставку подоходного налога. Имеется таблица(начиная с адреса A10) со столбцами: Nimi, Palk, Tax. В таблице куча людей с начисленной з/п НАМНОГО БОЛЬШЕ МИНИМУМА в столбце Palk. Нужно записать значения в столбец Tax. Грубо формула для вычислений такова: =(B11-G8)/G10 Получим собранный налог. Но это только для одной строчки. Для того чтобы нам не нужно было переписывать на каждую последующую строку формулу заново, воспользуемся "копированием растягиванием". Тоесть наведём курсор на ячейке так чтоб он превратился в чёрный крест, схватим и потянем вниз. в последующих строчках появятся копии формулы. Но при этом содержание их изменится, будет: =(B12-G9)/G11 И т.д. Тут действует простое правило - при растягивании ВНИЗ или ВВЕРХ меняются строки в адресах. При растягивании ВПРАВО или ВЛЕВО - меняются столбцы. При этом помнить надо что они все меняются одновременно, и к примеру значение на 100-й строчке таблицы уже не будет ссылаться на число в ячейке по адресу G8 и G10. Для того чтобы оно туда и только туда ссылалось, нужно частично или полностью "закрепить" адрес от изменения. Примерно так: =(B11-$G$8)/$G$10 Тогда следующая строчка будет: =(B12-$G$8)/$G$10. Стоит заметить, что B11, B12, и т.д. не "закреплено". Это нужно потому что в каждой следующей строчке стоят свои данные, нужные только в той же самой строчке. При перетягивании как раз и нужно чтоб этот адрес менялся. Это ещё не всё. Есть ещё и комбинированное применение абсолютных адресов, где результат вычисления зависит не только от строки, но и от столбца. Так что стоит немного подумать при составлении формулы вычисления в ячейке.

Это сообщение было отредактировано: Bystander, 11 Июль, 2007 г. - 16:09

Очень нужны шаблоны документов в EXEL, особенно главной книги. Также было бы неплохо оборотку, журнал операцийи карточки по счетам. Буду оооочень благодарна. :-*

[quote][I]2007-07-11 16:11 viktoria123 писал(а):[/I] Очень нужны шаблоны документов в EXEL, особенно главной книги. Также было бы неплохо оборотку, журнал операцийи карточки по счетам. Буду оооочень благодарна. :-* [/quote] Это не по данной теме. Ищите в других разделах форума.

У нас в файлах есть [URL=http://www.buh-info.ee/portal/modules.php?op=modload&name=Downloads&file=index&req=viewsdownload&sid=8&min=10&orderby=titleA&show=10] Табель рабочего времени[/URL]. Помимо прочих достоинств, он хорош ещё и тем, что сам знает, на какие дни выпадают государственные праздники. Одно "но" - создан табель году так в 2003, а тогда 20 августа и 23 декабря гос.праздниками не были. Подскажите, что и куда мне надо добавить в этом файле, чтобы он "увидел" эти два праздника? :nuuu:

В ячейках W1:Y12 находятся эти праздники. Но думаю что недостаток таблицы как раз в том, что там по всей видимости рассчитано так, что более 2-х праздников в месяц не бывает (т.к. колонка W1:W12 - это месяцы, а X1:Y12 - дни праздников). Если сами сможете подправить - флаг вам в руки, будем рады исправленной версии. Но подправлять нужно будет в нескольких (я так предполагаю) местах. По моему же субъективному мнению нужно было бы сделать спрятанный дополнительный рабочий лист с нужными вспомогательными данными, а оформлять и печатать всё в основном листе. Стоит отметить, что всё сделано тщательно, с использованием скрытых ячеек и формата по условию (такая штука, которая делает красным цветом в зависимости от месяца и года). К сожалению я бы такой файл не сделал бы, т.к. не знаю специфику того, что он должен вычислять (и как вычислять).

[quote][I]2006-11-08 15:40 Bystander писал(а):[/I] Предположил ситуацию когда есть 2 (n1.xls и n2.xls) или более файлов(n3.xls,.. n4.xls, .. n5.xls, ...) с числовыми данными и нужно в некотором отдельном (glavn.xls) сделать обобщение данных. В данном случае как ни странно действительно появляются "Ссылки" ("Links"). Хотя правильней было бы назвать их "связи" imho. Например хотим сложить числа из файла n1.xls и n2.xls. Для этого в файле glavn.xls, в ячейке куда хотят поставить данные из другого файла пишут: [CODE]=[n1.xls]Otdel1!B4+[n2.xls]Tsehh2!C8[/CODE] Подразумевается что данные были взяты с листов под названием "Otdel1" файла n1.xls и "Tsehh2" файла n2.xls. В результате получили данные из 2-х вспомогательных файлов в третьем - основном. Ещё вариант - сделать всё на VBA и не мучаться.[/quote] Подниму старую тему, так как сейчас она для меня актуальна. Пробовала воспользоваться советом Bystander, но не получается. Формула не хочет работать. А как сделать всё на VBA и не мучиться - не знаю :-) . Bystander, взываю к Вам - можете помочь?

вот так должно работать [n1.xls]Otdel1!$B$4+[n2.xls]Tsehh2!$C$8 но вообще в excel c перекрестными ссылками раньше беда была - часто рушился, так называемый "мастер-файл", т.е. основной сборщик агрегированной инфы

[quote][I]2011-05-01 16:09 Victor_Fin писал(а):[/I] вот так должно работать [n1.xls]Otdel1!$B$4+[n2.xls]Tsehh2!$C$8 но вообще в excel c перекрестными ссылками раньше беда была - часто рушился, так называемый "мастер-файл", т.е. основной сборщик агрегированной инфы [/quote] Извините, Виктор, не успела написать, что уже сама разобралась, теперь все работает как надо, так что вопрос снимаю. Единственное, что xотелось бы все-таки узнать, Это как "сделать всё на VBA и не мучиться"? :-)

[quote][I]2011-05-01 19:57 vista писал(а):[/I] А что Вы хотите узнать? Что такое VBA, или какой макрос написать, или чтоб уже взять готовый файл (файлы)? [/quote] интересует, каким образом конкретно это можно сделать с помощью Visual Basic for Applications? Если это означает, что надо написать макрос, то как его написать?

Тема довольно таки давняя, пожалуйста уточните что бы вы хотели на VBA сделать. Если то же что и раньше - агрегировать данные из одной(, второй, третьей ...) рабочей книги в главную это на VBA выглядит просто. [CODE] Public Sub LiidaLahtrid() Const excelFileFilter = "*.xls; *.xlsx; *.xlsm" Dim wrbA As Workbook, wrbB As Workbook, wrbC As Workbook, fd As FileDialog Dim numA, numB, numC, rngC As Range ' iz rabotsih knig A i B aggregiruem v knigu C Dim pnA As String, pnB As String, pnC As String Set wrbC = Application.ActiveWorkbook Set rngC = wrbC.Sheets("Sheet1").Range("resultat") ' Uznajem imena failov knig A i B Set fd = Application.FileDialog(msoFileDialogFilePicker) With fd .Title = "Gde nahoditsa kniga A ?" .AllowMultiSelect = False .Filters.Clear .Filters.Add "Excel files", excelFileFilter If .Show = -1 Then pnA = .SelectedItems.Item(1) Else Exit Sub .Title = "Gde nahodisa kniga B?" If .Show = -1 Then pnB = .SelectedItems.Item(1) Else Exit Sub End With Set fd = Nothing [/CODE]Это была часть, где просто напросто спрашиваются у пользователя где находятся рабочие книга A и B. Если точное местоположение известно (в виде Д:\Папка\ВтораяПапка\ЕщёОднаПапка\СамФайл.xlsx)- можно пропустить. Дальше просто всё - открыть рабочую книги, считать данные из них, произвести необходимые операции с данными и записать в нужную книгу - в данном случае книгу C.[CODE] Set wrbA = Application.Workbooks.Open(pnA) Set wrbB = Application.Workbooks.Open(pnB) numA = CDbl(wrbA.Sheets("Sheet1").Range("B2")) numB = CDbl(wrbB.Sheets("Sheet1").Range("B2")) numC = numA + numB rngC = CDbl(numC) ' v knige C v jatsejke "resultat" zapisem summu wrbA.Close wrbB.Close ' zakrojem ot greha podalse knigi A i B End Sub [/CODE]

Это сообщение было отредактировано: Bystander, 26 Мая, 2011 г. - 08:20

Б-р-р-р-р-ррр! Боюсь, это для меня слишком сложно, я ж не программист, а бухгалтер :nuuu: