» Excel FAQ (часть 2)

Спасибо. Не знаю про префикс в формулах.

Подскажите, пожалуйста:
есть данные:
2007-04-28 => 10р
2007-04-29 => 20р
2007-04-29 => 30р
2007-04-29 => 40р
2007-04-30 => 50р
включаю фильтр, отбираю
2007-04-29 => 20р
2007-04-29 => 30р
2007-04-29 => 40р
получаю сумму ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
90р
Подскажите, как получить 20р (значение самой верхней ячейки)?
Пожалуй, в формулировке (значение ПЕРВОЙ верхней видимой ячейки) и кроется ответ, но не знаю как.
Заранее спасибо.
-----------------------------------
Задавал этот несложный вопрос
Ru-Board/Excel FAQ (часть 2) = 17:17 30-04-2007
Ru-Board/Excel VBA (часть 2) = 17:28 30-04-2007
excel.1gb.ru = 01.05.2007 02:53
planetaexcel.ru = 01.05.2007 04:12
Огромное спасибо за помощь Евгению А., Platinum Boarder форума excel.1gb.ru за исчерпывающий и оперативный ответ и пример в Excel с использованием ИНДЕКС() и ПОИСКПОЗ(). На всех этих четылех форумах отписал ответ Евгения А. с excel.1gb.ru, полагаю, этот полезный прием работы понадобится и другим пользователям. Также спасибо за подсказку Николаю Павлову (planetaexcel.ru).
Хочется отметить контент сайта planetaexcel.ru: много полезных tips, отлично оформлены, бесплатные советы по сабжу от автора сайта. Но сайту не хватает форума, поэтому автор сайта Николай Павлов "на сайте один", как он пишет. Очень жаль, что пока один, будем надеяться на улучшения. Успехов ему!
Всем спасибо!

Помогите решить задачу. Имеются две книги, допустим Книга1 и Книга2. В каждой книге на Листе1 в столбце А перечень организаций, причем в Книге2 могут быть организации, которые есть в Книге1. Как поместить в конец столбца А Книги1 недостающие организации из Книги2 ? Как в Книге1 так и в Книге2 организации не повторяются.
Задавал этот вопрос в Excel VBA, меня отправили сюда.

aks_sv
А можно эту задачу заменить на задачу по выявлению таких органиций, а не помещения их в конец столбца А Книги1? Если да, то тогда это можно сделать через функцию ВПР.

Mushroomer
Можно выявить, но в итоге нужно их поместить в Книгу1

В "Поиске решений" выскакивает ошибка "слишком много изменяемых ячеек".
Подскажите пожалуйста сколько ячеек можно изменять в "поиске" и возможно ли обойти это ограничение?

здравствуйте, имеется таблица следующего формата

A B C D

start | end | val | path
...
X1 X3 V1 P1
X2 X3 V2 P2
...
X3 X6 V3 P3
X4 X6 V5 P4
X5 X7 V5 P5
...

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

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

записываю следущим образом

{=МАКС(ЕСЛИ(B:B=$A1;СМЕЩ(B:B;0;2);0))+$D1}

но похоже ошибка во втором и третьем пунктах. подскажите пожалуйста как исправить

все, сам разобрался =)
решил следующим образом

в первой строке пустые ячейки, либо заголовки
таблица отсортирована по второму столбцу

формула для второй строки:

{=МАКС(ЕСЛИ($B$1:$B1=$A2;1;0)*$D$1:$D1)+$C2}

Mushroomer

Цитата:

А можно эту задачу заменить на задачу по выявлению таких органиций, а не помещения их в конец столбца А Книги1? Если да, то тогда это можно сделать через функцию ВПР.

А как выявить?

aks_sv
Организации отсортированы по алфавиту?

Mushroomer
Если надо-отсортирую

Подскажите, пож-та, если некоторые ячейки листа с формулами защищены, то сортировка невозможна в данном случае?

aks_sv
Я чуть позже отвечу (запарка на работе). Хотя ничего сложного там нет. Наличие или отсутствие сортировки влиет только на третий параметр в функции ВПР. А так надо открыть оба файла и через ВПР проверить наличие каждой организации из одного файла в твердо зажатом (т.е. через $) диапазоне ячеек другого файла.

Народ помогите! срочно!
вообщем есть столбец
4130
5599.1
6372
3265
23626
293888
32732837
87987
762639
278930
к этим числам надо прибавить 25%, можно ли это сделать одним разом?
Заранее благодарен:)

votya85

Цитата:

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

1) в любую свободную ячейку пишешь 1.25 (125 процентов - то, что должно получится)
2) на этой ячейке Ctrl-Insert (т.ж. что "копировать в буфер обмена" или
меню "Правка"- "Копировать")
3) выделяшь столбец с твоими цифрами
4) меню "Правка" - "Специальная вставка"
5) выбираешь операция "Умножить"
всё.

aks_sv

Цитата:

Как поместить в конец столбца А Книги1 недостающие организации из Книги2

как Вам всё не везёт... ;-(( то в больницу попали, то теперь Вам ответить некому ;-(((
и у меня сейчас со временем напряги ;-((((
ладно. Вкратце. с помощью формул Вы легко можете выделить ОДИНАКОВЫЕ (или отличающиеся) значения в разных столбцах.
было чудесное решение от ZORRO2005 через условное форматирование:


А готовое решение, для того, чтобы поместить данные из Книги2 в книгу1 на VBA постараюсь написать сегодня вечером...

Желаю удачи.

aks_sv
Значит смотри:
В книге1.xls в столбце A [more]
Организация 1
Организация 3
Организация 5[/more]

В книге2.xls в столбце A [more]
Организация 1
Организация 2
Организация 3
Организация 4
Организация 5
Организация 6
Организация 7[/more]

В книге1.xls в ячейке А5 формула =ЕСЛИ(ЕНД(ВПР([Книга2.xls]Лист1!A1;A$1:A$3;1;0));[Книга2.xls]Лист1!A1;"")
Ну и разгоняешь ее вниз
Ну далее вставка как значения, и у тебя в книге1.xls недостающие фирмы из книге2.xls (ну пустоты надо удалить)

aks_sv
Ну вот, уважаемый Mushroomer уже решил задачу...
В модуле на VBA, насколько я понимаю, уже необходимости нет?

Mushroomer
Почему именно в А1, в книге1 заполнены 3 строки? И что такое:

Цитата:

Ну далее вставка как значения?

В общем сделал как Вы написали, вставил формулу в А1, скопировал и вставил в А6:А20 и.... ничего не получилось. Попробовал все на одном листе ( убрал [Книга2.xls]Лист1! и т.д )- тоже самое (нули в 8,12,16 и т.д. ячейках)
SERGE_BLIZNUK
А к VBA все равно надо будет обращаться: количество организаций постоянно меняется и их большое количество 600-1000
Спасибо за участие

aks_sv
Цитата:

Почему именно в А1, в книге1 заполнены 3 строки?

потому что это пример и формула дана только для этого примера. Для твоего примера ее надо переработать. Посмотри описание фукнции ВПР (или воспользуйся мастером формул) и все поймешь. В моем примере организации отсортированы

Цитата:

что такое: Ну далее вставка как значения?

Ты в книге1.xls получаешь только ссылки на ячейки из книги2.xls. А должны быть значения. поэтому надо выделить полученные организации и далее по меню Правка -> Специальная вставка -> Значения

Есть таблица вида
|Ст.1 | Ст.2 | Ст. 3|
| 10 | 20 | 5 |
| 20 | 30 | 1 5 |
| 30 | 35 | 10 |

Нужно на основании этих данных построить следующую гистограмму:
По оси Х от 10 до 20 высота по Y -5;
от 20 до 30 – 15;
от 30 до 35-10
Возможно ли это сделать в excel-е? Если да, то как? (хотелось бы посмотреть на пример такой гистограммы).
Заранее спасибо.

Доброй ночи.

Такая вот заковырка: Как получить разницу времени без отрицательного результата (проблемма с переходом времени 0 ч 00 мм)

Т е если из 1:00 вычесть 23:00 должно получиться 2:00

Отправился в 23:00
Прибыл в 1:00
Время в пути = ???

aks_sv
программка на VBA
[more]
программа переноса недостающих данных с одного листа (w2) на другой лист (w1).
Имена нужных книг/листов прописать в Set w1 = ... Set w2 = ...

Код:
Public Sub AddMissingDataFromSheet2()
' оригинальная идея: (c) AndVGri
'В редакторе VBA в меню Tools, пункт Reference
' в диалоге поставте галочку для Microsoft Scripting Runtime.

Dim pAll As New Scripting.Dictionary
Dim rowLast As Long, Column_A As Long, RowLast2 As Long
Dim w1 As Worksheet, w2 As Worksheet
Dim iRow As Long, iCol As Long, vEntry As String

Set w1 = Workbooks("Книга1.xls").Worksheets("Лист1")
Set w2 = Workbooks("Книга2.xls").Worksheets("Лист1")

Column_A = 1&
rowLast = Cells(w1.UsedRange.Rows.Count, Column_A).End(xlUp).Row

' сохраним весь столбец А в Scripting.Dictionary для удобства поиска
For iRow = 1& To rowLast
vEntry = CStr(Cells(iRow, Column_A).Value)
If Not pAll.Exists(vEntry) Then
pAll.Add vEntry, iRow
End If
Next iRow

RowNewCount = rowLast + 1
RowLast2 = w2.Cells(w2.UsedRange.Rows.Count, Column_A).End(xlUp).Row
If RowLast2 > 60000 Then
MsgBox "Ой, что-то не так со вторым листом... Ничего не делаем!"
Exit Sub
End If
For iRow = 1& To RowLast2
vEntry = CStr(w2.Cells(iRow, Column_A).Value)
If Not pAll.Exists(vEntry) Then
w1.Cells(RowNewCount, Column_A).Value = vEntry
RowNewCount = RowNewCount + 1
End If
Next iRow

End Sub

CEMEH
Идея такая же как и у SERGE_BLIZNUK.
=ЕСЛИ(B2<B1; 24 + ЧАС(B2)-ЧАС(B1); ЧАС(B2)-ЧАС(B1))

Проблема с установкой рамки у некоторых ячеек.

Появилась после установки Excel 2007. Мой старый макрос стал вылетать на строчках с установкой бордюра (рамки) ячеек. Но пишу сюда, а не в программирование, т.к., как оказалось, проблема не в программе. Если я сам вручную пытаюсь поменять рамки ячейки, то просто ничего не происходит, рамки остаются прежними.

Это происходит только на некоторых ячейках (в них уже установлены тонкие рамки, но мне нужно жирные). Остальное форматирование вроде работает, но при попытке залезть в диалог "форматировать ячейки" тоже ничего не происходит.

В чём дело?

Защита листа не установлена.

Дополнение. Похоже, это происходит только в тех ячейках, которые добавлены макросом. Но там ничего особого, да и в 2003 экселе работало всё...

Ребят помогите немогу вспомнить функцию.

Есть массив данных. Надо получить количество значений конкретного числа.
например сколько раз в этом массиве повторяеться цифра 19216810017

спс

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

ZevSforum

Цитата:

повторяеться цифра 19216810017

наверное, всё таки не цифра, а число?..
может, фукция СЧЕТЕСЛИ(диапазон;критерий) Вам поможет?

Да немножко гиморно но помогло.
спасиб.

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

Например я нашел что айпи 19216810073 чаще всех. А вот как бы выяснить с какой групой айпи она чаще всех? Например когда число 19216810073 присущо, какое число еще присуще чаще всего вместе с ним.

надеюсь понятно изяснил. даже незнаю существует ли такая функция

ZevSforum
да... простите, но изъясняетесь Вы не очень ясно...
если я вас правильно понял, то в одно столбце (пусть для примера A) — у вас
ip-адрес, в столбце B — какое-то ещё число... и стоит задача найти, какой сочетание ip-адрес + доп.число встречается чаще всего?...
тогда это можно решить через дополнитлеьный столбец.
в него заносите формулу =СЦЕПИТЬ(A1;B1) - разгоняете её до конца данных.
потом в любой столбец (для просто лучше в той же строчке) вставляете формулу:
=СЧЁТЕСЛИ($D$1:$D$18;СЦЕПИТЬ(A1;B1))
здесь D1-D18 - дополнительный столбец, где внесена формула =СЦЕПИТЬ(

всё. ну, для красоты можно ещё подсветить ячейки с максимальным числом через условное форматирование (если мы считаем счетесли в столбце C):
=ЕСЛИ(C1=НАИБОЛЬШИЙ($C$1:$C$18;1);ИСТИНА;ЛОЖЬ)

Хмм. Попробую. Неслышал даже о такой функции.

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