» Excel FAQ (часть 3)

Цитата:

Макросами имхо надо делать только то, что обычными формулами не делается

Согласен на половину - нюансов в данном вопросе достаточно.
Нюанс в защиту моей позиции/подхода:

и мой и твой пример использования формул при решении достаточно "извращен" - твой более простой(но 3 разных формулы), мой более универсальный (но более громоздкий). Допустим через месяц введут еще один параметр и придется обратно разбирать уже не A1&"_"&A2&"_"&A3 а A1&"_"&A2&"_"&A3"_"&A4 ? У меня надо будет лишь дописать 4-ю формулу, у тебя же - и дописывать 4-ю и переписываь 3-ю.

А вот если использовать VBA, то написав достаточно легкую функцию вида:
ДайПодстроку(СсылкаНаЗначение,НомерПодстроки,Optional Разделитель = "_")

можно писать так:

C1=ДайПодстроку(B1;1)
D1=ДайПодстроку(B1;2)
E1=ДайПодстроку(B1;3)

согласись , что так и проще и, что самое главное - понятнее.
а через теоретический месяц нужно лишь добавить F1=ДайПодстроку(B1;4)

alt76
да согласен понятно и разумно

Добавлено:
UP
так про курс доллара не кто не знает как качать, с каконить сайта, например РБС??

alt76
Если условия изменятся, то конечно мои формулы (3 и будущую 4) придется переписывать, т.е. мой способ не универсален. Но я исходил из конктретно поставленной задачи.

vasiliy74

Цитата:

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

нужно через поиск делать
вот такую формулу попробуйте
=ЕНД(ВПР(B2;LIST_OTHER;1;0))
где B2 - проверяемая ячейка

vasiliy74
Цитата:

так про курс доллара не кто не знает как качать, с каконить сайта, например РБС

Если ты говоришь о том, чтобы курсы $ втянуть в Excel, то лучше поступить так:
1) зайти на http://www.cbr.ru/currency_base/dynamics.asp
2) выбрать там Доллар США и диапазон нужного периода и получить таблицу с данными
3) выделить мышкой данные таблицы, скопировать, и вставить их в Excel.

Каким образом реализовать следующую задачу:
Есть книга из трех листов:
Лист1 Персонал предприятия. В каждой строке табельный, фамилия, имя, отчество, должность, и т д.
Лист2 Регистрация взысканий
Каждая строка содержит взыскание конкретного работника:
Руками я ввожу дату, кто наказал, за что, а вот фамилию работника ставлю так =Лист1!B1 (Т е ссылка на фамилию) В другие столбцы надо добавить автоматически его табельный (т.е. =Лист1!A1), должность (=Лист1!F1), и т д из Лист1.
С третьим листом чуть позже ))

Заранее спасибо

CEMEH
Цитата:

фамилия, имя, отчество

в 3-х разных столбцах?

Цитата:

другие столбцы надо добавить автоматически его табельный

Используй фукнцию ВПР. Работники отсортированы только по табельному номеру? Не по фамилии? Совпадающих фамилий нет?

Mushroomer
В Лист1 нет никакой сортировки, лист вытягивается с базы данных отдела кадров и манипуляции с ним нежелательны (почти каждый день обновляется) Совпадающие фамилии есть.
В Лист2 надо каким то образом автоматически вставлять данные (табельный, должность и тд) используя "фамилию", а точнее формулу на фамилию =Лист1!B1, а еще точнее - используя два последних символа этой формулы-сылки.
Кажется, сделать это невозможно.
На крайний случай можно копировать всю строку из Лист1 вставить в Лист2 неиспользуемые столбцы в Лист2 скрыть. и добавлять столбцы дата, взыскание, и т д руками.

CEMEH
Цитата:

Кажется, сделать это невозможно.

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

Mushroomer
Не пойдет в любом случае. Списки обновляются, и значит обновляется Лист1, а значит меняются фамилии в строках. Это значит, что сегодня выговор получил Пупкин, а после обновления листа выговор будет записан у Васечкина. (((
Фамилию ставить =Лист1!B1 нельзя. Лучше табельный вбить ручками и все остальное пляшет от табельного ИНДЕКС и ПОИСКПОЗ так наверное?
А если со временем табельный поменяется? Надо кнопку "зафиксировать" при нажатии на нее в ячейках вместо формул - их значения )))

Скопировать/вставить строку целиком тоже косяк ((( вставляется вся строка до самого последнего. Т е если я в Лист2 что то уже набил, а потом вставляю скопированную строку - все, что я вбил затирается вставленной строкой. Как в Лист1 удалить (убрать совсем) незадействованные столбцы? Что бы они потом при вставке ничего не затирали.

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

CEMEH
Если все меняется, то вставляй табельный номер не через ссылку, а руками. А если и табельные номера меняются (хотя это нонсенс), то вообще браться за автоматизаацию этой задачи бесполезно. Хотя есть у меня еще одно предложение. Надо в листе2 фамилию,имя, отчество вводить руками в три столбца. Затем в листе2 сделать служебный столбец=Фио&имя&Отчество. Такой же стобец сделать и в листе1. И вот по нему (будем считать что он-то уж точно уникальный) и производить поиск функцией ВПР.

Mushroomer
Руками ФИО вводить долго, а табельный меняется (табельный уволившемуся присваивают вновь прибывшему) Буду через скопировать/вставить всей строки. Только надо что то сделать чтобы не "затирались" ячейки, предназначенные для ввода данных по взысканию, вставляемой строкой. (скопировать ячейки то же долго)

CEMEH
вставлю и я свои пять копеек...
1) при выгрузке из базы отдела кадров очень было бы неплохо выгружать и уникальные коды сотрудников (только не говорите мне, что в БД нет первичного ключа!!! Тогда и БД и её разработчиков однозначно в топку!!!)

2) при выставлении взыскания на сотрудника надо ПОЛНОСТЬЮ копировать все данные (как минимум Фамилия Имя Отчество и ТабельныйНомер)
При этом было бы неплохо сделать следующую автоматизацию:
либо на изменение данных второго листа, либо по кнопке сделать переброс (именно копирование данных с листа1 на лист2
Выбор данных, думаю, можно повесить на выпадающий список (удобнее, кстати, добавить дополнительный столбец, в котором сцепить ТабельНомер и ФИО и его указать в качестве источника для списка).
а вообще тут простор для автоматизации ;-))))

SERGE_BLIZNUK

Уникальный код есть. Первый столбец.
Я так и стал делать. Базу данных (целиком) в Лист1, а потом необходимую строчку копирую в Лист2 ("нарушитель"). Ненужные столбцы скрыты. Добавил Лист3 ("Наказание") в котором номера строк совпадают с номерами строк в листе2 ("нарушитель") Ну это что бы не путаться. Теперь у меня три листа: База данных, нарушитель, наказание. Ну нарушитель и наказание можно и в одном листе написать, но думаю, что быстрее тыкать в ярлычки листов, чем дергать полосу прокрутки. Да! В "наказание" сделал ячейку =СЦЕПИТЬ(Нарушитель!C2;" ";ЛЕВСИМВ(Нарушитель!D2;1);". ";ЛЕВСИМВ(Нарушитель!E2;1);". ") Ну типа Фамилия и Инициалы. Что бы не путаться.

Мда, надо книжки учить. Формочками можно было б это все оч красиво автоматизировать. ))

Завтра буду ее протокол и приказ на взыскание заставлять писать.

SERGE_BLIZNUK

Цитата:

нужно через поиск делать
вот такую формулу попробуйте
=ЕНД(ВПР(B2;LIST_OTHER;1;0))
где B2 - проверяемая ячейка

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

Добавлено:
alt76

Цитата:

C D E F G H I

vasiliy74
Цитата:

работает только для твоего примера

А это точно alt76 адресовано, а не мне? Вроде это мои форумлы, хотя не совсем. А что такое C9& и почему через точку с запятой?

vasiliy74

Цитата:

нужно через поиск делать
вот такую формулу попробуйте
=ЕНД(ВПР(B2;LIST_OTHER;1;0))

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

странно, я как раз проверял на не пустых...
сделайте тестовый файлик XLS с потверждением этого глюка, запакуйте, выложите на хостинг, здесь дайте ссылочку! посмотрим, почему глючит.


по второй задаче...
думаю, что &amp добавил форум... в исходном посте от alt76
этого не было...
и ещё. в ячейке А ничего не меняли?
а в ваших длинных строчках знака "_" нет случаем?...
и вообще, что значит "не работает" ??#Н/Д, другая ошибка или просто не те значения?
во всех трёх G H I ?
поймите же, что тут телепатов нет, они все в отпуске! ;-)))
приводите, хотя бы скриншоты или конкретные значения, что вы занесли в C D E , какие у вас формулы и что получается в G H I

Mushroomer

Цитата:

Вроде это мои форумлы

да вроде нет.. ;-))) вот пост от alt76 от 20:57 25-07-2007

Цитата:

работает только для твоего примера если вводить другие значения в C D E, не работает! например цифры или длинные имена...

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

Ща попытался написать нормальную универсальную формулу - получается оооочень уж громоздко. В качестве моральной компенсации ща напишу на VBA функцию
ДайПодстроку(СсылкаНаЗначение,НомерПодстроки,Optional Разделитель = "_")


Код:
Public Function ДайПодстроку(Строка As String, НомерПодстроки As Long, Optional Разделитель As String = "_") As String

On Error GoTo Err

ДайПодстроку = ""
Строка = Строка & Разделитель

For НомерТекущегоРазделителя = 1 To НомерПодстроки

НайденаяПозиция = InStr(1, Строка, Разделитель)
ДайПодстроку = Left(Строка, НайденаяПозиция - 1)
Строка = Mid(Строка, НайденаяПозиция + Len(Разделитель))

Next

Exit Function
Err:
ДайПодстроку = ""


End Function

Mushroomer

Цитата:

Если ты говоришь о том, чтобы курсы $ втянуть в Excel, то лучше поступить так:
1) зайти на http://www.cbr.ru/currency_base/dynamics.asp
2) выбрать там Доллар США и диапазон нужного периода и получить таблицу с данными
3) выделить мышкой данные таблицы, скопировать, и вставить их в Excel.

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

Добавлено:
alt76
прикольно..

Добавлено:
есть ли функция позволяющая найти значение из столбца 1 если значение в ячейке равно х то возвращает соответствующее значение ячейки из столбца 2???

Добавлено:
написал функцию, только мне бы ещё преобразовать строку в число?

Код:
Public Function Substitution(in_r As Range, in_r1 As Range, x As String) As String
index = 1
For Each c1 In in_r
If c1.Value = x Then
Substitution = in_r1.Cells(index, 1).Value
Exit Function
End If
index = index + 1
Next c1

End Function

vasiliy74

Цитата:

написал функцию, только мне бы ещё преобразовать строку в число?

Код:
Public Function Substitution(in_r As Range, in_r1 As Range, x As String) As String

1) то, что Вам нужно - это объявить функцию
как вариант:
Public Function Substitution(in_r As Range, in_r1 As Range, x As String) As Variant


2) ОЧЕНЬ сильно подозреваю, что вы пытаетесь реальзовать то, что прекрасно можно реализовать БЕЗ макросов и функций:
Похоже, что функция ВПР( или ГПР) делает именно то, что Вам нужно.
единственное, что столбец (или строка для ГПР) в котором искать должен быть сопряжён с тем, откуда брать заменяемое значение.
=ВПР("ff";F1:G4;2;ЛОЖЬ)
это читаем, найти в столбце F1:F4 значение "ff" и вернуть значение из найденной строки из второго столбца указанного диапазона (в данном случае - столбец G)

Поверьте, что встроенные функции гораздо мощнее, чем можно себе представить и, реализованное через них эффективнее, надежнее, безопаснее и быстрее, чем макросы.
p.s. "Золотое правило" VBA в Excel: "Если можешь обойтись без VBA - обойдись!" (c) The okk

SERGE_BLIZNUK
ну ктож спорит просто я не все функции знаю искал именно эту но не нашёл вот и написал...

А вот и доллар нашёл только её править нужно будет, навное не совем сюда ну ладно сдеже вопрос был?!?!

Код:
Sub GetUSD()
'
' Here is a macro to get USD rate from Central Bank of Russia official site
' It also can get any other currency rate.
'
' Спасибо вот этим двум форумам за рабочий шаблон:
' http://www.relib.com/forums/thread801546.aspx
' http://www.plisco.ru/soft/usd.html
'


Dim url_request As String
Dim nodeList As Object
Dim xmldoc As Object
Dim xmlNode As Object
Dim node_attr As Object
Dim i As Integer
Dim strDate As String
Dim USD As String
Dim Dollar As String
Dim uDate As String

' Выборка параметра (даты, для которой выясняется курс ЦБ) из специальной ячейки таблицы -- C2
Range("C2").Select
uDate = ActiveCell.Value

' Запрос к серверу ЦБР
Set xmldoc = CreateObject("Msxml.DOMDocument")
xmldoc.async = False
url_request = "http://www.cbr.ru/scripts/XML_daily.asp?date_req=" + Format(uDate, "dd\/mm\/yyyy")

If Not xmldoc.Load(url_request) = True Then
MsgBox ("Äîêóìåíò íå çàãðóæåí")
Exit Sub
End If

' Обработка полученного ответа
Set nodeList = xmldoc.selectNodes("ValCurs")
Set xmlNode = nodeList.Item(0).CloneNode(True)
Set node_attr = xmlNode.Attributes(0)
strDate = node_attr.Value
Set nodeList = xmldoc.selectNodes("*/Valute")
For i = 0 To nodeList.Length - 1
Set xmlNode = nodeList.Item(i).CloneNode(True)
If xmlNode.childNodes(1).Text = "USD" Then
USD = xmlNode.childNodes(4).Text
Dollar = "Курс доллара на " & uDate & " установлен " & strDate & ": " & USD & " рублей за " & xmlNode.childNodes(2).Text & " доллар"
Exit For
End If
Next
MsgBox Dollar

' Оформительство
'Range("A2").Select
'USD = Replace(USD, ",", ".")
'ActiveCell.FormulaR1C1 = CDbl(USD)
'Range("B2").Select
'Dim sDD As Date
'sDD = Replace(strDate, ".", "/")
'ActiveCell.FormulaR1C1 = Format(sDD, "dd/mm/yyyy")

End Sub

Каким образом реализовать задачку?
В ячейке а1 ставим 28.07.07 (ясно дело, что формат ячейки - дата)
В ячейке а2 ставим "я ходил на форум руборд"
в ячейке а3 должно получиться "28.07.07 я ходил на форум руборд"

пробую так: =а1&а2
получается фигня (дата преобразуется в число, итог - "39291 я ходил на форум руборд")

пробую так: =день(а1)&месяц(а1)&год(а1)&а(2) (вставку точек разделителей между датой, месяцем, годом рассматривать не будем) Тоже не "айс" месяц надо получить прописью.

пробую так =БАТТЕКСТ(а1)&а2 Решил преобразовать число в текст, получилась вообще абракадабра.

В итоге используем второй вариант, но в нем месяц делаем через ЕСЛИ(1 то январь;"")ЕСЛИ(2;"февраль";"") и так далее до декабря, и формула получается слишком уж длинная для такой маленькой задачки.

CEMEH
а если так? (для простоты оставил чисто получение месяца:

=ВЫБОР(МЕСЯЦ(A1);"Январь";"Февраль";"Март";"Апрель";"Май";"Июнь";"Июль";"Август";"Сентябрь";"Октябрь";"Ноябрь";"Декабрь")

Добавлено:
либо написать простую функцию на VBA, которая по дате возращает месяц

Добавлено:

Цитата:

ЕСЛИ(1 то январь;"")ЕСЛИ(2;"февраль";"") и так далее до декабря, и формула получается

а вот и НЕ получается! вы пишете, не попробовав это реализовать - в ЕСЛИ - ограничение вложенности 7-ю уровнями, а вам потребуется 12 (ну или 11, лень думать, а тем более проверять заведомо нерабочий вариант... ;-)))

кстати, ещё вариант решения - через дополнительный диапазон (можно скрытый ;-), в котором тупо перечислены все месяцы, а в нужное место формулу
=ВПР(МЕСЯЦ(A1);Диапазон_с_названиями_месяцев;1;Ложь)

Цитата:

Каким образом реализовать задачку?
В ячейке а1 ставим 28.07.07 (ясно дело, что формат ячейки - дата)
В ячейке а2 ставим "я ходил на форум руборд"
в ячейке а3 должно получиться "28.07.07 я ходил на форум руборд"

a3=ТЕКСТ(a1;"ДД.ММ.ГГ ") & a2 -> "28.07.07 я ходил на форум руборд"
a3=ТЕКСТ(a1;"ДД.ММММ.ГГ ") & a2 -> "28.Июль.07 я ходил на форум руборд"
a3=ТЕКСТ(a1;"ММММ ") & a2 -> "Июль я ходил на форум руборд"

а вообще 2-й аргумент - форматная строка - как в [формат ячеек]->[Число]->[(все форматы)]

SERGE_BLIZNUK

ПОЛУЧИТСЯ!!!
Я обхожу ограничение количество вложений так:


=ЕСЛИ(A1=1;"январь";"")&ЕСЛИ(A1=2;"февраль";"")&ЕСЛИ(A1=3;"март";"")&ЕСЛИ(A1=4;"апрель";"")&ЕСЛИ(A1=5;"май";"")&ЕСЛИ(A1=6;"июнь";"")&ЕСЛИ(A1=7;"июль";"")&ЕСЛИ(A1=8;"август";"")&ЕСЛИ(A1=9;"сентябрь";"")&ЕСЛИ(A1=10;"октябрь";"")&ЕСЛИ(A1=11;"ноябрь";"")&ЕСЛИ(A1=12;"декабрь";"")

в ячейку А1 ставим цифирки и получаем месяц.
Фактически формула не один ЕСЛИ проверяет условие, а каждый ЕСЛИ на каждый месяц в случае соответствия условия - возвращает месяц, а остальные ЕСЛИ (несоответствие) возвращают пустоту "".
Ограничение будет только на количество значков & (а его по моему нет)

Спасибо! Сейчас попробую Ваши варианты
ВЫБОР рулит, спасибо!

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

CEMEH
Цитата:

Я обхожу ограничение количество вложений так:
=ЕСЛИ(A1=1;"январь";"")&ЕСЛИ...

красиво решение. Буду иметь в виду, иногда может быть полезным...

Обратите внимание на решение от alt76
Цитата:

a3=ТЕКСТ(a1;"ММММ ") & a2

Вот она - Истина!
я перебирал все функции, но то, что текст умеет форматы разные выдавать - не знал!
alt76 - спасибо.
Век живи — век учись!

SERGE_BLIZNUK

Цитата:

Вот она - Истина!

А если надо получить не январь, февраль, март а января, февраля, марта ТЕКСТ оч хорошо, но в моем случае лучше ВЫБОР


Добавлено:
SERGE_BLIZNUK

Цитата:

Пожалуй лучшая - Уокенбах Джон. Профессиональное программирование на VBA в Excel 2002...

То, что я нашел - скан не совсем хорошего качества. Если у Вас есть хороший вариант, пришлите плиз.

Не сочтите за флейм

SERGE_BLIZNUK

Цитата:

Похоже, что функция ВПР( или ГПР) делает именно то, что Вам нужно.

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

Добавлено:
сорри не прав эта функция совсем не работает ни так ни так
а моя функция всё нармально находит

Добавлено:
Например: =VLOOKUP($D1;Table;6;FALSE)
это я в таблице Table по первому столбцу ищу сзначение из D1 и при точном совпадении хочу вернуть значение из 6 столюбца, так же делал для 7, 8 и 9... Ни гдже не ищет! Может чего не так? Проверял как он сичтает значения таблицы подставляет верно D1 берёт тоже верно (это сторока), а далее просто возвращаяет не найдено а в столбце 6 значения постоянно меняются там функция их с сервера берёт, и это числа...

прошу совета, на компьютере у пользователя не понятная беда с EXCEL'ем - при открытии любого экселевского документа открывается сама программа, а документ нет. Приходится открывать документы через меню Файл-открыть. Как это исправить ?
При смене учетной записи все работает нормально. Переустановка офиса ничего не дала.
Спасибо

Вопрос:

Ячейку можно потянуть за правый нижний угол и формулы в остальные подставятся автоматически, а как пдставить, если ссылаемся на лист? (а если 100 листов?)
=Лист1!A1
=Лист2!А1
=Лист3!А1
=Лист4!А1
=Лист5!А1 и тд

Спасибо!