» Excel FAQ (часть 3)

Diamante
Попробуй может подойдет:
добавь дополнительный столбец и введи формулу как формулу массива (ctrl+shift+enter).
=СЧЁТ(ЕСЛИ(($a$2:$a$20)=a2;$a$2:$a$20))
подсчитывает сколько значений в массиве.
или просто СЧЁТЕСЛИ($a$2:$a$20;a2)

Добавлено:
bama
попробуй в нужном месте левый Alt + Enter

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

Diamante
Немного выше я как раз приводил пример
где есть условное форматирование,для выявления
повторяющихся артикулов.
=СЧЁТЕСЛИ($C$2:$C$2500;C2)>1

Цитата:

Soft_deadly
Так как есть повторяющиеся артикула(я их выделил розовым цветом)
то вот мое решение:
http://slil.ru/24467881

Имеются два столбца числовых данных (А и В, например). Необходимо получить разность А - В в каждой строке (т.е. из значения А вычесть значение В) и разместить его в ячейке С. Формула,соответственно, имеет вид А - В = С.
Какой способ самый оптимальный для получения результата?
С программой дела не имел никогда, поэтому не помешает пошаговое объяснение.

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

AndVGri

Цитата:

Несколько перемудрил, стандартно будет: #" руб."," "00" коп."
Знак _ используется для задания отступа, размером в следующий символ. От десятичного разделителя, похоже, не избавиться

О, спасибо, так лучше Не знал, что там ещё кавычки забиндены. С десятичным разделителем - интересно, конечно. Но, в принципе, если есть точка - то на него уже пофиг.
AlexeyID, удивительно, как это оно у вас работает.
eugene1957, жжоте, почитали бы хелп хотя бы к экселю. Просто пишите в ячейке C1 что-нибудь вроде =A1-B1 или просто ставите = и выделяете ячейку, ставите нужное действие (минус) и выделяете другую ячейку. затем энтер, растягиваете эту штуку вниз за правый нижний край. Оптимальней, наверное и некуда =)

Никак не соображу )))

Как сделать СЧЕТЕСЛИ(проверять на "И" два столбца) Т. е. есть штат предприятия:
фамилия бригада должность
Иванов 1 слесарь
Петров 1 токарь
Сидоров 2 токарь
Соловьев 1 токарь
...
Надо сосчитать количество токарей в первой бригаде.

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

CEMEH
Цитата:

Надо сосчитать количество токарей в первой бригаде

Ну можно сделать еще один столбец со значениями
1слесарь
1токарь
2токарь
1токарь
И затем уже делать СЧЕТЕСЛИ на 1токарь

Mushroomer
да, так, пожалуй, и придется, спасибо.

CEMEH
цитирую сам себя (пост от 15-07-2006 ;-)))):

Цитата:

вы не поверите, открыл Excel, написал в строке поиска СУММЕСЛИ, выбрал пунктик "Сложение чисел", узнал МНОГО нового... там есть ваш пример (суммирование продаж в зависимости от двух условий!

Т.о, если не хочется вводить доп.столбец, то можно сделать через формулу массива:
{=СУММ(ЕСЛИ((B2:B4=1)*(C2:C4=" токарь ");1))}

ВАЖНО!
Формулы в примере необходимо ввести как формулу массива (Формула массива. Формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.).

помогите, плиз, как создать
список в excel - нужен столбец в ячейках которого содержатся (маленькие) jpg

- в word таблица содержащая изображения нормально сортируется
НО в excel изображения НЕ вставляются в ячейки а оказываются как бы в другом слое и не сортируются

Выручайте.
Есть таблица, в каждой строке название предприятия, а в части стобцев (подряд) виды деятельности, которыми эти предприятия занимаются. Если предприятие занимается определенным видом деятельности, то на пересечении строки и столбца стоит цифра 1. Каждое пр-е занимается только одним видом деятельности (т.е. в этом диапазоне в каждой строке по одной единице, в остальных ячейках диапазона пусто). В некоторых строках диапазона пусто - вообще нет единиц.
Задача - сделать в другой таблице формулу, которая по строкам ищет единицы в исходной таблице и выдает название столбца, в котором ее находит. В новой таблице в одном столбце будут названия пр-й, а в соседнем нужно вывести вид деятельности.
Попробую объяснить на примере - допустим в диапазоне A3:D3 указаны названия видов деялельности, а в диапазоне A5:D5 нужно найти единицу. Допустим, что единица находится в ячейке С5, тогда формула должна вывести содержимое ячейки C3 или вывести 0, если исходная строка пустая.
Работаю в Эксель 2007 и не пойму какой функцией сделать поиск по строке и получить сначала букву столбца, в котором найдена 1, а потом вывести значение другой ячейки этого столбца (если по примеру, то нужно поискать в строке №5, получить букву С и вывести значение ячейки на пересечении столбца С и строки №3).

lucky_Luk
Имхо совсем недавно было что-то очень похожее.
задача
решение
Выложи пример, чтобы по "живому" примеру формулу писать.

lucky_Luk
1) подобная задача была про культуры и пестициды... или здесь, либо в Excel VBA
2) мне кажется, что это просится под автоматизацию на VBA (тут я могу ошибаться, как не раз показывал опыт, с помощью формул можно чёрта лысого подсчитать в Excel...)
3)
Цитата:

В новой таблице в одном столбце будут названия пр-й, а в соседнем нужно вывести вид деятельности.

а если в строке несколько единиц (несколько видов деятельности у одного предприятия), тогда какой вид выводить в соседнем?

SERGE_BLIZNUK
Цитата:

а если в строке несколько единиц (несколько видов деятельности у одного предприятия),

Не, там имхо только один вид или ничего.
Цитата:

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

lucky_Luk
Я сделал без VBA. Ну просто я его не знаю.
1) делается дополнительный столбец, куда заносится формула =ПОИСКПОЗ(1;A2:D2;0) Цель - получить номер столбца в котором есть 1. Если 1 нет, то будет Н/Д
2) далее получаем эту же информацию (т.е. номер столбца равен этому новому столбцу), но уже через функцию ВПР
3) а потом уже через Индекс по первой строке таблицы с указанием нужного столбца из пункта 2.
Пункты 2 и 3 можно объединить.
Если выложишь пример, я на нем все покажу.

SERGE_BLIZNUK

Цитата:

а если в строке несколько единиц (несколько видов деятельности у одного предприятия), тогда какой вид выводить в соседнем?

Mushroomer

Цитата:

Не, там имхо только один вид или ничего.

Именно так, только один вид.

Цитата:

Я сделал без VBA. Ну просто я его не знаю.

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

lucky_Luk
Цитата:

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

Удалось решить задачу или нет?

Ребята помогите, нужно облегчить труд при составлении калькуляций на изготовления металлоконструкций!!!
(да и вообще для состовления стоимости работ по определенным видам работ)
в шапке имеем:
№п/п,Вид работ,разряд работ,кол-во рабочих,стоимость 1 чел/час,
норма временив часах на опред.кол-во,объем работ,Трудозатраты чел-час,
Стоимость выполненных работ.
Как сделать так , чтобы вид работ заполнялся из списка,
(c другова листа,где находится весь перечень работ)(выбирая одну работу допустим ), при этом сразу заполнялись строки относящиеся к этой работе:разряд работ,кол-во рабочих,стоимость 1 чел/час,
норма временив часах на опред.кол-во,
чтобы потом ввести только объем работ и получить речультат:Трудозатраты чел-час,
Стоимость выполненных работ.
Вот пример (весит 8кб):
http://slil.ru/24516001

divik
Цитата:

чтобы вид работ заполнялся из списка

Данные -> Проверка -> Параметры -> Тип данных -> Список

Цитата:

c другова листа,где находится весь перечень работ

Мне не удалось разобраться как это делать с другого листа, хотя в хелпе такая возможность описана. Кто-то в топике делал это.

Цитата:

при этом сразу заполнялись строки относящиеся

Делается через функцию ВПР.

Есть xls-файл с макросами и формулами. Была с ним проблема, из-за которой на некоторых машинах файл не корректно работал из-за наличия в нем формул на русском языке. Тогда я, воспользовавшись ссылкой из шапки, заменил все русские функции на их английские синонимы. Но теперь возникла обратная проблема. На одной машине с русским офисом файл работал нормально, но в какой-то момент, вдруг, перестал считать данные, забитые в англоязычные формулы. Если заменить английские формулы на русские, то на этой машине все считается нормально.

Делать два варианта файла: с русскими и с английскими формулами не хочется. В чем может быть проблема? Как заставить русский офис понимать английские формулы?

Mushroomer
из списка необходимо брать по одной работе........

divik
Ну так и будет. Будет вываливаться ниспадающий список и в нем можно будет выбрать одну конкретную работу.

divik

Цитата:

c другова листа,где находится весь перечень работ

Присвоить имя диапазону с перечнем работ.
А остальное как написал Mushroomer

Mushroomer

Цитата:

Удалось решить задачу или нет?

Удалось, спасибо за наводку. Не понял куда лепить ВПР и что значит

Цитата:

номер столбца равен этому новому столбцу

Но можно и без ВПР и дополнительного столбца.
Я сделал так - в ячейку, в которую нужно вывести название вида деятельности, ставится формула:
=ИНДЕКС($A$3:$D$6;1;ПОИСКПОЗ(1;A5:D5))
т.е. ИНДЕКС выводит значение ячейки массива A3:D6, которая находится на пересечении 1-й строки массива (в которой названия видов деятельности) и столбца, в котором есть единица в строке A5:D5, номер столбца с единицей получаем из ф-и ПОИСКПОЗ.
Формула протягивается по всему столбцу, соответственно единица ищется в каждой строке (а каждая строка отвечает за конкретное предприятие).
Работает как надо, конечно если нужно выводить значение в таблицу на другом листе, то в формуле нужно добавить ссылки на исходный лист.

Добавлено:
Посмотрел решение задачи про Веза и Мовен , примерно так же решается.

lucky_Luk
Цитата:

Не понял куда лепить ВПР и что значит

Я просто твою задачу чуть по-другому понял. Я думал что у тебя есть таблица и где-то ниже ее просто вводится название предприятия и нужно вывести рядом род деятельности. А вот для того, чтобы найти это предприятие в таблице и нужно ВПР.

Mushroomer

Цитата:

Я думал что у тебя есть таблица и где-то ниже ее просто вводится название предприятия и нужно вывести рядом род деятельности.

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

Цитата:

А вот для того, чтобы найти это предприятие в таблице и нужно ВПР.

Не понял, это на случай если нужно вычислить не только вид деятельности, но и название конкретного пр-я? Или ВПР надо юзать, если нужно чтобы юзер вводил в ячейку название любого пр-я из массива и чтобы рядом отобразился вид его деятельности?

Задачка такого плана:
Есть столбцы А и B чисел (географические координаты) более 8 тыс строк
Есть столбцы С и D чисел (некоторые точки географических координат) 100-200 строк

Каким образом произвести проверку и подбор точек в одну строку если выполняется условие ((А=С)И(В=D))

Пример
есть:
A B C D
112 117 245 144
178 657 273 327
747 277 854 822
245 144
939 213
944 654
273 327
865 221
854 822

надо получить:
A B C D
112 117
178 657
747 277
245 144 245 144
939 213
944 654
273 327 273 327
865 221
854 822 854 822

CEMEH
Я правильно понял, что данные должны быть в одной (но не в одинаковой) строке каждых 2 столбцов?

lucky_Luk
Чуть позже выложу свой пример, о котором я говорю.

Mushroomer
Желательно получить в одной строке (потом с ними еще кучу вычислений делать)
Упростить задачу можно, если СЦЕПИТЬ пары столбцов A+B и С+D

CEMEH
Для твоего примера:
В столбце E формула =ЕСЛИ(ЕОШИБКА(ВПР(A1;C$1:D$3;1;0));"";ЕСЛИ(B1=ВПР(A1;C$1:D$3;2;0);A1;""))
В столбце F формула =ЕСЛИ(E1="";"";B1)
Если что непонятно - спрашивай