Задача excel на собеседовании

mayaa

не знаю даже куда запостить, нужна помощь по екселю. На собеседовании дали задачу. Есть 2 таблицы. В одной 2 столбца: имена банков и их регистрациооные номера, в другой - 4: дата, объём депозитов, номера счетов и такие же регистрационные номера. причем на каждый номер примерно по 10 строк таблицы. надо объеденить 2 таблицы в одну. Предлагают воспользоваться функцией впр(). убил кучу времени, пытаясь найти в инете что-нибудь похожее. помогите кто-нибудь пожалуйста, объясните дураку как это сделать нормально

ETrohkina

впр()

Evgeniy57

будет тебе решение в визуал бейсике

mayaa

а поподробнее

pchelka77

суммесли(регистрацномерсостраницы1;регистрацномерсостраницы2;номерасчетов)
но тока если на первой странице идиентичные записи

ETrohkina

столбец рег.номер - ключ. по нему впришь.
берёшь таблицу где больше столбцов. в правом пустом в первой ячейке вбиваешь = и выбираешь ф-ю ВПР. (читай хэлп ВПР).
тыкаешь на первое поле окна ф-и ВПР, и тыкаешь на ячейку этой строки (куда впр вбиваешь) ячейку из поля "Рег.номер". В окне вставилась ссылка.
Далее тыкаешь на второе поле. идёшь на лист где таблица с 2-мя столбцами (Рег.Номер и Банк). Выделяешь столбцы начиная с рег.номера и до Банка (Важно чтобы рег.номер был первым, т.е. слева, а потом поле Банк. иначе надо юзать ф-ю ГПР).
Чтобы выделить столбцы надо тыкнуть мышкой на название столбца, а не на ячейку (либо диапазон с ячейками можно).
В третьем поле окна ф-и ФПР ставишь 2 (это номер столбца Банк).
в 4-м - интервальный просмотр, ставишь 0
жмёш готово.
далее протягиваешь эту ячейку с формулой на всю таблицу вниз.
готово.

ETrohkina

вот тебе даже с картинками, пока я добрый






pchelka77

альфу лю

mayaa

спасибо огромное, тут еще кое-что сделать надо, но я пока сам подумаю

ETrohkina

хэлпы ботай, там всё понятно

Oleg4534

эксель - отстой.
http://www.javenue.info/post/20

bredjuk

а что еще за задачки? скажи какие еще были

kirawa

Если девушку не найдем и с Excell разобрался, то может отправишь резюме своё на меня.
Вакансия в соседнем треде.

ETrohkina

красавчег!

Evgeniy57

ексель вробе с одной Л пишитцо

philnau

В экселе можно JOIN сделать, кроме как писать макрос?

kirawa

ексель вробе с одной Л пишитцо
Сосчитай сколько ты ошибок сделал?
вробе....
пишитцо...
Гуляй Вася.

Evgeniy57

много, но ексель пишу прально

ETrohkina

Cell - пишется с 2-мя Л =))

Evgeniy57

win+r excel enter и получаем то что нужно

Evgeniy57

а называетцо он Microsoft Excel. и есть подозрение что cel не значит cell а такая же абревиатура

ETrohkina

XL - прально пишецо

a7137928

Cell там не при чём совершенно.
Задача, конечно, простая. Можно было чего-нибудь посложнее задать =))
Протягивать формулы - это удобно, да. Но Мастера Экселя не протягивают формулы
Они делают так:
Left Ctrl+Down Right Shift+Ctrl+Up Ctrl+D
При определённой (небольшой) тренировке эта последовательность действий занимает ну может не полсекунды, но в районе секунды, наверное.

a7137928

В экселе можно JOIN сделать, кроме как писать макрос?
Ну это смотря какого типа join нужно сделать и по какому полю =))
Сложный join по нескольким полям - трудновато будет.
Вот выше демонстрирует пример join при помощи функции vlookup (ВПР). Таким макаром можно легко сделать inner join, если поле, по которому ты соединяешь, является в одной из таблиц уникальным идентификатором записи (или как это там называется в базах данных, "первичный ключ"?). Но вот сджойнить по полям id такие две таблицы
Table1:
data1 id
a 1
b 1
Table2:
data2 id
x 1
y 1
ты при помощи одного vlookup'а не сможешь.
Декартовы произведения таблиц легко получать при помощи pivot tables, но только в том случае, когда одна из таблиц маленькая (да и другая тоже маленькая =) ). И для этого надо ещё в таблицах уникальные идентификаторы записей иметь (или завести).
Короче, в каждом конкретном случае можно реализовать join двух конкретных таблиц при помощи функций, и это обычно не очень сложно, но какую-то общую универсальную конструкцию для джойнов так с ходу трудновато предложить. Универсальным только макрос будет. Но тогда не проще ли тупо перенести это дело в какой-нибудь аксесс и там сджойнить? =))
************
Ночь на дворе, если где фигню написал, поправьте =))

ETrohkina

а два раза на маркер кликнуть не один хер? 0,1секунды
мастера екселя, запомнил

Slawik75

а два раза на маркер кликнуть не один хер?
неа.
Для этого надо оторвать руку от клавиатуры (потому как обычно обе они там при работе с Excel), потом поймать мышку, навести ее куда надо... Дольше, чем описанный вариант получится.
2:
если ты владеешь sql, то проще действительно перенести в Access (хотя там sql слегка отличается от стандартного). Иногда умнее перенести и научиться делать запросы, даже если ты этого еще не умеешь (макрос в среднем пишется дольше, макрос в Excel по нескольким десяткам тысяч строк будет довольно долго шариться, может быть потом придется писать на этой же информации другие выборки).

klenal

Left Ctrl+Down Right Shift+Ctrl+Up
не очень понял
это не то же самое, что LeftCtrl+LeftShift+DownArrow

katrinmania

че такое маркер? =)

Kraft1

szm

mayaa

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

a7137928

не очень понял
это не то же самое, что LeftCtrl+LeftShift+DownArrow
Если ты так сделаешь, то "улетишь" до самого низа столбца, до строчки 65536. Я вот про что говорю:

Тебе нужно протянуть формулу вниз до конца блока. Это можно сделать тремя способами:
1) схватиться мышкой за правый нижний угол ячейки Д19 и протянуть
2) двойной клик на квадратике в правом нижнем углу ячейки Д19
3) описанная мной комбинация: переходишь на ячейку левее (С19), дальше Ctrl+Down - до конца блока (С22), дальше на ячейку правее обратно в столбец Д (Д22), дальше Shift+Ctrl+Up выделяешь кусок столбца вверх до Д19, а затем Ctrl+D копируешь содержание Д19 вниз на выделенный блок.

geva

Ха, не знал про метод 2

a7137928

неа.
Для этого надо оторвать руку от клавиатуры (потому как обычно обе они там при работе с Excel), потом поймать мышку, навести ее куда надо... Дольше, чем описанный вариант получится.
2:
если ты владеешь sql, то проще действительно перенести в Access (хотя там sql слегка отличается от стандартного). Иногда умнее перенести и научиться делать запросы, даже если ты этого еще не умеешь (макрос в среднем пишется дольше, макрос в Excel по нескольким десяткам тысяч строк будет довольно долго шариться, может быть потом придется писать на этой же информации другие выборки).
Очень правильно написал! И про мышку, и про скуэль. Макрос действительно гораздо дольше и сложнее писать, чем запрос в скуэле. Ну то есть мега-отцы макросов, наверное, быстро пишут, но на простом уровне это минут пять-десять-то отнимет вместе с отладкой и исправлением ошибок.
:
да, здесь дело именно в том, что тебе нужно перенести руку на мышку и поводить курсором по экрану, а потом ещё назад руку на клавиатуру положить (ну если ты не одним пальцем левой руки печатаешь =)) ). В итоге можно секунды две-три на это потратить.
Я как-то видел, как работает Мастер Экселя (у него чему-то и научился) =)) Я тогда знал про формулки всякие, а из горячих клавиш - только Ctrl+C, Ctrl+V. И тут я вижу, как человек открывает лист и, отодвинув мышку в сторону, со страшной скоростью начинает долбить по клавиатуре. Слепая печать на русском и английском, переход между листами по Ctrl+PgUp/PgDn, меню вызывает альтом (и дальше не курсором бегает, а сразу на нужную строчку перемещается по подчёркнутой букве), контекстное меню - специальной клавишей на виндовской клавиатуре, куча разных хоткеев использует.
Я прям офигел, не мог глазами проследить, что он делает.

a7137928

Ха, не знал про метод 2
Да, в экселе вообще до фига всяких таких удобных мелочей =)
Я ещё, помню, сильно удивился, когда в первый раз увидел, как автоподбор ширины столбца делается (двойным кликом по палочке между буквой/цифрой нужного столбца и буквой/цифрой следующего). До этого я старательно всем столбцам вручную ширину менял. Когда их было штук тридцать, это отнимало чертовски много времени =)

ETrohkina

Я тоже видел как работает мастер екселя сидел днями обложенный книжками по екселю. через 3 месяца его уволили, т.к. кроме ботанья екселя он нифига не делал =)

a7137928

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

ETrohkina

не. макросы другой пишет. адекватный
а по поводу мышки, Дэн, ты не заметил по скриншотам, что я мышой работаю, а не клавой?

ETrohkina

че такое маркер? =)
маркер - это такая большая пиписка Шалера, нажимая на которую она становится ещё больше
Область ячеек (ячейка) может быть размножена при помощи использования маркера заполнения. Как было показано в предыдущем разделе, маркер заполнения представляет собой контрольную точку в правом нижнем углу выделенной ячейки.
http://www.delcomp.ru/030_1.html

a7137928

а по поводу мышки, Дэн, ты не заметил по скриншотам, что я мышой работаю, а не клавой?
Вот я тебя агитирую переучиваться =)) Клава всяко рульнее мыжки!

anna77

есть такая проблемка:
База данных в лист1: улица, дом, корпус, квартира, фамилия, инициалы
используя эти данные необходимо сгенерировать соответвенно столько же форматов А4 сколько строк
в данном лист1 имеется
для последующей печати-чтобы получилось что-то вроде адресного поля(потом лист складывается втрое и засовывается в конверт с окном) соответсвенно чтоб через это окошко были видны эти данные
может кто поможет?

a7137928

Что такое "формат А4"?
Если нужно из этой базы наделать бумажек с адресами, то я бы писал макрос. Сначала взять чистый лист экселя и экспериментальным путём определить, в каком месте должно оказаться имя, в каком адрес, и т.п., чтобы при печати на листе формата А4 и складывании листа втрое все надписи попадали в "окошко" (желательно, с приличным запасом).
Возможно, надо будет на этом листе, с которого пойдёт печать, вручную отрегулировать ширину столбцов и высоту строк.
Допустим, после серии экспериментов обнаружили, что улица должна быть в новом листе в ячейке B2, а дом - в ячейке B4. Пишем простой макрос по типу такого (за синтаксис не ручаюсь, не проверял):
 
* база на первом листе начиная с ячейки А1, первая строчка - имена полей
* печать идёт со второго листа
i=2
do while len(sheets(1).cells(i,1).value)>0 *цикл пока база не закончится
sheets(2).cells(2,2) = sheets(1).cells(i,1).value
sheets(2).cells(2,4) = sheets(1).cells(i,2).value
* и так далее вручную копируем все ячейки из i-й строчки первого листа в заранее
* определённые ячейки второго листа

* теперь распечатываем второй лист
sheets(2).PrintOut

i= i+1
Wend

Как-то так.

anna77

а поподробнее, как ты написал не особо получилось(

katrinmania

что именно не получилось?

anna77

не компилируется((

losjawrik

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

anna77

пробывал:
Sub macros()
i = 2
Do While Len(Sheets(1).Cells(i, 1).Value) > 0
Sheets(2).Cells(2, 2) = Sheets(1).Cells(i, 1).Value
Sheets(2).Cells(2, 4) = Sheets(1).Cells(i, 2).Value
Sheets(2).PrintOut
i = i + 1
Wend
End Sub
ругается

tralx

не понял что тебе надо, но этот текст работать будет тольк так
Sub macros()
i = 2
Do While Len(Sheets(1).Cells(i, 1).Value) > 0
Sheets(2).Cells(2, 2) = Sheets(1).Cells(i, 1).Value
Sheets(2).Cells(2, 4) = Sheets(1).Cells(i, 2).Value
Sheets(2).PrintOut
i = i + 1
Loop
End Sub

anna77

мешался
end loop)))
Всем-всем огромное спасибо за помощь!

a7137928

вместо звёздочек одинарные кавычки
Кстати да.

nikitin555

А какие есть хорошие книжки по Exel? Посерьезнее, чтобы макросы писать научиться, навороченные функции
изучить...

a7137928

Есть майкрософтовское руководство. Видел такую книгу по экселю 97, хотя, в принципе, там отличий-то немного от более поздних версий. Толстенная книжища белого цвета с майкрософтовским логотипом (стОит, наверное, до фига). Там совсем чуть-чуть написано про макросы, но зато очень подробно описаны все "базовые" функции, и "навороченные" тоже. Хотя, вообще говоря, по функциям тупо читается экселевский хелп, и всё. Там отлично всё расписано, и примеры есть.
Макросы - лично я учился по такой книжке: Андрей Гарнаев "Эксель 2002. Разработка приложений". Но у него и другие книги есть, примерно такие же, может быть даже получше. Например, "VBA. Наиболее полное руководство" и "Самоучитель VBA". По ходу, они не только про эксель, но и про другие МСОфисные приложения.
На самом деле, "Разработка приложений" - наверное, не лучшая книга с точки зрения хороших учебников по программированию, она мне показалась какой-то не очень структурированной. Хотелось бы, наверное, полный индекс бейсиковых функций по группам: тут функции обработки строк, тут функции печати, и т.п. А этого там нет. Но зато я понял оттуда объектную модель майкрософтовских приложений, что потом позволило мне, когда припёрло, с нуля, не пользуясь ничем кроме официального хелпа, написать несколько коротких макросов в Ворде и скриптов в СПСС.

avm09

Я не так давно из инета вынес 2 книжки в эл. виде правда
Демидова Л.А. Пылькин А.Н. "Программирование в среде VBA", 2004
Кузьменко В.Г. "Программирование на VBA 2002", 2003
Если хочешь, могу принести в ГЗ.

Brodnik

Нужно написать очень простенький макрос, чтобы в текущую ячейку вставлялась формула.
Как в редакторе макроса заставить его использовать абсолютные адреса ячеек, а не относительно текущей ( R[1]C[1] — относительный адрес ячейки правее и ниже текущей )?

katrinmania

ну .Cells(...) и так абсолютные координаты принимает

Slawik75

вопрос не про то, как взять значение из конкретной клетки. А про то, как в клетку из макроса написать формулу с абсолютными ссылками.
Т.е. он пишет в макросе
Range("A1").Select
ActiveCell.FormulaR1C1 = "R[1]C[1]"
В результате в клетку А1 вставляется формула, возвращающая значение из клетки на одну правее и на одну ниже (относительная ссылка)
2:
Записал макрос. Сделал следующее:
1. выделил клетку
2. написал в нее =$E$11
В макросе появился код
ActiveCell.FormulaR1C1 = "=R11C5"
Думаю, это то, что тебе надо

a7137928

Думаю, это то, что тебе надо
Как я понял, именно это и надо. А вообще, товарищи, ботайте R1C1-адресацию, это же очень простая вещь!
Абсолютный адрес пишется как RmCn
Относительный как R[m]C[n]
Смешанные варианты: RmC[n], R[m]Cn
Если с R1C1 совсем тяжело, то пишем ActiveCell.FormulaLocal = "...", и в кавычках вставляем формулу в "локальной" адресации (буквенный код столбца и номер строки, с долларами для абсолютной адресации).

katrinmania

так вот куда все телепаты из нетворка ушли

Brodnik

Да, именно это и надо было. Огромное спасибо за телепатию и

Brodnik

Подскажите еще как проверить, что текущая ячейка находится на нужном листе?
if AnsiSameText(ActiveCell.WorkSheet.Name, 'Name') не работает

losjawrik

а сравнивать ActiveSheet.Name нельзя?

ivan06

Что значит
текущая ячейка находится на нужном листе
?
напиши подробнее

Brodnik

Всем спасибо. Была проблема в неправильном использовании русских имен функций в макросе

a7137928

Всем спасибо. Была проблема в неправильном использовании русских имен функций в макросе
Я сильно не разбирался, но у меня сложилось такое ощущение, что русский эксель - это вообще не очень хорошая вещь с точки зрения макросов:
1. надо постоянно переключать раскладку, если русские функции прописываешь в макросе, и листы по умолчанию называются типа "Лист 1". Либо раскладку дёргать, либо по номеру листа обращаться. Очень неудобно работать.
2. я не смог в русском экселе заставить работать такую мега-вещь, как Application.WorksheetFunction. Было очень грустно, когда хотел воспользоваться в массиве экселевской функцией rank, а оно не работает, блин! Это было полнейшее западло, потерял на этом кучу времени и в итоге тупо пошёл на комп, где стоял английский эксель.

Slawik75

Application.WorksheetFunction.
по-моему, просто WorksheetFunction
А вообще, Ctrl+Shift нажать довольно просто. Тут вопрос привычки.
Хотя русский Excel, конечно, немного напрягает тем, что в макросе и в листах функции называются по-разному.

mamishka

application.worksheetfunction.*английское имя функции*()
все будет прекрасно работать.
П.С. Кстати, такие функции как правило очень хорошо оптимизованы и офигенно быстро исполняются.

a7137928

application.worksheetfunction.*английское имя функции*()
все будет прекрасно работать.
Во как! А я и не знал. Точнее, я был уверен, что пробовал с английскими именами, и у меня не получилось. Видимо, плохо пробовал, или ошибся где-то. Спасибо
П.С. Кстати, такие функции как правило очень хорошо оптимизованы и офигенно быстро исполняются.

И поэтому вдвойне ломает писать свой аналог экселевской функции =))

a7137928

по-моему, просто WorksheetFunction
Слово application можно опускать, если ты за пределы экселя не выходишь. То же самое, что писать просто cells(), без всяких workbooks().worksheets(), если ты точно знаешь, в какой книге и на каком листе находишься.

mamishka

VBA популярен в разеделе Job =)
Оставить комментарий
Имя или ник:
Комментарий: