MS Excel - решение практических задач

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

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[Enfriz]

Цитата: Сообщение от Tin Используя функцию СЧЁТЕСЛИ при совпадении чисел в столбце, прога считает за одно. Как исправить? Пример: 1, 2, 3, 4, 4, 5 =СЧЁТЕСЛИ(A1:A6;">3") Должно получиться 3, но пишет 2, т.е. (4) считает за один. 2007 EX

Специально сейчас у себя проверил, все работает. то есть показывает 3, а не 2:



Т.е. это не баг Экселя вообще, это скорее баг именно вашей версии. Попробуйте либо переустановить эксель, либо найти другую версию.

[ivan712]

Цитата: Сообщение от Enfriz Т.е. это не баг Экселя вообще, это скорее баг именно вашей версии. Попробуйте либо переустановить эксель, либо найти другую версию.

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

[ortofil]

Подскажите пожалуйста. У меня есть база данных: один столбец кодовый номер, а другой фамилия. Как сделать так чтобы при набирании какого либа кодового номера на другом листе, во втором столбце рядом автоматически высвечивалась (появлялась) фамилия.? Я пробовал связывать данные но както не удачно.
Спасибо.

[Enfriz]

Цитата: Сообщение от ortofil Подскажите пожалуйста. У меня есть база данных: один столбец кодовый номер, а другой фамилия. Как сделать так чтобы при набирании какого либа кодового номера на другом листе, во втором столбце рядом автоматически высвечивалась (появлялась) фамилия.? Я пробовал связывать данные но както не удачно. Спасибо.

В ячейке, где должна оботражаться фамилия:

=ВПР(адрес ячейки, куда вводится код;диапазон обоих столбцов;номер столбца в диапазоне;0)

То есть например если есть два столбца. Один A1:A5 с кодами, другой B1:B5 с фамилиями. Все находятся на Листе1.

Допустим на Листе2 вводится код в ячейку B1, а фамилия должна быть в A1. Для этого в A1 пишем:

=ВПР(B1;Лист1!A1:B5;2;ЛОЖЬ)

[vilukin]

=ЕСЛИ(ЕНД(ВПР(B1;Лист1!$A$1:$B$5;2;ЛОЖЬ)) = ИСТИНА; "Сотрудник не найден"; ВПР(B1;Лист1!$A$1:$B$5;2;ЛОЖЬ)) - тогда формулу можно "перетаскивать".

ЗЫ: подобное можно получить и используя две функции: ИНДЕКС(ПОИСКПОЗ())

[gazowik]

добрый день всем.
Подскажите как втсавить данные из Ворда в Ексель без форматирования.
То есть если написано 2:1, то чтобы после вставки в Excel так и оставалось а не изменял в 2:01

[vilukin]

gazowik,
Вариант 1
1. Выделить ячейки в Excel-е, в которые будете вставлять данные - Формат ячеек – Текстовый
2. Скопировать данные в Word-е.
3. В Excel-е – специальная вставка - Text
Вариант 2
1. В Word-е перед копированием заменить ":" на " : " (пробел до и после двоеточия)

ЗЫ: Это случайно не результаты футбольных матчей?

[gazowik]

to vilukin
спасибо за совет. ты почти угадал, только не футбольных а хоккейных матчей

[DmitriC]

Цитата: Сообщение от Tin Используя функцию СЧЁТЕСЛИ при совпадении чисел в столбце, прога считает за одно. Как исправить? Пример: 1, 2, 3, 4, 4, 5 =СЧЁТЕСЛИ(A1:A6;">3") Должно получиться 3, но пишет 2, т.е. (4) считает за один. 2007 EX

Подтверждаю слова Enfriz - Excel выдает правильный результат (3).

Office 2007 SP1

[capcipa]

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

[vilukin]

capcipa,
В "Формат ячейки-Числовые форматы-(все форматы)" создать формат ячейки # ###;(-# ###).

[capcipa]

Цитата: Сообщение от vilukin capcipa, В "Формат ячейки-Числовые форматы-(все форматы)" создать формат ячейки # ###;(-# ###).

Работает, большое спасибо.
Мне оказывается нужен был такой формат - # ###;(# ###)

[sterion]

Итак, работаем.
Вопрос не по excel, а по openoffice calc.
Можно ли сделать так, чтобы цифра прописывалась, т.е. не 100, а сто?

[ivan712]

Цитата: Сообщение от sterion Итак, работаем. Вопрос не по excel, а по openoffice calc. Можно ли сделать так, чтобы цифра прописывалась, т.е. не 100, а сто?

Так же, как в Excel - используя функции на Basic. Пример функций, работающих одинаково в Excel и Calc:

Код макроса

Option Explicit

Function myrubl(ByVal lnga As Long) As String

Dim strx As String
Dim intlx As Integer
Dim inti As Integer
Dim strq As String
Dim intll As Integer

Dim intrub(1 To 9) As Integer

Dim strr(1 To 4, 1 To 9) As String

strr(1, 1) = "один"
strr(1, 2) = "два"
strr(1, 3) = "три"
strr(1, 4) = "четыре"
strr(1, 5) = "пять"
strr(1, 6) = "шесть"
strr(1, 7) = "семь"
strr(1, 8) = "восемь"
strr(1, 9) = "девять"

strr(2, 1) = "десять"
strr(2, 2) = "двадцать"
strr(2, 3) = "тридцать"
strr(2, 4) = "сорок"
strr(2, 5) = "пятьдесят"
strr(2, 6) = "шестьдесят"
strr(2, 7) = "семьдесят"
strr(2, 8) = "восемьдесят"
strr(2, 9) = "девяносто"

strr(3, 1) = "сто"
strr(3, 2) = "двести"
strr(3, 3) = "триста"
strr(3, 4) = "четыреста"
strr(3, 5) = "пятьсот"
strr(3, 6) = "шестьсот"
strr(3, 7) = "семьсот"
strr(3, 8) = "восемьсот"
strr(3, 9) = "девятьсот"

strr(4, 1) = "одиннадцать"
strr(4, 2) = "двенадцать"
strr(4, 3) = "тринадцать"
strr(4, 4) = "четырнадцать"
strr(4, 5) = "пятнадцать"
strr(4, 6) = "шестнадцать"
strr(4, 7) = "семнадцать"
strr(4, 8) = "восемнадцать"
strr(4, 9) = "девятнадцать"

strx = Trim(Str(lnga))
intlx = Len(strx)
If intlx > 9 Or lnga = 0 Then
myrubl = ""
Else

For inti = 1 To intlx
intrub(inti) = Val(Mid(strx, intlx - inti + 1, 1))
Next

strq = ""

For inti = intlx To 1 Step -1
intll = intrub(inti)

If intll <> 0 Then
If (inti = 9 Or inti = 6 Or inti = 3) Then
strq = strq + Trim(strr(3, intll))
ElseIf (inti = 8 Or inti = 5 Or inti = 2) Then
If intll = 1 And intrub(inti - 1) <> 0 Then
inti = inti - 1
intll = intrub(inti)
strq = strq + Trim(strr(4, intll))
Else
strq = strq + Trim(strr(2, intll))
End If
ElseIf inti = 7 Or inti = 1 Then
strq = strq + Trim(strr(1, intll))
ElseIf inti = 4 Then
If intll = 1 Then
strq = strq + "одна"
ElseIf intll = 2 Then
strq = strq + "две"
Else
strq = strq + Trim(strr(1, intll))
End If
End If
End If

If Right(strq, 1) <> " " Then
strq = strq + " "
End If


If inti = 7 Then
If intrub(7) = 1 And (intlx > 7 And _
intrub(8) <> 1 Or intlx = 7) Then
strq = strq + "миллион"
ElseIf (intrub(7) >= 2 And intrub(7) <= 4) _
And (intlx > 7 And intrub(8) <> 1 _
Or intlx = 7) Then
strq = strq + "миллиона"
Else
strq = strq + "миллионов"
End If
ElseIf inti = 4 Then
If intrub(4) + IIf(intlx > 4, intrub(5), 0) + _
IIf(intlx > 5, intrub(6), 0) = 0 Then
ElseIf intrub(4) = 1 And (intlx > 4 And _
intrub(5) <> 1 Or intlx = 4) Then
strq = strq + "тысяча"
ElseIf (intrub(4) >= 2 And intrub(4) <= 4) _
And (intlx > 4 And intrub(5) <> 1 _
Or intlx = 4) Then
strq = strq + "тысячи"
Else
strq = strq + "тысяч"
End If
ElseIf inti = 1 Then
If intrub(inti) = 1 And (intlx > 1 And _
intrub(2) <> 1 Or intlx = 1) Then
strq = strq + "рубль"
ElseIf (intrub(1) >= 2 And intrub(1) <= 4) _
And (intlx > 1 And intrub(2) <> 1 Or _
intlx = 1) Then
strq = strq + "рубля"
Else
strq = strq + "рублей"
End If
End If

If Right(strq, 1) <> " " Then
strq = strq + " "
End If

Next

myrubl = Trim(strq)

End If

End Function

Function mykop(inta As Integer) As String
Dim strq As String
Dim strt As String
Dim strt1 As String
Dim strt2 As String

If inta > 99 Then
mykop = ""
Else

strt = Trim(Str(inta))
If Len(strt) = 1 Then
strt = "0" + strt
End If

strt1 = Left(strt, 1)
strt2 = Right(strt, 1)

If strt2 = "1" And strt1 <> "1" Then
strq = strt + " копейка"
ElseIf (strt2 = "2" Or strt2 = "3" Or strt2 = "4") _
And strt1 <> "1" Then
strq = strt + " копейки"
Else
strq = strt + " копеек"
End If

mykop = strq

End If

End Function

Function myrubkop(ByVal curz As Currency, _
Optional ByVal blnnkop) As String
' Если Blnnkop=Falsе,
' "00 копеек" не выводится

Dim lngr As Long, intk As Integer
Dim strq As String
Dim blnnkopl As Boolean

If curz < 0 Or curz > 999999999.99 Then
myrubkop = "***************************"
Else

lngr = Int(curz)
intk = (curz - lngr) * 100
strq = myrubl(lngr)

If IsMissing(blnnkop) Then
blnnkopl = True
Else
blnnkopl = blnnkop
End If


If intk <> 0 Or (intk = 0 And blnnkopl) Then
strq = strq + " " + mykop(intk)
Else
strq = strq
End If

myrubkop = UCase(Left(strq, 1)) + _
Right(strq, Len(strq) - 1)

End If
End Function

В Calc в меню выбираешь
Сервис - Макросы - Управление Макросами - Open Office.org Basic
создаешь в файле новый модуль и вставляешь код.
После этого можно в ячейках использовать функции myrubl(), mykop(), myrublkop().

[sterion]

Спасибо. конечно.
Но боюсь, это мне не поможет. Представьте, что я блондинка - будет легче...

Добавлено через 8 минут
Добавить макросы смогла. Как использовать не знаю...