» Excel FAQ (часть 5)

VicKos

Цитата:

Во всяком случае можно временно, перед вставкой скопированных из html данных, заменить разделитель в параметрах Excel, а потом вернуться к стандартным настройкам.
P.S. Я могу ошибаться, возможно, существуют и другие, менее трудоемкие способы решения проблемы, но мне пока таковых найти не удалось. Как вариант, можно попробовать сначала вставить скопированную таблицу в Word, заменить все точки на запятые, а потом уже перенести в Excel.

Это всё понятно и допустимо для разовых дел. А если подобное приходится делать ежедневно? Да ещё с таблицами не 3*4... Не-не-не, нужен способ именно для того, чтоб отучить его угадывать формат вставляемых данных...

ghosty
=MATCH(TRUE;IF(N(OFFSET(C3;;COLUMN(C3:L3)-3))>=0,75;SUBTOTAL(1;OFFSET(C3;;COLUMN(C3:L3)-2;;2))>=0,75);)
Формула массива.
Проверку на отсутствие значений соответствующих условию делать не стал - будет выдавать ошибку N/A (или как там в английской локали - не помню). Думаю, Вы с этим сами легко справитесь.

kimtan
Какой класс! Спасибо огромное!
Узнал много нового - у меня всегда формулы массива шли со скрипом. Только не могли бы Вы прокомментировать в двух словах, все-таки что при этом происходит. Делаю Evaluate, слежу, и все равно, не все понимаю
И еще вопрос: с точки зрения нагрузки на процессор что лучше - "обычные" формулы или формулы массива?

Ребята, не улыбайтесь, если вопрос Вам покажется глуповат, есть столбец в каждой ячейке которой есть school_1 , school_2 , ... , school_344.... каким образом единовременно дописать к вышепредложенной фразе (school_n) фразу @tula.edu.ru и обязательно (!!!) чтобы ссылки на e-mail, при этом были подчеркнуты, т.е. активными, при"мышином" щелчке открывалась бы почтовая программа которая стоит по-умолчанию... ! Помогай коллективный РАЗУМ!

ghosty
Вообще-то формулу можно немного сократить - применение OFFSET() в условии IF() излишне (вчера уже засыпал):

=MATCH(TRUE;IF(C3:L3>=0,75;SUBTOTAL(1;OFFSET(C3;;COLUMN(C3:L3)-2;;2))>=0,75);)

Кратенько:
Частью OFFSET(C3;;COLUMN(C3:L3)-2;;2) формируется виртуальный массив диапазонов, каждый шириной в две ячейки - D3:E3; E3:F3; F3:G3... и т.д. до конца заданного диапазона.
Далее функция SUBTOTAL, ибо только она может справиться с подобным массивом, вычисляет среднее арифметическое каждого в отдельности диапазона в этом массиве.
Это самая сложная для понимания часть формулы, дальше разобраться не трудно.

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

Добавлено:
suomifinland
Попробуйте поиграться с функцией ГИПЕРССЫЛКА()

Гиперссылка это вторичное.., главное это...

есть столбец в каждой ячейке которой есть school_1 , school_2 , ... , school_344.... каким образом единовременно дописать к вышепредложенной фразе (school_n) фразу @tula.edu.ru

..?

Как убрать из выпадающего списка автофильтра значения определённых строк?

kimtan
Еще раз спасибо большое за лаконичный и очень емкий ответ. Теперь все стало на свои места.
А где можно прочитать об особенностях обсчета формул и об оптимизации расчетов. В некоторых книгах вынужден уже отключать автоматический перерасчет из-за того, что при вводе данных начинаются подвисания (CPU - PIV 3000, RAM - 3Gb).

Цитата:

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

Даже предположить такого не мог

suomifinland!
Вариант формула в поле "С" - "=ГИПЕРССЫЛКА(B2&"@tula.edu.ru")", в поле "В" - Scholl_1 и пр.

Спасибо Вам уважаемый shaniev.., одно огорчает, что я совсем не разбираюсь в Excel, если Вас не затруднит, подскажите куда вводить предложенные Вами параметры, я кроме столбцов, строк и ячеек в Excele, абсол., ничего не знаю директрисса дала задание....

suomifinland

Цитата:

подскажите куда вводить предложенные

Цитата:

Как убрать из выпадающего списка автофильтра значения определённых строк?

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

Ребята! Кто в курсе подскажите: сегодня с утра сотрудница пожаловалась, что при открытии некоторых файлов Excel2003 на её компе появляется окошко с таким содержанием:
UI DLL Message
Version Error of dat2 data. Please install again.

Закрываю окошко - оно опять появляется и так до бесконечности (закрываю всё принудительно через диспетчер задач)
Что это значит и как от этого избавиться (переустановка Excel 2003 через панель "Установка и удаление программ" ничего не даёт)?
Вот изображение этого

Вчера такого не было. Комп соединён с другим компом LAN-кабелем.
Всё, выяснил сам: дело в принтере - по умолчанию кто-то на её компе переназначил с HP1005 на Epson R1800, привязанный к соседнему компу, а он в этом плане глючный (судя по схожей проблеме с подобной железякой в интернете).

unyqUm
Цитата:

что при открытии некоторых файлов

А эти файлы случайно не в openoffice были сделаны? http://www.oooforum.org/forum/viewtopic.phtml?t=57293

Добавлено:
Хотя наверное openoffice тут не причем.

операционная система какая? На Office 2003 установлен SP3?
есть ли принтер, подсоединенный к этому компьютеру? если есть, то какой? Epson?
http://www.epson.co.uk/Printers-and-All-In-Ones/Large-Format/Epson-Stylus-Pro-3800/Drivers-Support?target=article&extn=.html&articleId=2115

Если эти файлы открыть на другом компьютере, то это сообщение тоже будет?

Есть одна маленькая непонятка с экселем, вернее с апострофами...
В ветке нашел только макрос для удаления и упоминание про форматирование импортированных из Лотуса ячеек.
А теперь сама непонятка...
Знакомая попросила помочь с обработкой очень большого документа: прописывает формулу, получает ошибку (офис 2003). Сам документ - выгрузка из бухгалтерской базы (1с8) - присылается по почте для обработки.
Как выяснилось, загвоздка в апострофах. Сам работаю в ОпенОфисе, где через Найти и Заменить (.* &) решение "проблемы" заняло меньше секунды.
По её словам, в МСОфисе это не работает... Неужели? К сожалению, проверить не могу по определенным причинам (OS Linux), но узнать, как от них избавится без записи макросов (юзер бесправный) очень бы хотелось...

vallyol
Апострофы перед числами?
Если да:
Скопировать любую пустую ячейку. Выделить диапазон с "неправильными" числами, ПКМ - специальная вставка - операция - сложить.

kimtan

Цитата:

Апострофы перед числами?

Именно так!
И за совет СПАСИБО!

2 All Подскажите пожалуйста как узнать значение ячейки при ее заливке любым цветом.
Типа если залито то = 1, не залито = 0.

Есть отчет в виде таблицы на 9500 строк и 9 столбцов. Необходимо сделать следующее:
1. добавить строку через каждые 50 строк.
2. к каждой такой добавленной строке длинной с колонки А по колонку G (т.е. в ширину всей таблицы) применить жирную границу.
3. в каждой этой добавленной строке вставить: 1) слово (с жирным шрифтом и по центру ячейки) "ИТОГО" в ячейку в колонке D; 2) сумму 50 верхних ячеек по колонке F (так же с жирным шрифтом и по центру ячейки); 3) сумму 50 верхних ячеек по колонке F (так же с жирным шрифтом и по центру ячейки и с разделением групп разрядов).
Вот тут выложил образец.
Смог выполнить только 1-й пункт, запустив макрос:
"Sub Вставка_строк()
For i = 1 To 10000 Step 51
Rows(i).Insert
Next
End Sub"
Вставило строки без проблем. А вот с остальным не получается. А делать это вручную просто Мало того, что уйма времени уйдет, так еще и ошибок можно запросто наделать .

RJ2k6
самое простое - макрос записать:
- встать в первую строку с данными в первой ячейке
- включить запись макроса
- проделать всё, что требуется (т.е. перейти вниз на 50 строк (клавиатурой!), оказавшись в строке итога, шагнуть в ячейку D, вписать в ней "ИТОГО", выделить, сделать жирным, шагнуть в следующую ячейку, вставить формулу Сумма... и т.п. до конца алгоритма, шагнуть на строку ниже, в первую ячейку)
- остановить запись макроса..

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

ALeXkRU
А ведь действительно все довольно просто! Приду на работу в понедельник сразу попробую так сделать. Огромнейшее спасибо!

Подскажите пожалуйста нужно посчить количество ячеек в столбце отвечающих условию что значение в ячейке больше чем в ячейке в соседнем столбце.

Antonio755
Вариант - данные в поле "В", для счета - в "С", формула в "D" - =СЧЁТЕСЛИ($B$2:$B$22;">"&C2).

Цитата:

одскажите пожалуйста нужно посчить количество ячеек в столбце отвечающих условию что значение в ячейке больше чем в ячейке в соседнем столбце.

Нужно использоыать "формулу массива". Вот пример попроще http://support.microsoft.com/kb/275166/ru. Теперь вернемся к Вашему случаю. Имеем два диапазона, скажем C5:C13 и D5:D13. Формула получается простой

=СУММ(ЕСЛИ(C5:C13>D5:D13;1;0))

НО! Если вы ее вводите в ячейку нажатием Enter, то она НЕ будет применена к массивам C5:C13 и D5:D13 и выдаст 1 или 0.
Вводить надо одновременным нажатием CTRL+SHIFT+Enter. Лишь тогда она даст правильный результат.
В качестве признака, в строке формул Вы увидите, что формула заключена в фигурные скобки.
(Справка по Excel "Формулы массива и их ввод")

shaniev в твоем варианте почему то теряется одно значение, непонятно почему все суммы -1.

ded2007 Спасибо твоя формула работает!

Antonio755
А вы свое условие внимательно перечитайте... И вникните в формулу ded2007'a... Впрочем, все сделано по вашему объяснению "на пальцах" - был бы пример в XL - был бы и другой разговор...

Если бы диапазоны были именованные, скажем данные_за_январь и данные_за_февраль, то формула была бы читабельнее
{ =СУММ(ЕСЛИ(данные_за_январь>данные_за_февраль;1;0)) }

shaniev
Тебе тоже спасибо.

ded2007
И так все работает именно так как я и хотел

Еще одна задачка:
Нужно посчитать сумму последних 3 значений в столбце, но условие что каждый день столбец пополняется новыми значениями. Надо чтоб всегда считались последние 3 значения

ded2007
Уважаемый, ded2007! Заполним два поля значениями от 15 до 29, в правом крайнем поле введем две вышеуказанные формулы, протянем и... сравним результат, помятуя о том, что надо определить кол-во чисел более (!) 15, 16, 17... Или речь идет о том, что ">=X"?

Antonio755
Цитата:

Нужно посчитать сумму последних 3 значений в столбце, но условие что каждый день столбец пополняется новыми значениями. Надо чтоб всегда считались последние 3 значения

Считаем, что данные расположены в столбце D.
1. Если данные начинают идти прямо с первой строки (т.е. c D1 и далее), то помогает функция СЧЕТЗ(D:D), которая считает количество заполненных ячеек, т.е. выдает номер последней строки.
2. Если же все-таки присутствует "шапка" и данные начинаются, скажем, с 7-ой строки, то номер строки последней заполненной ячейки будет счетз(D:D)-счетз(D1:D6)+6
3. Получаем формулу
=СУММ(ДВССЫЛ("D" & СЧЁТЗ(D:D)-СЧЁТЗ(D1:D6)+4 & ":D" & СЧЁТЗ(D:D)-СЧЁТЗ(D1:D6)+6))

---

Добавлено:
shaniev
Цитата:

Заполним два поля значениями от 15 до 29, в правом крайнем поле введем две вышеуказанные формулы, протянем и... сравним результат, помятуя о том, что надо определить кол-во чисел более (!) 15, 16, 17... Или речь идет о том, что ">=X"?

Честно говоря, не допонял
Что-то работает неправильно?