» Excel FAQ (часть 5)

arturrc
Спасибо. Да, моя вина, упустил. Дело в том, что формулу менять приходиться регулярно.
Т.е. в разных частях, постоянно меняется строки. Удаляются какие то, какие то добавляются. И нужно вводить новые строки, туда указывать строки и т.д.
Вот из-за этого то и нужно бы найти способ, изменив одну цифру (тут в последнем столбике), автоматом, в копированной и вставленной с формулами строчке, поменялись на ячейки, нужно строчки. Таблица "живая", регулярно и без каких то правил (конечно они от чего то все зависят), изменяются. Тут описано очень малая часть работы.

Aktaf

Цитата:

мой предыдущий закопали, видимо

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

Цитата:

Да, моя вина, упустил. Дело в том, что ...
Тут описано очень малая часть...

Вот-вот, снова о том же...

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

Всё изложенное - абсолютное ИМХО и У других вполне могут быть свои, совсем даже не совпадающие с моим спичем, причины....

ALeXkRU

Цитата:

Сочинять формулы/алгоритмы можно, когда чётко представляешь задачу.

Спору нет. А представляя, сочинять как то тоже трудновато. кажется все понятно, а изложить трудно, не доходит, какую именно не поймут, а какую часть без объяснения поймут.
Первый вопрос, не связан со вторым никак. и на мой взгляд легко понимается, но ощущаю что не так. Потому возможно и лишние [more=словоблудие]
Ровно через каждый месяц от первоначальных дат совершается определенные действия. Если через месяц этого числа не будет, то в следующий день. В 13 раз, совершается не через месяц, а только через 2 дня. Нужно определять эти даты. Формула =ЕСЛИ((D2=13); ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2-1;ДЕНЬ(B2)+2); ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2;ДЕНЬ(B2))) все это считает. За одним исключением - когда первоначальная дата в конце месяца в феврале будет не через месяц, а такая картина:
28.12.14    28.02.15    2
29.12.14    01.03.15    2
30.12.14    02.03.15    2
31.12.14    03.03.15    2
А должно быть так:
28.12.14    28.02.15    2
29.12.14    01.03.15    2
30.12.14    01.03.15    2
31.12.14    01.03.15    2 [/more]
Второй вопрос, он изложен как должно быть. Решать его нужно, а не искать как помощь не решая. Если есть возможность, то нужно [more=написать]
как можно изменяя одну цифру переводить на другую строчку. По другому нет возможности помощь. Простой пример задачи. - A1 есть определенные данные, инфа (в любых форматах). Она так же повторяется на D35, H33 J57 и т.д. В них будет указано =A1. Когда же такие же множество ячеек будут ниже или выше на определенном расстоянии, примеру 56, можно писать =A1+56.
Пока так и облегчаем эту задачу. В первой основной части таблицы есть столбик целых чисел - порядковый номер строк с 1. В других разделах таблиц используется аналогичный столбик с номерами соответствующих строк первой части.
Что делать когда таких строк много, в каждой строке множество ячеек со ссылками на первую часть таблицы? Ячейки свои места внутри этих строк не изменяют. А вот строки целиком в отношении ячеек первой части "двигаются" вне зависимости от других строк. Сами строки с инфой, добавляются, удаляются и пр. Это "ломает" таблицу. Чтобы узнать в какой строке Екселя находиться первоначальная инфа, используем следующий столбик, где указывается =A156+89. Т.е. A156 где указан номер строки первой части таблицы и прибавляем расстояние от первой ячеки Екселя, до первой строки первой части таблицы +89, поучаем строку Екселя нужной нам строки. И теперь исправляем ссылки во всех ячейках этой строки в ручную. Вот эту работу и хотелось "автоматизировать". Тогда, изменив указанный порядковый номер первой части строки, в строке второй части, мы бы изменили все ссылки этой строки.[/more]

ALeXkRU
ОЧЕНЬ ПОДДЕРЖИВАЮ!
Когда сам вопрос задавал (пытался его сформулировать) ловил себя на мысли: "как сложно тонко выразить то, что тебя не устраивает". Так у меня вопрос был примитивный. Из вопроса Aktaf тоже ничего не понял А учитывая что мы тут как бы никому ничего не должны, то логично что люди просто проходят мимо если вопрос не даётся легко. Конечно очень облегчает понимание вопроса файл с примером.
Я думал об этом когда задавал свой вопрос, а после поста ALeXkRU окончательно убедился в необходимости разработки определённой формы вопроса с добавлением её в шапку.
Форма по сути то проста:
1. Сформулировать максимально полно вопрос.
2. Приложить файл с примером.
Кто тут старший? Добавьте в шапку, если ни кто не против.

RemikR
То есть тут так трудно что-то понять - словоблудие:
Ровно через каждый месяц от первоначальных дат совершаются определенные действия. Если через месяц этого числа не будет, то в следующий день. В 13 раз, совершается не через месяц, а только через 2 дня. Нужно определять эти даты. Формула =ЕСЛИ((D2=13); ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2-1;ДЕНЬ(B2)+2); ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2;ДЕНЬ(B2))) все это считает. За одним исключением - когда первоначальная дата в конце месяца в феврале будет не через месяц, а такая картина:
28.12.14 28.02.15 2
29.12.14 01.03.15 2
30.12.14 02.03.15 2
31.12.14 03.03.15 2
А должно быть так:
28.12.14 28.02.15 2
29.12.14 01.03.15 2
30.12.14 01.03.15 2
31.12.14 01.03.15 2
Тут можно не понять разве что саму формулу. А ведь задачу можно было поставить без этой формулы.
Т.е. человек ее тоже мог не знать. И была бы только так:
--------------------------
Ровно через каждый месяц от первоначальных дат совершаются определенные действия. Если через месяц этого числа не будет, то в следующий день. В 13 раз, совершается не через месяц, а только через 2 дня. Нужно определять эти даты.
Это понятно изложено? Я уже вроде пример на Екселе приготовил, но передумал выкладывать. Показался до смешного простым. Не исполнение задачи, а саму задачу. Тут первая табличка
https://cloud.mail.ru/public/ee3311952819/%D0%9A%D0%BD%D0%B8%D0%B3%D0%B01.xls
Если уж и там не понятно, то, во втором все случаи https://cloud.mail.ru/public/245c30131527/%D0%9A%D0%BD%D0%B8%D0%B3%D0%B02.xls
На счет второго вопроса, еще не придумал какую табличку сделать.

Aktaf

Цитата:

А должно быть так:
30.12.14 01.03.15 2
31.12.14 01.03.15 2

а дальше? когда начнёт сдвигаться счётчик?
01.01 -> 01.03
02.01 -> 02.03
Правильно?
и ещё уточнение:
Цитата:

Ровно через каждый месяц от первоначальных дат совершаются определенные действия

почему, после 28.12.14 получаем 28.02.15?
Через месяц - это же: 28.12.14 -> 28.01.15?

Цитата:

А должно быть так:  
30.12.14    01.03.15    2  
31.12.14    01.03.15    2  

В феврале да,

Цитата:

а дальше? когда начнёт сдвигаться счётчик?
01.01 -> 01.03
02.01 -> 02.03  
Правильно?

Нет, в условии сказано, от первоначальных дат. 2 ка это число месяца от первоначальной даты. Следующее будет 3 и формуле "плевать" что между ними.

Цитата:

и ещё уточнение:
Цитата:
Ровно через каждый месяц от первоначальных дат совершаются определенные действия
почему, после  28.12.14  получаем  28.02.15?  
Через месяц - это же:   28.12.14  ->  28.01.15?

Потому что каждый месяц, а какой определяется в третьем столбике. Таблица слишком упрочена, чтобы понять только эту задачу. Для решения задачи не нужны другие знания.
Тут сам не понял вопрос до конца.
Через месяц - это же:   28.12.14  ->  28.01.15? конечно через месяц, так. Но Меня это не интересует поскольку там формула работает. Это первый проходящий месяц. По другому результаты должны быть:
28.01.15, 28.02.15, 28.03.15, 28.04.15, 28.05.15, 28.06.15, 28.07.15, 28.08.15, 28.09.15, 28.10.15, 28.11.15, 28.12.15, 30.12.15.
29.01.15, 01.03.15, 29.03.15, 29.04.15, 29.05.15, 29.06.15, 29.07.15, 29.08.15, 29.09.15, 29.10.15, 29.11.15, 29.12.15, 31.12.15.

Добавлено:
Вот думаю на счет второго вопроса. На самом деле условие задачи простое, но, не как ее выполнить.
Как написать ссылку на ячейку из составной части? Примеру, H649 вместо =H649. Одна часть постоянная H5 и вторая, меняется в зависимости от другой ячейки, где целые числа. По данному примеру, оставшиеся еще 644 строк (ячеек по вертикали).

Как бы не изложил, какие бы примеры не показывал проще не станет.

Aktaf

Цитата:

https://cloud.mail.ru/public/245c30131527/%D0%9A%D0%BD%D0%B8%D0%B3%D0%B02.xls

а если так?
Пояснения в файле.

RemikR
Это с моего файла ссылка или его же обратно по ошибке отправили?

Aktaf
не понял

Цитата:

а если так?

под "так" ссылка с нужным файлом

Aktaf
Посмотрите по второму вопросу:
файл
не знаю, в каких ячейках должны быть расположены Ваши формулы, воткнул наугад.

RemikR
Спасибо, дошло. К сожалению такой вариант не устраивает. Дело в том, аналогичные формулы приходиться использовать в разных местах, а главные разными спецами, которым я указываю как образец и они дальше по мере надобности используют. А вариант со скрытыми столбиками не прокатит. Если уже никак не получиться, лучше оставить как есть и там где таковые будут в ручную править. Но, очень бы не хотелось.
VictorKos
Я когда еще в закладке прочел Ваш ник, повеселел, думаю что нибудь должно получиться!
Спасибо большое. Вроде получается, пока пробовал на Вашем листе. Буду пробовать на основной таблице. Я сразу кинулся к файлу, только теперь прочел сообщение. Понял что наугад, это не страшно, главное работает.

Может по первому вопросу, дорисуете формулу с вторым Если?

Цитата:

Может по первому вопросу, дорисуете формулу с вторым Если?

Честно говоря, не совсем понял смысл первого вопроса, перечитал несколько раз, скачал и посмотрел файл. Вроде понятно, что если результат - март, то число не может быть больше 1, но не уверен... В общем, не понял.
* * *
PS Если бы не было проверки на 13, тогда подошла бы (возможно) такая формула:

Код: =ЕСЛИ(И(МЕСЯЦ(КОНМЕСЯЦА(B2;D2))=2;ДЕНЬ(B2)>ДЕНЬ(КОНМЕСЯЦА(B2;D2)));КОНМЕСЯЦА(B2;D2)+1; ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2;ДЕНЬ(B2)))

Цитата:

Честно говоря, не совсем понял смысл первого вопроса, перечитал несколько раз, скачал и посмотрел файл. Вроде понятно, что если результат - март, то число не может быть больше 1, но не уверен... В общем, не понял.  
* * *

Да, немного не так. Не если март, в марте аналогичная формула.
А если февраль, но попадает на уже (или еще) "отсутствующие даты". Из-за его "укороченности" от других месяцев. На такие дни, действует условие как бы "исключительное", не в том же "числе" (его нет), а в следующий день. И это следующий день для всех отсутствующих чисел 29, 30, 31 будет одинаковое - 01.03.хх. И если первоначальная дата на 31, с 30 дневными месяцами работает так же. Но, там только одного дня не хватает и потому как раз совпадает с условием. По сути, получается 29 число в коротких годах и 30 число в высокосном году, так же сработают правильно.
Остается в феврале - 30, 31 числа в обычных годах и 31 число в высокосном году дадут ошибочные дни.

Цитата:

PS Если бы не было проверки на 13, тогда подошла бы (возможно) такая формула:
Код:
=ЕСЛИ(И(МЕСЯЦ(КОНМЕСЯЦА(B2;D2))=2;ДЕНЬ(B2)>ДЕНЬ(КОНМЕСЯЦА(B2;D2)));КОНМЕСЯЦА(B2;D2)+1; ДАТА(ГОД(B2);МЕСЯЦ(B2)+D2;ДЕНЬ(B2)))
а добавить условие с "13" терпения не хватило... Может быть когда нибудь после подумаю.

В начальной формуле это условие прописано, видимо нужно совмещать с Вашими.
Но, тут еще загвоздка. Ваша формула пока выдает ошибку - #ИМЯ?.

Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку «Пакет анализа»

VictorKos
Извините,я обычно всегда ставлю. Забыл, у меня в 7 ке были проблемы с Екселем и пришлось его же еще раз ставить и торопясь забыл. Все нормально, получилось.
За то я дописал Вашу формулу и теперь все нормалек:
=ЕСЛИ((D5=13);ДАТА(ГОД(B5);МЕСЯЦ(B5)+D5-1;ДЕНЬ(B5)+2);
ЕСЛИ(И(МЕСЯЦ(КОНМЕСЯЦА(B5;D5))=2;ДЕНЬ(B5)>ДЕНЬ(КОНМЕСЯЦА(B5;D5)));КОНМЕСЯЦА(B5;D5)+1;
ДАТА(ГОД(B5);МЕСЯЦ(B5)+D5;ДЕНЬ(B5))))
(Разорвал, иначе портит страничку)
СПАСИБИЩЕ!!!

Продолжим, в развитие вчерашнего?
В тех самых "злосчастных картинках"
28.12.14 28.02.15 2
29.12.14 01.03.15 2
как уже выяснили, в третьем столбике целое число обозначающее месяца от первоначальной даты. Нужно вместо этих чисел (2) вписать формулу, выводящую аналогичное число, в зависимости прошедших дней от первоначальной даты до сегодняшнего дня.
Т.е. формулы берут сегодняшний день из одной клетки с =СЕГОДНЯ(), а еще лучше если будет в самой формуле ячейки. Отнимают первоначальную дату и выводят определенное число. Нужно чтобы начиная с первого дня после первоначальной даты и до аналогичного числа следующего месяца было единицей, следующие 2 и т.д. В первоначальный день 0.
Или с верхней таблицы, должны быть такие результаты:
29.12.14 - 0; (или если с "нормальным" февральским днем) 28.12.14 - 0;
с 30.12.14 по 29.01.15 - 1; с 29.12.14 по 28.01.15 - 1;
с 30.01.15 по 01.03.15 - 2; с 29.01.15 по 28.02.15 - 2;
с 02.03.15 по 29.03.15 - 3; с 01.03.15 по 28.03.15 - 3;
с 30.03.15 по 29.04.15 - 4; с 29.03.15 по 28.04.15 - 4;
с 30.04.15 по 29.05.15 - 5;
с 30.05.15 по 29.06.15 - 6;
с 30.06.15 по 29.07.15 - 7;
с 30.07.15 по 29.08.15 - 8;
с 30.08.15 по 29.09.15 - 9;
с 30.09.15 по 29.10.15 - 10;
с 30.10.15 по 29.11.15 - 11;
с 30.11.15 по 29.12.15 - 12;
с 30.12.15 по 31.12.15 - 13; (далее не важно, должны переделать или стереть)
По сути, эти цифры будут означать первый месяц, второй и так далее (так вроде лучше понимается).

Подскажите как нарисовать такой чарт
вот на этих данных
http://rghost.ru/6rkkFBplx
чарт должен быть примерно такой

т.е. круг разделен на 2 части
одна 53%, другая 47%
но каждая часть разделена на свои подчасти.
Можно ли такое сделать?

Хотелось бы с разными словами в одной текстовой ячейке связать разные гиперссылки. Можно ли каким-нибудь образом это сделать в Excel? В Word'е это делается стандартным образом. Есть ли возможность обойти такое ограничение в Excel?

Alex_B

Цитата:

Можно ли каким-нибудь образом это сделать в Excel? В Word'е это делается стандартным образом

ну, в Word-e слово - естественная единица информации, а в Excel-е - ячейка... так что, вряд ли это обойдёшь...
Разве что, использовать для каждого слова ячейку, и, косметическими средствами, делать вид, что они в одной ячейке..


Добавлено:
вот, ещё вариант через формулу:

Код: =ЕСЛИ(A1=1;ГИПЕРССЫЛКА("http://www.ya.ru";"первое");ГИПЕРССЫЛКА("http://www.yandex.ru";"второе"))

ALeXkRU
Косметика мне не подойдет, а VBA посмотрю. Спасибо.

Через формулу не подойдет, так как "первое" и "второе" должны быть одновременно видимы в одной текстовой ячейке.

Снова закопали, вот бы подождали пока ответят. Все таки, как изложить формулу:
Чтобы получать целое число - порядковый номер месяца от первоначальной даты:
Примеру, 29.12.14 в этот день до сегодняшнего дня =СЕГОДНЯ(), будет 0.
Далее, должны быть такие результаты:
с 30.12.14 по 29.01.15 - 1;
с 30.01.15 по 28.02.15 - 2;
с 01.03.15 по 29.03.15 - 3;
с 30.03.15 по 29.04.15 - 4;
с 30.04.15 по 29.05.15 - 5;
с 30.05.15 по 29.06.15 - 6;
с 30.06.15 по 29.07.15 - 7;
с 30.07.15 по 29.08.15 - 8;
с 30.08.15 по 29.09.15 - 9;
с 30.09.15 по 29.10.15 - 10;
с 30.10.15 по 29.11.15 - 11;
с 30.11.15 по 29.12.15 - 12;

Ребята, помогите с формулой. Нужно из A2 вычесть B1 деленое на 0,71, что то типа A2-(B1/0,71), получается только первое значение, затем растягиваю на табличку - первый ряд неправильно, а столбики кричат на перекрестную ссылку. Мне надо запилить вот это:



К примеру 154 рубля вычесть (118 рублей / 0,71) должно получиться -12,19 руб (отрицательное)

Так не пробовали $A2-(B$1/0,71) ?

Werty666
Попробуйте такую формулу =$A2-(B$1/0,71)

LaCastet
Повторять спустя 2 минуты уже написаного у Aktaf не серьезно.
Ладно бы большое сообщение, можно понять, человек не видел предыдущего.

Aktaf
LaCastet

спасибо, огромное! здорово, 825 вычислений за секунду))) я уж думал придется калькулятором продрочить все поля, если сеня никто не ответит)))

Здравствуйте!
Как в *.xls удалить строки, которые не соответствуют значениям годов рождения, начиная с 1922 1925 1928... и так далее с шагом +3 (до 1991), оставив только людей с этими годами рождения в списке?
Формат столбцов a-фамилия, b-имя, c-отчество, d-год рождения 19**.
Заранее спасибо!

Medicar

Цитата:

удалить строки

можно только через VBA/макросы.. Формулами - так не получится..
Нужно именно удалять? а скрывать не пойдёт? Тогда можно посмотреть в сторону фильтров или условного форматирования

Medicar
В дополнительном столбце написать формулу:

Код: =ОСТАТ(Ячейка с годом рождения;3)