Здравствуйте, уважаемые специалисты.Создал сложную систему расчётов из трёх последовательно связанных Листов т.е. расчёты из Листа1 передаются для последующих расчётов на Лист2, затем итоги расчётов Листа2 служат отправными данными для расчётов на Листе3. Все 3 листа так же берут статичные данные с Листа Констант для своих расчётов. Используется множество ячеек типа Список, заданных в ДиспетчереИмён формул. Пока нет макросов или модулей из VisualBasic, но возможно далее понадобятся, пока можно обходиться без них. Назовём эту систему из трёх листов расчётов - шаблоном, для последующего копирования.

На основании данного Шаблона - требуется создать в этом же документе (или лучше разнести эти КопииШаблонаХХ по новосоздаваемым Файлам Эксель, не суть, просто очень много листов получается в одном файле) на данном этапе около 40 копий (по 3 Листа), в дальнейшем число копий может увеличиваться примерно до 60 или чуть более.
Задача - требуется создавать листы КопииШаблонаХХ таким образом, что бы свои ячейки с расчётами они проводили согласно формулам взятым из шаблона. Это нужно для того, что бы когда придётся изменить формулу в Шаблоне, добавить или удалить строку или столбец - все КопииШаблонаХХ подхватили произведённое изменение форматирования или изменение формулы и поменяли свои расчёты, согласно уникальным для каждого листа КопииШаблона ячейкам с ручным вводом цифровых значений, которые из шаблона не берутся, а задаются в этих ячейках на каждой КопииШаблона вручную.

В файле примера для наглядности упростил все формулы до тривиальных расчётов типа Цена-Количество-Итого и в качестве Шаблона задал всего 1 лист.
Помогите, пожалуйста, понять, какими средствами Эксель лучше всего произвести подобный перенос форматирования и формул заданных на листе Шаблона на множество листов КопийШаблонаХХ.

Ну наконец-то - работает в исходной таблице! Формула начала работать корректно после того как эксперементально подобрал значение вместо тройки сменить на пятёрку. Внешне таблица из примера и таблица конечного назначения формулы выглядят идентично. Не понимаю почему пришлось менять 3 на 5, почитаю подробности позднее...
... но теперь это работает. Попробовал менять значения мин\макс\группа, пока ошибок не обнаружено. Буду тестировать дальше. Благодарю за внимание.

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

Теперь спешу доводить свой табличный замысел до победного завершения, а то засел на этом моменте уже на несколько дней, а без этого не можно было двигаться дальше. Теперь лёд тронулся. Очень рад. Делюсь счастьем по поводу решённой части задачи. Ура, товарищи. Ура. )

Можете предложить рабочее решение? В файле. Любыми средствами.
Пусть тогда этот промежуточный столбец (столбцы) формируются на листе Формула.

Промежуточные вычислений и скрытые столбцы можно потерпеть, только не пустые строки в файле. Благодарю за попытку, но выпадающий список в файле формируется некорректно. Например, если задать группу Овощи и границу от 30 до 30, то в список попадает всего одно название, тогда как с артикулом 30 таких наименований должно быть в списке два. Выборки по другим границам диапазона так же формируют некорректный список.Эта версия работает отлично. Единственный недостаток - при любой комбинации группы и границ диапазона по артикулу - в самый нижний пункт выпадающего списка попадает одна Ё. Вникаю в формулу. Пытаюсь нагуглить варианты как от неё избавиться. Есть идеи как это поправить?

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

Эта статья мне хорошо знакома. Перечтена взад-перёд-диагональю до дыр. Как и все остальные статьи в интернетах по актуальному и смежным запросам. Экселем пользуюсь не часто и мои познания в оном крайне тривиальны. Для всех остальных задач исходной таблицы мне удалось адаптировать нагугленные формулы, кроме этой. Эта штука самостоятальными потугами пока никак не даётся к адаптации под продемонстрированную в файле задачу.
замените слово артикул на любое другое, подразумевающее неуникальность\неоднократность применения. пусть это будет не артикул а номер заказа или просто номер.

на странице "Данные" строки свойства 1-10 не имеют для данного примера никакого значения, поэтому просто оставил их значания пустыми, не удалял совсем просто что бы сохранить общий вид таблицы. вероятно - стоит их удалить или не обращать на них внимания.

задача: на листе Формула в ячейке "список названий" (В5) расположить контекстно зависимый список названий, соответствующих выбранной Группе (в ячейке В2) с артикулами в заданном диапазоне (от значения задаваемого в ячейке В3 до В4)

Здравствуйте.

Перепробовал множество вариантов, пока ни один не работает корректно, может потому что строка артикулов выше строки названий, в крайнем случае допустимо переместить её ниже, пока пробую найти решение так как есть. Желательно обойтись без промежуточных строк\стобцов, но если никак - допустимо и с ними. Будут скрыты. Прошу помощи.


Задача наглядно продемонстрирована в упрощённом эквиваленте - см. файл Пример2.
примечание, если это имеет значение для формул - артикул не уникален т.е. разным названиям может неоднократно соответствовать одинаковый артикул. главное получить в выпадающем списке все названия с артикулами входящими в заданный диапазон.

Здравствуйте, уважаемые специалисты. До свидания.

DV, Эта формула что-то переводит всякую дату в одно целое число.

9/17/2009 40073
9/15/2009 40071
8/27/2009 40052
8/25/2009 40050
8/20/2009 40045

Hugo, Всё верно. Я неправильно Вас понял, вы имели ввиду редактирование файла, а не сообщения. Я отыскал исходный файл выгрузки. Он, действительно, открывается блокнотом в относительно сносном прочтении! Не меняя разрешения, я импортировал этот файл как текст указав кодировку 1251, разделители знаком табуляции (что бы это ни значило) и указал формат МДГ и файл, к моей великой радости, открылся экселем в читаемом виде! И даты стали все в одном порядке!!!

Только даты теперь записались не через слеш, а через точку. Всё же я попробовал. Добавил столбец рядом с исходными датами в котором применил формулу многоуважаемого DV. Она сработала, не смотря на то что в ней присуствовало условие поиска "/", а не ".". Забавно.

Я очень рад. Друзья. Вы мне очень помогли! Сейчас я вынужден отойти, а когда вернусь - приступлю к детальной проработке данных. На первый взгляд всё в файле кажется корректным!

Разрешите поблагодарить Вас за оказанное внимание. Я желаю Вам всего самого наилучшего, здоровья и процветания, что бы Вам часто встречались люди, добрая воля и сильные качества которых оказались востребованными в разрешении актуализировавшихся препядствий на жизненном пути, преодолевая которые самостоятельно или с дружественной помощью, мы как растём, так и больше ценим достигнутые плоды, которые в свою очередь можем возложить на алтарь добрых дел для достижения новых результатов, в процессе которых и происходит наша жизнь, добра и радости в которой я желаю Вам от всей души!

Hugo,

Исходный файл WorkoutTrackerExport.xls был прекреплён мной 8 Июня 2013 23:23:26 в 9м сообщении. До сей поры оно не редактировалось.

Данный файл несколькими текстовыми редакторами (включая блокнот) открывается в не читаемом формате, всё там квадратиками и кракозябрами.

Импорт "как текст" данных из этого файла экселем не производится. Пробовал.

Hugo, Прикреплённый изначально файл и есть исходный файл выгрузки. Я только убрал все данные не относящиеся к проблеме с датами и сократил длину списка дат, оставив лишь ряд значений для примера. Сама же структура файла, его разрешение, формат ячеек, их заполнение оставленно без изменений.

К сожалению, выгрузка не изобилует какими-либо настройками и представляется только в старом *.xls формате, загрузить его как текст эксель не хочет уметь. Как иначе задавать форматы ячеек при открытии файла - мне не ведомо.

Может возможно задать формулой условие проверки на наличие разделителей "/" и "."
Даты через слеш - оставлять как есть.
Даты с точкой - "неправильно понятые экселем", их вернуть к исходному виду путём перемены мест числа и месяца  :?:

DV, Ваша формула восхитительно работает. Вы великолепно владеете экселем, я бы сказал - Вы владыка экселя! Мне придётся прочитать дюжину книг и справочных материалов, провести массу человекочасов за напряжённой практикой, прежде чем удастся уразуметь работу формулы даже в таком виде, не говоря о её упрощениях. Вероятно, к тому времени я бы уже и забыл за решением какой задачи обратился к экселю. Всё же, действительно стоит учиться, но для решения насущной проблемы перенесения дневника на ПК Ваша скорая помощь просто неоценима! =)

Не будем распыляться, обсудим только второй вопрос, он видится самым сложным и фундаментально важным, только с его решением я наконец смогу корректно обработать накопленные данные:

Как я уже говорил, файл выгружается из программы на КПК с датами в виде ММ/ДД/ГГГГ. И когда я его открываю на ПК в экселе, то он сразу же интерпретирует видимые ему даты под свой стандарт ДД.ММ.ГГГГ. И в этом загвоздка. Ваше решение великолепно работает, но оно не учитывает разделение в интерпретации прочтения дат экселем. Как видно в исходном файле, эксель делает различия между двумя типами дат:

1) Даты любого года и месяца с числом месяца в интервале с 1 по 12 включительно. Эта часть дат экспортированных с КПК в формате ММ/ДД/ГГГГ эксель без разбора интерпретирует словно видит их как ДД.ММ.ГГГГ и в этом ошибка экселя, не учитываемая Вашей формулой.
Пример: Эксель читает 01/12/2009, которое на КПК означало 12 января 2009, а на компьютере автоматически ошибочно распознаётся как 1 декабря 2009, и записывает егокак 12.01.2009, т.е. меняет число и месяц местами автоматически.
Примета: Эти данные в исходном столбце записываются не через слеш, а через точку. Они же автоматически выравниваются по правому краю. Им же автоматически назначается формат ячейки из категории ДАТА вида *14.03.2001.
ОШИБКА: В этих датах эксель автоматически меняет местами месяц и день в отличии от остальных дат. Починить это сменой формата на ММ/ДД/ГГГГ мне не удалось, что бы хотя бы весь исходный столбец был одинакового Америкосовского формата... Тогда бы Ваша формула сработала на ура!

2) Даты любого месяца с числом месяца в интервале от 13 до конца месяца. Эти даты эксель не может интерпретировать как даты, потому что не может ошибочно посчитать число за месяц, потому что 13го и т.д. месяцев не существует в принятой системе разделения времени и поэтому оставляет эти записи в файле как есть.
Примета: Эти даты записаны через слеш, выровнены по левому краю, формат ячейки назначается ОБЩИЙ.
Здесь ошибки нет и Ваша формула работает идеально.

Извините, за излишние подробности, я постарался дать подробное описание проблемы. Я так понимаю нужно привести исходный столбец в единый исходный формат, что бы все даты были записаны одинаково в виде ММ/ДД/ГГГГ. Как это сделать - я могу только догадываться, знаний нюансов логики экселя мне не хватает.

DV, Да, файл Ваш сильно пригодился. Почти получилась моя табличка уже.

2 вопроса по формату дат:

1. Как из текстового значения вида ДД.ММ.ГГ Ч:CC перевести в число в обратном прочтении вида ГГ.ММ.ДД ЧЧ:ММ (так удобно для сортировки), при этом заставить выводится нули перед часами, днями и месяцами (так удобно визуально, что бы строка с датой всегда имела фиксированную длину)

2. В прикреплённом файле оставлен один столбец. Это фрагмент выгрузки данных c КПК из спортивной программы, которая к сожалению писана Америкосом и представляет в выгрузку даты только в формате ММ/ДД/ГГГГ. Эксель это пытается читать в стандарте другого формата - ДД/ММ/ГГГГ и, как видно, некорректно распознаёт некоторые даты. Они автоматически выравниваются по правому краю столбца. Выделенный красным фрагмент переведён в формат ячейки вида ММ/ДД/ГГГГ. Прошу помочь перевести в число вида ГГГГ.ММ.ДД.

DV, Да, это то что нужно! Благодарю Вас за помощь, Вы меня очень сильно выручили! Сейчас попробую доработать Вашу формулу в исходной таблице.

Для аналогичных таблиц, где я точно знаю, что учтённое секундомером время никогда не превысит часа, т.е. мне не потребуется учёт часов, а так же долей секунд - как можно округлить или отбросить эти значения из того же исходного текстового формата ЧЧ:ММ'СС"ДС до числового значения ММ:CC?

Я искал по форуму и не нашёл искомого. Всего несколько похожих тем и моему случаю они не помогли. Уточню задание:
Я хочу научить эксель понимать именно такой формат записи времени: 00:03'24"83, а не такой 00:03:24,83 и ни какой другой. Мне хотелось бы иметь возможность вставлять в файл время из секундомера без модификаций знаков преминания между значениями.
Если Вы знаете где обсуждался столь тривиальный вопрос - прошу указать ссылку.
По указанной ссылке обсуждается даже близко не относящийся к теме вопрос.

anvg,
В Вашем файле есть всего одно заполненное поле с формулой =ТДАТА, которая возвращает целое число, представляющее текущие дату и время. Это шутка или зачем?

Здравствуйте, друзья.

Задача проста: в Microsoft Excel 2010 я хочу создать графики (столбиками), отображающие прогресс в занятиях бегом, плаванием и некоторыми другими видами активностей, допускающих отслеживание результатов с помощью секундомера.

Мой секундомер способен выгружать данные времени вида ЧЧ:ММ'CC"ДС (часы : минуты ' секунды " доли секунды, доли секунды измеряются от 0 до 100).

К сожалению попытка построить график по этим значениям показывает, что Microsoft Excel 2010 не понимает этот формат записи времени ни в одном из испробованных мной форматов ячейки. График у меня не строится.
Вот для примера ряд значений:
00:03'24"83
00:03'36"92
00:03'38"29
00:01'15"08
00:02'19"27
00:03'40"63
00:03'44"38
00:03'39"82

Если я правильно понимаю - необходимо создавать свой формат ячейки. Я пробовал, зарылся в мануалы, узнал очень много интересной информации об устройстве Microsoft Excel 2010, о форматах, о времени, и ничего из этого не помогло мне решить поставленную задачу. Прошу помощи у старших товарищей.