天道酬勤,学无止境

excel-2011

Application.Filedialog support in Mac excel 2011

Couple of questions Is Application.Filedialog(msoFileDialogSaveAs) support in Mac excel 2011 vba? Difference between Application.Filedialog(msoFileDialogSaveAs) and Application.Dialogs(xlDialogSaveAs)?

2021-11-15 09:28:40    分类:问答    macos   vba   excel-2011

Excel SUMIFS of cells based upon multiple criteria

I'm trying to create a formula which calculates the sum an array of fields based upon multiple criteria. This relates to various franchises and the promotions which they run during the year My document contains two sheets with the following information within in it - 'Daily Revenues'!A:A - date. There is one entry per franchise per day with the revenue for that specific day. 'Daily Revenues'!B:B - the franchise name 'Daily Revenues'!D:D - number. The daily revenue for that day. 'Promotions'!I:I - date. The start date of the promotion 'Promotions'!J:J - date. The end date of the promotion

2021-11-13 03:49:55    分类:问答    excel   excel-formula   excel-2011

Excel 2011 - 如何将 textJoin 的输出从单元格的值更改为单元格名称,以便我可以将其放入 Sum() 以添加这些值(Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values)

问题 我正在使用下面列出的 textJoin UDF 将来自不同行的值组合到一个单元格中; 它显示每个值。 但是,我想知道我是否可以操纵这个 UDF 的输出,这样我就可以将这些值相加并获得这些值的总和,而不是简单地显示这些值。 或者理想情况下,如果我可以修改变量以指示它添加值。 有谁知道是否可以指示这个 UDF(我没有创建)输出单元名称(A2、B2、C2),如果可以,我可以将该输出放在 Sum() 函数中,以便它添加 A2 +B2+C2? Function TEXTJOIN(delim As String, skipblank As Boolean, arr) Dim d As Long Dim c As Long Dim arr2() Dim t As Long, y As Long t = -1 y = -1 If TypeName(arr) = "Range" Then arr2 = arr.Value Else arr2 = arr End If On Error Resume Next t = UBound(arr2, 2) y = UBound(arr2, 1) On Error GoTo 0 If t >= 0 And y >= 0 Then For c = LBound(arr2, 1) To UBound(arr2, 1) For d = LBound(arr2

2021-11-10 19:13:13    分类:技术分享    excel   vba   macos   excel-2011   excel-udf

Excel 2011 - How to change output of textJoin from value of cell, to the cell name, so I can put it in Sum() to add those values

I'm using the textJoin UDF listed below to combine values from various rows into a single cell; it displays each value. However, I want to know if I can manipulate the output of this UDF so that instead of simply displaying the values I can add the values and get the sum of the values. Or ideally, if I can just modify variables to instruct it to ADD the values. Does anyone know if this UDF (which I didn't create) can be instructed to output the Cell names (A2, B2, C2), and if so can I put that output within the Sum() function so that it will add A2+B2+C2? Function TEXTJOIN(delim As String

2021-11-06 11:07:49    分类:问答    excel   vba   macos   excel-2011   excel-udf

显示即将到来的截止日期(Display the upcoming due date)

问题 我正在使用 Microsoft Excel for Mac 2011 做一些个人理财并尝试设计一个公式来显示特定日期。 我有一张信用卡账单,每个月的 24 号到期。 我在A列有名字,B列有到期日期。假设当月是10月,账单将在24日到期,我希望它显示10/24/15 (mm/dd /年)。 我不想显示任何以前的日期或当前日期,我只想显示即将到来的截止日期,并且我希望它保持设置在 10/24/15 直到 10/25/15 它将显示下一个到期日期日期为11/24/15 ,下个月。 我需要它显示 09/25/15 到 10/24/15 的截止日期。 然后在 10/25/15 我需要它来显示下一个截止日期。 回答1 未经测试。 请尝试: =IF(DAY(TODAY())<25,DATE(YEAR(TODAY()),MONTH(TODAY()),24),DATE(YEAR(TODAY()),MONTH(TODAY()+1),24)) 回答2 此解决方案假设如下: Microsoft Excel for Mac 2011 包括 Excel 函数EOMONTH 应付帐款表位于B1:D6 (根据需要调整),具有以下字段(见图 1) : Account : 应付账款名称 到期日: 付款到期日 到期日: 下一个付款日期 在C2输入这两个公式中的任何一个并复制到最后一条记录 公式1: =IF(DAY

2021-10-24 06:01:38    分类:技术分享    excel   datetime   excel-formula   excel-2011

Display the upcoming due date

I am working with Microsoft Excel for Mac 2011 doing some personal finance and trying to devise a formula to display a specific date. I have a credit card bill that is due on the 24th of every month. I have the name in Column A, and the Date it is due in Column B. Say that the current month is October, and the bill will be due on the 24th, I want it to display 10/24/15 (mm/dd/yy). I do not want to show any previous dates or current date, I only want to display the upcoming due date, and I want it to remain set on 10/24/15 until 10/25/15 where it will show me the next due date as 11/24/15, the

2021-10-24 01:31:51    分类:问答    excel   datetime   excel-formula   excel-2011

Sumif 在两列的两个日期之间(Sumif between two dates over two columns)

问题 我想对两个日期之间的一列求和,但是日期在不同的列中。 我想要以下日期 01-01-2014(单元格 E1)和 31-03-2014(单元格 F1)之间的总和。 如果您查看单元格 C6,则日期低于第一个范围,并且按您的预期包含在内。 当前公式分别查看两列,即 E1 上方的开始日期(B 列)和 F1 下方的结束日期(C 列)。 我需要一个公式来考虑这两个日期,即如果开始日期高于 E1 且低于 F1,则对价格列求和。 C6 的日期是我想避免的,即使它低于单元格 F1,我仍然希望它高于单元格 E1。 有没有人能够就我如何做到这一点提供一些帮助? 回答1 请尝试: =SUMIFS(E:E,B:B,">="&E1,C:C,">"&E1,C:C,"<="&F1)

2021-10-19 12:16:14    分类:技术分享    excel   date   excel-formula   excel-2011

Sumif between two dates over two columns

I would like to sum a column between two dates, however the dates are in separate columns. I want the sum total between the following dates 01-01-2014 (cell E1) and 31-03-2014 (cell F1). If you look at cell C6 the date is lower than the first range and is being included as you would expect. The current formula looks at the two columns individually i.e. is Start date (column B) above E1 and then is End date (column C) below F1. I need a formula that takes into account both dates i.e if start date is above E1 and below F1 then sum the Price column. The date at C6 is something I would like to

2021-10-19 03:11:59    分类:问答    excel   date   excel-formula   excel-2011

VLOOKUP 不适用于 MAC 2011 的 excel(VLOOKUP not working in excel for MAC 2011)

问题 我在 MAC 2011 的 excel 中使用 VLOOKUP 时遇到问题。我在不同的工作表中查找值两列,列 A 是我要检查的列,列 B 是参考。 =VLOOKUP(A1, Sheet2!$A$1:$B$1000,2,0) A 列中的某些框会返回值,而其他框则不能。 对于那些不能的,当我重新输入框中的单词时,我得到返回的值。 我尝试复制 A 列并仅选择性地粘贴值,但无法获得返回值的那些保持不变。 我的客人格式有问题,但我无法修复它。 请帮忙。 回答1 似乎是尾随空格的问题。 可以通过将公式调整为以下内容来处理表数组中有或没有: =VLOOKUP("*"&A1&"*",Sheet2!$A$1:$B$1000,2,0) (前提是你没有像 ColumnA 中的manifestation和station类的东西!)。 如果您的源数据具有 Table_array 没有的前导或尾随空格,则推荐的解决方案是通过以下公式删除这些空格: =TRIM(A1) 向下复制以适应,然后将该数组插入到带有特殊粘贴、值的 A1 中。 另一种可能性是您可能试图将一个值与看起来像一个值的文本进行匹配(反之亦然)。

2021-10-10 03:08:52    分类:技术分享    excel-2011

VLOOKUP not working in excel for MAC 2011

I have a problem when using VLOOKUP in excel for MAC 2011. I am looking up for a value two column in different sheet, Column A is the one I want to check, Column B is the reference. =VLOOKUP(A1, Sheet2!$A$1:$B$1000,2,0) Some of boxes in Column A get a value return, but the other cannot. For those which cannot, when I retype the words inside the box, and I get the value returned. I try to copy the Column A and selectively paste with value only, but those cannot get a returned value remained the same. I guest there is something wrong with the format, but I cannot fix it. Please help.

2021-09-07 08:54:53    分类:问答    excel-2011