» Excel FAQ (часть 5)

Fintrum

Цитата:

если нужна динамическая сумма всего столбца B, то

я б посоветовал на динамические диапазоны обратить внимание... Тебе ж не десять тысяч ячеек нужно? или как?

Как обычно, всё в правильный вопрос упирается (постановку задачи).. Нормально объяснял бы, что требуется, не было бы неправильных вариантов с вставкой текущей даты

Добавлено:

Цитата:

или что-то значительно сложнее

наверняка. Нужно формулу, собирающую правильную текстовую строку, да ещё и изменяющуюся в зависимости от каких-то (каких?) условий. Представление о результате должно было бы уже появиться. Я ж ссылку приводил, там пример есть, как скобочку к номеру добавить..

Добавлено:

Цитата:

динамическая сумма

хмм.. что-то подумал, а что подразумевается под этим?

На работе на двух-трех ПК появился неприятных баг.
Во время работы с экселем в случайный момент (возможно при переходе на соседнюю ячейку табом – не уверен), перестают вводится значения в листе. Выглядит это так — визуально интерфейс экселя не реагирует на нажатия (лента инструментов листается, но нажатие на иконки ничего не дает), скролл колесом мыши перестает работать, каретка на выделенной ячейке отсутствует, при двойном клике мышки по ячейке - каретка появляется и данные ввести можно, но баг не пропадает. Лечится переключением на другой лист и обратно.
Понаблюдав выяснилось, что ошибка возникает при работе с несколькими открытыми эксель файлами (свернуты они или нет – не имеет значения), когда возникает баг и значения перестают вводиться на видимом листе, они вводятся в случайном месте другой открытой книги (=файле). Баг очень не приятный, ибо есть возможность затереть важные данные и надо постоянно следить за этим.
Система Win 7 x32, Excel 2013 со всеми последними обновлениями (лицензия), стоит КES 10.
Кто-нибудь сталкивался? Как лечится?
Принтер по умолчанию стоит HP LaserJet 700 M712, но он стоит на многих машинах, а баг только на трех.

вопрос:
есть ли функция для проверки, отсутствует ли строка из масива А в массиве Б?
я знаю vlookup - позволяющую осуществлять поиск присутствующей, и вписывать в массив Б значения из А, соответствующие той же строке.
Но если надо искать отсутствующие? Одной функцией или формулой можно?
Через дополнительый столбец, наполненный N/A на месте отсутствующих и проверку на их наличие и вписание если да второй функцией - сам знаю. А если без промежуточных столбцов?
Можно?

bredonosec
Извиняюсь, что перебиваю Вашу просьбу. Решил поделиться с радостью и решением:
Думаю, подобных задач может быть при разных сферах много. Профи темы - может в шапку напишете?
Просил:
как изложить формулу:
Чтобы получать целое число - порядковый номер месяца от первоначальной даты:
Примеру, это = 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;
Решение:
=(РАЗНДАТ(I216;$I$215;"m"))+1
Где I216 - первоначальная дата, $I$215 - ячейка с "сегодня", m - ключ - (означает месяц), может быть:
d=day, m=month, y=year
возвращает количество полных дней, полных месяцев, либо полных лет, от изначальной даты, до конечной.

образец

bredonosec

Цитата:

есть ли функция для проверки, отсутствует ли строка из масива А в массиве Б?

А что мешает использовать =СЧЁТЕСЛИ ? Если результат подсчета равен 0, значит в массиве нет такого значения.

А вообще, условие задачи какое-то размытое. Не ясно, какой вы хотите получить результат: это должен быть массив с какими-то значениями, или Вам просто нужен ответ ДА/НЕТ, или что-то еще. По-конкретней бы...

Цитата:

Не ясно, какой вы хотите получить результат

вывод только тех строк, которые отсутствуют в другом массиве. Точнее, сумм из нужного столбца массива.

Добрый день.
Помогите создать умный файл.

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

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

Соответственно вопросы:
1. Может, архитектура не совершенна и посоветуете что-то лучше?
2. Может ли excel подставлять данные, например в word? или возможно excel умеет подставлять данные в шаблоны, чтобы текст не ломался (число подставляемых символов всегда разное)?
3. С чего следует начинать?

Grekuless

Цитата:

Может ли excel подставлять данные, например в word?

ну, например, вот
и там соседние заметки

bredonosec

Цитата:

вывод только тех строк, которые отсутствуют в другом массиве. Точнее, сумм из нужного столбца массива.

Тогда можно. Формула будет примерно такой:
{=СУММ(ЕСЛИ(ЕНД(ПОИСКПОЗ(B2:B10;F2:F10;0));C2:C10))} - вариант 1
{=СУММ(ЕНД(ПОИСКПОЗ(B2:B10;F2:F10;0))*C2:C10)} - вариант 2
{=СУММ((СЧЁТЕСЛИ(F2:F10;B2:B10)=0)*C2:C10)} - вариант 3
Вот пример: http://rghost.ru/7F6kdwks8
Чуть подправил файл для красоты: http://rghost.ru/69V7TMfZb

Оно?

Grekuless

Цитата:

Может ли excel подставлять данные, например в word?

Мне по этому еще вариант подсказали. Может по легче будет:
http://www.excelworld.ru/publ/hacks/integration/excel_with_other_office/58-1-0-127

Всех приветствую.

Подскажите кто сталкивался с похожей задачей:

В общем имеет много файлов excel 2007, в них есть определенные значения - результаты формул - первая ячейка - это число, следом за ним - расчет процента числа от полного значения формулой. Задача сделать своего рода отчет, где будут перечислены все позиции со значением и процентом. Скелет я создал, но встал вопрос - как вытягивать значения из ячейки одного документа в ячейку другого документа.

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

NskRonin

Цитата:

Все формулы, что я перебирал подразумевают, что данные находятся в рамках текущей книги

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

Добавлю:
Тут - есть примеры как ссылаться на другой файл (например)

NskRonin

Цитата:

как вытягивать значения из ячейки одного документа в ячейку другого документа.

Так же, как и в пределах одного документа. Самый простой способ:
1. Откройте сразу два документа.
2. В одном из документов в ячейке поставьте знак "=".
3. И когда Excel перейдет в режим ввода формулы, ткните мышкой в ячейку второго документа. И затем нажмите Enter. И все.

Это просто пример. В остальном уже разберетесь.

DmitriC
спасибо.. открыл..
удивлен, разве критерий можно давать как тот же массив значений? Какая логика оценки?

bredonosec

Цитата:

удивлен, разве критерий можно давать как тот же массив значений? Какая логика оценки?

Да, можно. Логика оценки точно такая же как для одного значения, но только для массива. Для проделывания таких "фокусов", в Excel'е используются формулы массивов (обратите внимание на скобки {} в формуле).

Если надо пояснить работу какой-нибудь формулы из тех что я предложил - спрашивайте...

ALeXkRU
DmitriC

Спасибо за подсказки! Дальше я уже сам разберусь - это точно, кстати гугл от меня эту информацию прятал

можно ли сделать так, чтоб каждый пользователь мог редактировать только свой диапазон? и чтоб одновременно при этом работать можно было?

может ли эксель поддерживать до 10 млн строк, при помощи каких нить настроек или костылей?

Futurism ответ на нередактированный вопрос:
Код: =IF(AND(A1>=30,A1<=40),1,IF(AND(A1>=41,A1<=50),2,IF(AND(A1>=51,A1<=60),3,0)))

а не подскажете как из одной таблицы перенести список данных в другую причём предположим в одной у меня есть имена и время приходов из одной программы а в другой имена и приходы из другой и мне над перенести данные для сравнения из одной в другую так чтобы в данные попали именно туда куда надо в те же ячейки например иванов пришёл в 8 часов по одной программе и из другого excel документа перенести данные что иванов пришёл например в 9 часов

ItiratoreI

Цитата:

не подскажете как из одной таблицы перенести список данных в другую

Очень просто. Вам в помощь две функции (они будут нужны обе сразу):
=ПОИСКПОЗ
=ИНДЕКС

Пример: http://rghost.ru/6bWCv4TJr

ItiratoreI
Vlookup, hlookup - для вас
Синтаксис сами гляньте)

Спасибо ребят

Добавлено:
ед что осталось понять у меня этих записей одна тысяча что можно придумать не вводя данное условие каждый раз для тысячи ячеек

ItiratoreI

Цитата:

ед что осталось понять у меня этих записей одна тысяча что можно придумать не вводя данное условие каждый раз для тысячи ячеек

Ну так и скопируйте ее в вашу тысячу ячеек. Или схватите мышкой за правый нижний угол ячейки с формулой и протащите его вниз на сколько нужно.

vikkiv
я просто хотел задать 2 вопроса, этот и про эксель. Но первый вопрос сам сделал. а втрой
а где взять этот power pivot

Futurism 1) как включить PowerPivot
пример с 97М строк (далеко не предел)
а вообще-то это лучше в базах данных делать и к ним уже Excel как интерфейс цеплять

Подскажите, кто знает!
Для строк с текстом "Доступ ..." применена команда "Объединить ячейки и поместить в центре".



Почему отступ в последней строке отличается от отступа в предыдущих?
Проверял в "Формат ячеек" - везде для отступа указано "в центре".
И всё остальное (шрифт, формат, ...) - одинаково.

Bolenic

Цитата:

Почему отступ в последней строке отличается от отступа в предыдущих?

Проверьте "дорожку", вдруг там пробел затесался.

Aktaf

Цитата:

... вдруг там пробел затесался ...

Точно!
Хотя - копипастил строки да подправлял даты,
во всех верхних "дорожках" справа (после 00) было по 1 пробелу, а в нижней - не было.
Спасибо!

Добрый день, подскажите плиз как можно сделать такую формулку. Есть 2 таблицы, в первой 10 строк, а во второй 5, как сделать так чтобы автоматом из второй приравнять в первую со сдвигом с низ на одну строку. например
1 таблица     2 таблица        результат
0     1        1
0     2        
0     6         2
0     8        
0     7         6
0            
0 8
0
0              7
Спасибо